Thesql fulltext – exact match or nothing

full-text-searchMySQL

I have mysql table with column tags, and would like to search exact match.
For this data:

 proj7 invoice delivery
 proj7 exchange

and this query

 MATCH(tags) AGAINST ('+proj7 +(invoice report)')

should be

 (proj7 AND (invoice OR report))

So i want only first record in result, but there are both, second with low score due to proj7 existence. How can i query exact matches only.

sqlfiddle http://sqlfiddle.com/#!9/26524/3

Best Answer

PROPOSED QUERY

SELECT * FROM test WHERE MATCH(tags)
AGAINST ('"+proj_7" "(invoice report)"' IN BOOLEAN MODE);

YOUR SAMPLE DATA LOADED

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS test;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `test` (
    ->   `tags` text NOT NULL,
    ->   FULLTEXT KEY `tags` (`tags`)
    -> );
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO test (tags) VALUES ("proj_7 type_invdtl year_15 month_04");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (tags) VALUES ("proj_8 type_invdtl year_15 month_04");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (tags) VALUES ("proj_10 type_invdtl year_15 month_04");
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM test;
+--------------------------------------+
| tags                                 |
+--------------------------------------+
| proj_7 type_invdtl year_15 month_04  |
| proj_8 type_invdtl year_15 month_04  |
| proj_10 type_invdtl year_15 month_04 |
+--------------------------------------+
3 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT * FROM test WHERE MATCH(tags)
    -> AGAINST ('"+proj_7" "(invoice report)"' IN BOOLEAN MODE);
+-------------------------------------+
| tags                                |
+-------------------------------------+
| proj_7 type_invdtl year_15 month_04 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql>

OTHER EXAMPLES

mysql> SELECT * FROM test WHERE MATCH(tags)
    -> AGAINST ('"+proj_7" "(type_invdtl year_15)"' IN BOOLEAN MODE);
+--------------------------------------+
| tags                                 |
+--------------------------------------+
| proj_7 type_invdtl year_15 month_04  |
| proj_8 type_invdtl year_15 month_04  |
| proj_10 type_invdtl year_15 month_04 |
+--------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM test WHERE MATCH(tags)
    -> AGAINST ('"+proj_7" "(type_invdt2 year_15)"' IN BOOLEAN MODE);
+-------------------------------------+
| tags                                |
+-------------------------------------+
| proj_7 type_invdtl year_15 month_04 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE MATCH(tags) AGAINST ('"+proj_7 +type_invdtl" "+proj_7 +year15"' IN BOOLEAN MODE);
+-------------------------------------+
| tags                                |
+-------------------------------------+
| proj_7 type_invdtl year_15 month_04 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE MATCH(tags) AGAINST ('"+proj_7 +type_invdtl" "+proj_7 +year16"' IN BOOLEAN MODE);
+-------------------------------------+
| tags                                |
+-------------------------------------+
| proj_7 type_invdtl year_15 month_04 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE MATCH(tags) AGAINST ('"+proj_7 +type_invdt2" "+proj_7 +year16"' IN BOOLEAN MODE);
Empty set (0.00 sec)

mysql>

GIVE IT A TRY !!!

Related Question