MySQL Full-Text Search detailed introduction

Second, the grammar
MATCH (Col1,col2,...) Against (expr [search_modifier])
Search_modifier: {in BOOLEAN MODE | With QUERY expansion}
For example: SELECT * from Tab_name WHERE MATCH (col1,col2) against (Search_word);
The table here needs to be MyISAM type tables, col1, col2 need to be char, varchar, or text type, and a Full-text index must be established on col1 and col2 before querying.

1. Prerequisites for using MySQL Full-text search fulltext

The type of the table must be MyISAM
The field type that establishes Full-text search must be Char,varchar,text

2. Establish the advance configuration of Full-text search

Because MySQL's default configuration is to index the length of the word is 4, so to support the Chinese word, first change this.
*unix users to modify MY.CNF, general this file in/etc/my.cnf, if not found, first look find/-name ' my.cnf '
Add in [mysqld] location:
Ft_min_word_len = 2
Other attributes also have
Ft_wordlist_charset = GBK
Ft_wordlist_file =/home/soft/mysql/share/mysql/wordlist-gbk.txt
Ft_stopword_file =/home/soft/mysql/share/mysql/stopwords-gbk.txt
A little explanation:
Ft_wordlist_charset represents the dictionary's character set, currently supported well (UTF-8, GBK, gb2312, Big5)
Ft_wordlist_file is a thesaurus file, each line including a word and its frequency (with a number of tabs or spaces separated, disambiguation dedicated)
Ft_stopword_file means to filter out the not indexed thesaurus, one line.
Ft_min_word_len the minimum length of the word added to the index, the default is 4, in order to support the Chinese word is changed to 2

3. Establishment of Full-text Search

Using the FULLTEXT keyword in a table, an existing table creates an index with ALTER table (or CREATE INDEX)
CREATE Fulltext INDEX index_name on table_name (COLUM_NAME);

4. Using Full-text Search

In the WHERE clause of SELECT, with the match function, the indexed keyword is identified with against, in BOOLEAN mode is only the keyword, not the location, the starting position.
SELECT * from articles WHERE MATCH (tags) against (' travel ' in BOOLEAN MODE);

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 The instruments when They the play in concerts. These guys totally rock! Like, awesome, dude! ');

Verify the correct data entry:

code is as follows

copy code

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