Can I perform a MySQL search using free text words?

user_4715656 0
Viewed 54

How can I use free text search entries to find items in a MySQL table?

For example, I have a simple table of wines like below:

id  |  wine
================================
 1    Bollinger Special Cuvée Brut
 2    Bollinger R.D. Extra Brut 
 3    Bollinger La Grande Année  
 4    Bollinger La Grande Année Brut

I have earlier used the LIKE pattern method to be able to search for e.g. "La Grande" (WHERE wine LIKE '%La Grande%'), and it works fine for more exact search phrases.

But if the user provides a string such as "Bollinger Brut", or "Cuvee" (without the grave accent) then the LIKE pattern method won't find any matches. Can I solve this using some MySQL tricks, or do I need some other method/algorithm to be able to make more ad hoc searches?

mysql
1 Answers

If you have a FULLTEXT index like this:

CREATE TABLE `wine` (
  `id` int DEFAULT NULL,
  `wine` varchar(200) DEFAULT NULL,
  FULLTEXT KEY `wine` (`wine`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

then:

select * from wine where match(wine) against('Bollinger Brut' in boolean mode);

will select:

+------+--------------------------------+
| id   | wine                           |
+------+--------------------------------+
|    1 | Bollinger Special Cuvée Brut   |
|    2 | Bollinger R.D. Extra Brut      |
|    4 | Bollinger La Grande Année Brut |
|    3 | Bollinger La Grande Année      |
+------+--------------------------------+

and:

mysql> select * from wine where match(wine) against('Cuvee' in boolean mode);
+------+------------------------------+
| id   | wine                         |
+------+------------------------------+
|    1 | Bollinger Special Cuvée Brut |
+------+------------------------------+

NOTE:

select 
   id, 
   wine, 
   ROUND(match(wine) against('Bollinger Brut' in boolean mode) ,5) score
from wine 
where match(wine) against('Bollinger Brut' in boolean mode);

will show:

+------+--------------------------------+---------+
| id   | wine                           | score   |
+------+--------------------------------+---------+
|    1 | Bollinger Special Cuvée Brut   | 0.01561 |
|    2 | Bollinger R.D. Extra Brut      | 0.01561 |
|    4 | Bollinger La Grande Année Brut | 0.01561 |
|    3 | Bollinger La Grande Année      | 0.00000 |
+------+--------------------------------+---------+

Because the "Bollinger La Grande Année" does not have "Brut" score is lower. (But it is not 0 !)

user_7240390
Related