Full-text search in MySQL

Full-text search in MySQL uses the relevance between the query keyword and the query column content for retrieval. Full-text indexes can be used to increase the matching speed.

Full-text search in MySQL uses the relevance between the query keyword and the query column content for retrieval. Full-text indexes can be used to increase the matching speed.

Ii. Syntax
MATCH (col1, col2,...) AGAINST (expr [search_modifier])
Search_modifier: {in boolean mode | with query expansion}
Example: SELECT * FROM tab_name where match (col1, col2) AGAINST (search_word );
Here, tables must be MyISAM tables, and col1 and col2 must be char, varchar, or text. Before querying, you must create a full-text index on col1 and col2.

1. Prerequisites for using Mysql full-text search fulltext

The table type must be MyISAM.
The field type for full-text search must be char, varchar, and text.

2. Create an advanced configuration for full-text search

Because the default configuration of Mysql is that the index word length is 4, to support Chinese words, first change this.
* Unix users need to modify my. cnf. Generally, this file is stored in/etc/my. cnf. If not found, find/-name 'my. cnf 'first'
Add the following content to the [mysqld] location:
Ft_min_word_len = 2
Other attributes include
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 indicates the character set of the dictionary, which currently supports (UTF-8, gbk, gb2312, big5)
Ft_wordlist_file is a Word Table file. Each line contains one word and its word frequency (separated by several tabs or spaces, dedicated for elimination)
Ft_stopword_file indicates filtering out non-indexed word lists, one row.
The minimum length of the word ft_min_word_len is added to the index. The default value is 4. To support Chinese words, change to 2.

3. Create a full-text search

The FullText keyword is used to identify the field in the TABLE under construction. The existing TABLE uses alter table (or create index) to CREATE an INDEX.
CREATE fulltext INDEX index_name ON table_name (colum_name );

4. Use full-text search

Use the MATCH function IN the WHERE clause of the SELECT statement. the keywords of the index are identified by AGAINST. in boolean mode only supports the keyword. You do not need to care about the position or whether it is the starting position.
SELECT * FROM articles where match (tags) AGAINST ('travel 'in boolean mode );

This section describes how to perform full-text search in MySQL.

1. Set the basic table

Use the following SQL command to create an example table:

The Code is as follows:
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 text in the entire segment), and then adds some records to the table:

The Code is as follows:

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 that the data is entered correctly:

The Code is as follows:
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 to be indexed as full-text search

The Code is as follows:
Mysql> alter table reviews add fulltext index (data ); Query OK, 3 rows affected (0.21 sec) Records: 3 Duplicates: 0 Warnings: 0

Run the show indexes command to check whether the index has been added:

The Code is as follows:

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 data and indexes, you can use MySQL full-text search. The simplest full-text search method is with MATCH... the SELECT query of the AGAINST statement. The following is a simple example to find records containing the word "single:

The Code is as follows:
Mysql> SELECT id FROM reviews where match (data) AGAINST ('sing'); + ---- + | Id | + ---- + | 1 | | 2 | + ---- + 2 rows in set (0.00 sec)

Here, MATCH () compares the text in the field passed to it as a parameter with the parameter passed to AGAINST (). If there is a MATCH, it is returned in the normal way. Note: You can pass more than one field and use MATCH () to view the field list. You only need to use commas to separate the field list.

When MySQL receives a full-text search request, it scores each record internally, and the non-matching record score is zero, the "more relevant" record will get a relatively higher score than the "less relevant" record. Relevance is determined by a series of MySQL differentiation standards. You can view the MySQL user manual to obtain more information.

To view the score of each record, you only need to return the MATCH () method as part of the result set, as shown below:

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

You can also use the logical search modifier for more accurate search, which is achieved by adding a special in boolean mode modifier to the AGAINST statement. IN the following example, the record containing the word "single" but not "Madonna" will be searched:

The Code is as follows:
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 usually used to search for a word segment (rather than a complete word), which can be achieved through the * (asterisk) operator IN the in boolean mode statement, the following example shows how to find records with "hot" In a word:

The Code is as follows:
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 a record that contains at least one of the words "hell" and "rocks:

The Code is as follows:
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 comparison with the traditional SELECT... LIKE statement, a more effective method for full-text search, when you need to write the MySQL database search interface next time, you can try this method