3

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

1

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 !!!

  • maybe i'm trying to do something, what is not possible in fulltext search... but if i search proj_7 AND (invoice OR report) ... result should not contain rows with proj_7 only .. in proposed query there is one row in result, but there is no "invoice" or "report" in that row, so i dont want this row – opio Jun 2 '15 at 16:28
  • Added two more examples – RolandoMySQLDBA Jun 2 '15 at 16:29
  • Added three more examples (5 altogether) – RolandoMySQLDBA Jun 2 '15 at 16:32
  • yes, looking at last query you wrote, i can rewrite query "A AND (B OR C)" to "(A AND B) OR (A AND C)" so for this example it works.. but i have more complex query (i simplified it) what if i have "(A AND (B OR C) OR (D AND (E OR F OR G OR H))" .. i can't write queries using boolean logic? have to rewrite to all possibilities? – opio Jun 2 '15 at 16:42
  • Most likely you will have to code the possibilities. See my old post dba.stackexchange.com/questions/84402/… where I quote from the MySQL Documentation. – RolandoMySQLDBA Jun 2 '15 at 17:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.