MySQL full-text retrieval notes

1. mySQL 4. full-text search is supported in Version X and later versions, but the storage engine type of the table must be MyISAM. The following table creation SQL statements are used. Note that the storage engine type is explicitly set.

CREATE TABLE articles (    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,    title VARCHAR(200),    body TEXT,    FULLTEXT (title,body)) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Fulltext (title, body) creates a full-text index for the title and body columns. You must specify these two columns at the same time.

 

2. Insert Test Data

INSERT INTO articles (title,body) VALUES    ('MySQL Tutorial','DBMS stands for DataBase ...'),    ('How To Use MySQL Well','After you went through a ...'),    ('Optimizing MySQL','In this tutorial we will show ...'),    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),    ('MySQL vs. YourSQL','In the following database comparison ...'),    ('MySQL Security','When configured properly, MySQL ...');

 

3. Full-text search test

SELECT * FROM articles    WHERE MATCH (title,body) AGAINST ('database');

The search result is as follows:

5        MySQL vs. YourSQL        In the following database comparison ...
1        MySQL Tutorial               DBMS stands for DataBase ...

Case Insensitive for full-text matching.

 

4. Possible troubles

So far it has been quite smooth, but what if I change the search SQL to the following?

SELECT * FROM articles    WHERE MATCH (title,body) AGAINST ('well');

As a result, I began to get confused for a long time. Then I checked it online to find out that this was the case:

MySQL specifies the minimum character length. The default value is 4. The returned result must be matched to a value greater than 4. You can use show variables like 'ft _ min_word_len 'to view the specified character length, you can also go to the MySQL configuration file my. INI to change the minimum character length, in the my. add a line for INI, for example, ft_min_word_len = 2. Restart MySQL after modification.

Therefore, the preceding results cannot be returned. However, I used the above method to modify the configuration file and restart the MySQL server, and then run the show command to check it.

In addition, MySQL calculates the weight of a word to determine whether it appears in the result set, as shown below:

MySQL calculates their weights for each appropriate word in a set and query. A word that appears in multiple documents has a lower weight (or even a zero weight ), because in this particular set, it has lower semantic values. Otherwise, if the word is less, it will get a higher weight. The default MySQL threshold value is 50%. The above 'you' appears in each document, so it is 100%, only less than 50% will appear in the result set.

But what should we do if we do not consider the weight? MySQL provides Boolean Fulltext search)

If well appears in all records and ft_min_word_len is changed to 2, The result set obtained by the following SQL query statement will contain all records:

 

SELECT * FROM articles WHERE MATCH (title,body)     AGAINST ('well' IN BOOLEAN MODE );

 

 

5. boolean full-text search syntax

In Boolean mode is used to specify the full-text retrieval mode as Boolean full-text retrieval. MySQL also provides some similar syntaxes that we usually use when using search engines: logic and, logic or, non-logic. Several SQL statement examples are used to describe

SELECT * FROM articles WHERE MATCH (title,body)     AGAINST ('+apple -banana' IN BOOLEAN MODE);

+ Indicates and, which must be included. -Indicates not, that is, not included.

SELECT * FROM articles WHERE MATCH (title,body)     AGAINST ('apple banana' IN BOOLEAN MODE);

There is a space between Apple and banana, and a space indicates or, that is, at least one of Apple and banana is included.

SELECT * FROM articles WHERE MATCH (title,body)     AGAINST ('+apple banana' IN BOOLEAN MODE);

It must contain apple, but if it also contains banana, it will get a higher weight.

SELECT * FROM articles WHERE MATCH (title,body)     AGAINST ('+apple ~banana' IN BOOLEAN MODE);

~ Is an exclusive OR operator that we are familiar. The returned record must contain apple, but if it also contains banana, the weight is reduced. However, it is not strict with apple-banana, because the latter does not return if it contains banana.

SELECT * FROM articles WHERE MATCH (title,body)     AGAINST ('+apple +(>banana <orange)' IN BOOLEAN MODE);

Returns records that contain both Apple and banana, or both Apple and orange. However, records that contain both Apple and banana have a higher weight than those that contain both Apple and orange.

 

6. MySQL does not support full-text retrieval in Chinese.

By default, MySQL does not support full-text Chinese search. What should I do? The general method is as follows:

A. It is difficult to extend MySQL to support Chinese full-text search.

B. A corresponding English index table is provided for the Chinese content table (the Fulltext index column is converted into every record in the English index table according to certain rules, for example, all are base64-encoded, the content table and the English index table have the same ID). During retrieval, the same search term is converted to English and then used. If you want to support full-text retrieval by pinyin, you also need to add the corresponding pinyin content to the index table (you need to convert Chinese to PinYin ). Of course, if you still need to support interactive searches between Chinese and English, for example, you also need to return William when searching William, and vice versa, you also need to save William's English translation to the index table.

Refer to the link on the Internet. The specific practices include first segmenting Chinese content, then converting Chinese to four-bit code and saving it to the index table. During retrieval, the search term containing Chinese characters must also be segmented, converted to a four-bit location code, and then searched in the full text in the index table.

 

7. Check entries

A. Only tables with the storage engine type MyISAM and the mysql version is 4.x or later can use MySQL's built-in full-text search support.

B. MySQL full-text search does not support Chinese characters by default, and case insensitive for English Retrieval

C. For MySQL full-text search, the default search length is 4, that is, the keyword length must be greater than 5 to be captured.

D. During MySQL full-text retrieval, all Fulltext index columns must use the same character set

E. weight is also considered when MySQL full-text search returns results

F. MySQL full-text retrieval also supports flexible Boolean full-text retrieval Modes

G. For more information, see the official mysql5 manual.

 

Reference link:

Http://viralpatel.net/blogs/2009/04/full-text-search-using-mysql-full-text-search-capabilities.html

Http://hi.baidu.com/gogogo/blog/item/28b16c81b3bc87d6bc3e1eb7.html

Http://dev.mysql.com/doc/refman/5.1/zh/functions.html#fulltext-query-expansion

Http://www.chinahtml.com/0702/mysql-117187149111362.html

Http://www.phpx.com/happy/viewthread.php? Tid = 124691