|
innodb_fts.fulltext2.test (from mysql 5.7)
CREATE TABLE t1 (
|
FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
|
id int(10) not null ,
|
first_name varchar(50) NOT NULL,
|
last_name varchar(50) NOT NULL,
|
PRIMARY KEY (FTS_DOC_ID),
|
UNIQUE KEY idx_1 (first_name, last_name),
|
FULLTEXT KEY `idx_2` (first_name)
|
) ENGINE=InnoDB;
|
|
INSERT INTO t1 (id, first_name, last_name) VALUES
|
(10, 'Bart', 'Simpson'),
|
(11, 'Homer', 'Simpson'),
|
(12, 'Marge', 'Simpson'),
|
(13, 'Lisa', 'Simpson'),
|
(14, 'Maggie', 'Simpson'),
|
(15, 'Ned', 'Flanders'),
|
(16, 'Nelson', 'Muntz');
|
|
analyze table t1;
|
SELECT fts_doc_id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN BOOLEAN MODE) AS score FROM t1;
|
in MariaDB 10.2.10
MariaDB [test]> SELECT fts_doc_id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN BOOLEAN MODE) AS score FROM t1;
|
+------------+------------+-----------+-------+
|
| fts_doc_id | first_name | last_name | score |
|
+------------+------------+-----------+-------+
|
| 1 | Bart | Simpson | 0 |
|
| 2 | Homer | Simpson | 0 |
|
| 4 | Lisa | Simpson | 0 |
|
| 5 | Maggie | Simpson | 0 |
|
| 3 | Marge | Simpson | 0 |
|
| 6 | Ned | Flanders | 0 |
|
| 7 | Nelson | Muntz | 0 |
|
+------------+------------+-----------+-------+
|
7 rows in set (0.00 sec)
|
in mysql 5.7
mysql> SELECT fts_doc_id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN BOOLEAN MODE) AS score FROM t1;
|
+------------+------------+-----------+--------------------+
|
| fts_doc_id | first_name | last_name | score |
|
+------------+------------+-----------+--------------------+
|
| 1 | Bart | Simpson | 0 |
|
| 2 | Homer | Simpson | 0.7141907215118408 |
|
| 4 | Lisa | Simpson | 0 |
|
| 5 | Maggie | Simpson | 0 |
|
| 3 | Marge | Simpson | 0 |
|
| 6 | Ned | Flanders | 0 |
|
| 7 | Nelson | Muntz | 0 |
|
+------------+------------+-----------+--------------------+
|
7 rows in set (0,05 sec)
|
also without primary key, result is different in mysql/ please check that it is correct.
MariaDB [test]> CREATE TABLE t1 (
|
-> FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL,
|
-> id int(10) not null ,
|
-> first_name varchar(50) NOT NULL,
|
-> last_name varchar(50) NOT NULL,
|
-> UNIQUE KEY idx_1 (first_name, last_name),
|
-> FULLTEXT KEY `idx_2` (first_name)
|
-> ) ENGINE=InnoDB;
|
Query OK, 0 rows affected (1.74 sec)
|
|
MariaDB [test]> INSERT INTO t1 VALUES
|
-> (1,10, 'Bart', 'Simpson'),
|
-> (2,11, 'Homer', 'Simpson'),
|
-> (3,12, 'Marge', 'Simpson'),
|
-> (4,13, 'Lisa', 'Simpson'),
|
-> (5,14, 'Maggie', 'Simpson'),
|
-> (6,15, 'Ned', 'Flanders'),
|
-> (7,16, 'Nelson', 'Muntz');
|
Query OK, 7 rows affected (0.04 sec)
|
Records: 7 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> SELECT fts_doc_id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN BOOLEAN MODE) AS score FROM t1;
|
+------------+------------+-----------+--------------------+
|
| fts_doc_id | first_name | last_name | score |
|
+------------+------------+-----------+--------------------+
|
| 1 | Bart | Simpson | 0 |
|
| 2 | Homer | Simpson | 0.8155715465545654 |
|
| 4 | Lisa | Simpson | 0 |
|
| 5 | Maggie | Simpson | 0 |
|
| 3 | Marge | Simpson | 0 |
|
| 6 | Ned | Flanders | 0 |
|
| 7 | Nelson | Muntz | 0 |
|
+------------+------------+-----------+--------------------+
|
7 rows in set (0.00 sec)
|
|