MySql full-text search syntax description

How to get better full-text search results in MySQL

Many Internet applications provide full-text search, users can use a word or phrase as a query to locate the matching records. In the background, these programs use LIKE statements in a SELECT query to perform such queries, and while this works, it is an extremely inefficient way for full-text lookups, especially when dealing with large amounts of data .

mysql provides a solution to this problem based on the built-in full-text search. Here, developers simply mark the fields that require full-text lookups and then run queries on those fields using special MySQL methods, which not only improves performance and efficiency (because MySQL indexed those fields to refine the search) , But also achieved a higher quality of search because MySQL uses natural language to intelligently rate results to eliminate irrelevant items.

This article will show you how to do a full-text search in MySQL.

1, set the basic form

Starting with creating the example table, use the following SQL command:

mysql> CREATE TABLE reviews (id INT (5) PRIMARY KEY NOT NULL AUTO_INCREMENT, data TEXT);

The above command creates a simple music album repository (mainly the whole paragraph of text) and then adds some records to this table:

mysql> INSERT INTO `reviews` (` id`, `data`) VALUES
(1, 'Gingerboy has a new single out called Throwing Rocks. It's great!');
mysql> INSERT INTO `reviews` (` id`, `data`) VALUES
(2, 'Hello all, I really like the new Madonna single.
One of the hottest tracks currently playing ... I've been listening to it all day ');
mysql> INSERT INTO `reviews` (` id`, `data`)
VALUES (3, 'Have you heard the new band Hotter Than Hell?
They have five members and they burn their instruments when they play in concerts.
These guys totally rock! Like, awesome, dude! ');

Verify the correct entry of data:

mysql> SELECT * FROM reviews;
+ ---- + -------------------------------------------- +
id data
+ ---- + -------------------------------------------- +
1 Gingerboy has a new single out called ...


2 Hello all, I really like the new Madon ...
3 Have you heard the new band Hotter Than ...
+ ---- + -------------------------------------------- +
3 rows in set (0.00 sec)

2, the definition of full-text search field

Next, define the fields that you want to use as the full-text search index

mysql> ALTER TABLE reviews ADD FULLTEXT INDEX (data);
Query OK, 3 rows affected (0.21 sec)
Records: 3 Duplicates: 0 Warnings: 0

Use the SHOW INDEXES command to check that the index has been added:

mysql> SHOW INDEXES FROM reviews;
+ --------- + --------------- + -------- + ------ + ------- ----- + --------- +
Table Column_name Packed Null Index_type Comment
---------- + --------------- + -------- + ------ + ------- ----- + --------- +
reviews id NULL BTREE
reviews data NULL YES FULLTEXT
+ --------- + --------------- + -------- + ------ + ------- ----- + --------- +


3, run full-text search

When you have the data and indexes, you can use MySQL full-text search, the simplest way of full-text search is the SELECT query with MATCH ... AGAINST statement, the following is a simple example, you can find contains the word "single "record of:

mysql> SELECT id FROM reviews WHERE MATCH (data) AGAINST ('single'); + ---- +
id
+ ---- +
1
2
+ ---- +

2 rows in set (0.00 sec)

Here, MATCH () compares the text in the field passed as a parameter to the argument passed to AGAINST () and, if there is a match, returns as normal. Note that you can pass more than one field with MATCH () to see - just split the field list with a comma.

When MySQL receives a request for a full-text search, it scores each record internally, mismatching records score zero, and "more relevant" records get a higher relative "less relevant" record fraction. Dependencies are determined by a series of criteria by MySQL, and you can get more information by looking at the MySQL user's manual.

To see how each score is recorded, just return the MATCH () method as part of the result set as follows:

mysql> SELECT id, MATCH (data) AGAINST ('rock') FROM reviews;


+ ---- + ------------------------------- +
id MATCH (data) AGAINST ('rock')
+ ---- + ------------------------------- +
1 0
2 0
3 1.3862514533815
+ ---- + ------------------------------- +

3 rows in set (0.00 sec)

4, the use of logical search modifiers (Boolean search modifiers)

You can also use the logical search modifier for a more precise search, by adding a special IN BOOLEAN MODE modifier to the AGAINST statement. In the following example, the search for a word that contains the word "single" but no "Madonna" record of:

mysql> SELECT id FROM reviews WHERE MATCH (data) AGAINST ('+ single-madonna' IN BOOLEAN MODE);
+ ---- +
id
+ ---- +
1
+ ---- +

1 row in set (0.00 sec)

This search feature is often used to search for word fragments (not complete words), which can be done with the * (asterisk) operator in the IN BOOLEAN MODE statement. The following example shows how to find words that contain "hot "record of:

mysql> SELECT id FROM reviews WHERE MATCH (data) AGAINST ('hot *' IN BOOLEAN MODE); + ---- +
id
+ ---- +
3
2
+ ---- +

2 rows in set (0.00 sec)

You can also use this method to find at least one parameter passed to AGAINST. The following example finds records that contain at least one of the words "hell" and "rocks":

mysql> SELECT id FROM reviews WHERE MATCH (data) AGAINST ('hell rocks' IN BOOLEAN MODE);
+ ---- +
id
+ ---- +
1
3
+ ---- +

3 rows in set (0.00 sec)

These examples above demonstrate a more efficient way to do full-text search than the traditional SELECT ... LIKE statement, which you can try out the next time you need to write a MySQL database search interface.