Get better Full-text search results in MySQL

MySQL provides a solution for this problem based on the built-in Full-text lookup. Here, developers simply mark out fields that require Full-text lookup, and then run the search in those fields using a special MySQL method, which not only improves performance and efficiency (because MySQL indexes these fields to optimize the search), but also enables higher quality searches, Because MySQL uses natural language to intelligently rank results to get rid of irrelevant items.

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

1. Set the basic form

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

The code is as follows	Copy Code
Mysql> CREATE TABLE Reviews (ID INT (5) PRIMARY KEY not NULL auto_increment, data TEXT);

The above command creates a simple music album database (mainly the entire paragraph text), and then adds some records to the table:

The code is as follows

Copy Code

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 '); 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 data entry: 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. Define Full-text Search Fields

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

The code is as follows	Copy Code
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:

The code is as follows

Copy Code

Mysql> show INDEXES from reviews; +---------+---------------+--------+------+------------+---------+ | Table | column_name | Packed | Null | Index_type | Comment | ----------+---------------+--------+------+------------+---------+ | Reviews | ID | NULL | | Btree | | | Reviews | Data | NULL | YES | Fulltext | | +---------+---------------+--------+------+------------+---------+ 2 rows in Set (0.01 sec)

3. Run full-Text search

When you have the data and index, you can use the MySQL Full-text search, the simplest Full-text search method is with match ... Against a select query for a statement, the following is a simple example to find records that contain the word "single":

The code is as follows	Copy Code
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 in the normal way. Note You can pass more than one field to view with match ()-Just separate the field list with commas.

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

To see how each record is scored, just return to the match () method as part of the result set, as follows:

The code is as follows	Copy Code
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. Use the logical search modifier (Boolean modifiers)

You can also use the logical search modifier for more precise searches, which are implemented by adding a special in BOOLEAN mode modifier in the against statement, in the following example, to find records that contain the word "single" but do not have "Madonna":

The code is as follows	Copy Code
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 typically used to search for word fragments (rather than full words), which can be achieved by the * (asterisk) operator in the in BOOLEAN mode statement, and the following example shows how to find a record in a word that contains "hot":

The code is as follows	Copy Code
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, and the following example looks for records that contain at least one of the words "hell" and "rocks":

The code is as follows	Copy Code
Mysql> SELECT ID from reviews WHERE MATCH (data) Against (' Hell Rocks ' in BOOLEAN MODE); +----+ | ID | +----+ | 1 | | 3 | +----+ 2 rows in Set (0.00 sec)

The above examples demonstrate the relative to the traditional select ... Like statement, a more efficient way to do full-text search, you can try this method the next time you need to write a MySQL database search interface.