MySQL-FULLTEXT pattern matching

Question

I have an MySQL-query which is made out of FULLTEXT-indexes because I need a relevance ordering. The disadvantage is that I need to have the pattern matching like

SELECT * FROM table WHERE colum LIKE %word%

Is there any way to implement this in FULLTEXT-searches?

Answer 1

Here is how you can use MySQL full Text search:

Here you need not have to break the bigger input string into an array to compare with each individual word.

Please refer the below examples to write your own:

A phrase that is enclosed within double quote (“"”) characters matches

only rows that contain the phrase literally, as it was typed.

I want to explain you about Boolean Full Text Search; But I advise you to please go through Full Text Search using Query Expansion also.

Let's look at the example table:

mysql> select * from articles;
+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  1 | PostgreSQL Tutorial   | DBMS stands for DataBase ...             |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  3 | Optimizing MySQL      | In this tutorial we will show ...        |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
+----+-----------------------+------------------------------------------+

mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('"database comparison"' IN BOOLEAN MODE);

+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+

Order matters, when the words are quoted:

mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('"comparison database"' IN BOOLEAN MODE);

Empty set (0.01 sec)

When we remove the quotes, it will search for rows, containing words "database" or "comparison":

mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('database comparison' IN BOOLEAN MODE);

+----+---------------------+------------------------------------------+
| id | title               | body                                     |
+----+---------------------+------------------------------------------+
|  1 | PostgreSQL Tutorial | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL   | In the following database comparison ... |
+----+---------------------+------------------------------------------+

Order doesn't matter now:

mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('comparison database' IN BOOLEAN MODE);

+----+---------------------+------------------------------------------+
| id | title               | body                                     |
+----+---------------------+------------------------------------------+
|  1 | PostgreSQL Tutorial | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL   | In the following database comparison ... |
+----+---------------------+------------------------------------------+

If we want to get rows, containing either word "PostgreSQL" or phrase "database comparison", we should use this request:

mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('PostgreSQL "database comparison"' IN BOOLEAN MODE);

+----+---------------------+------------------------------------------+
| id | title               | body                                     |
+----+---------------------+------------------------------------------+
|  1 | PostgreSQL Tutorial | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL   | In the following database comparison ... |
+----+---------------------+------------------------------------------+

fiddle

Make sure, that the words, you are searching for, are not in the list of stopwords, that are ignored.

Answer 2

Yes use MATCH AGAINST FULLTEXT SEARCH like this

SELECT * FROM tablename WHERE MATCH (columnname) AGAINST (' value ');

if you want to search from multiple tables then mention the tablenames separating by commas