Shulou(Shulou.com)06/01 Report--

Full-text search support MATCH is provided above mysql 4.x. AGAINST mode (case-insensitive)

The storage engine type of the table that establishes the full-text index must be MyISAM

The problem is that match against does not support Chinese fuzzy search very well.

Create a new table of type utf8 MyISAM and create a full-text index:

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

FULLTEXT (title, body) establishes a full-text index for title and body, and then note that both columns must be specified when retrieving them.

Add some test data to this table

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...')

Full-text retrieval test

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

Note that the values in MATCH (title,body) must be the two fields in which the full-text index was established earlier.

By default, mysql supports full-text retrieval with a character length of 4. You can use SHOW VARIABLES LIKE 'ft_min_word_len' to view the specified character length, or you can change the minimum character length in the mysql configuration file my.ini by adding a line in my.ini, such as: ft_min_word_len = 2, and restart mysql after the change.

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

Mysql calculates the weight of each appropriate word in the set and query first, and a word that appears in multiple documents will have a lower weight (maybe even a zero weight) because it has a lower semantic value in this particular set. Otherwise, if there are fewer words, it will get a higher weight, the default threshold of mysql is 50%, the above 'you' appears in every document, so it is 100%, and only those less than 50% will appear in the result set.

Full-text retrieval grammar

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

Represents AND, that is, it must be included. -indicates NOT, that is, it does not contain.

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

There is a space between apple and banana, and the space represents OR, which contains at least one of apple and banana.

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

You must include apple, but you will get a higher weight if you also include banana.

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

~ is the familiar XOR operator. The returned record must contain apple, but it will reduce the weight if it also contains banana. But it is not as strict as + apple-banana, because the latter does not return at all if it contains banana.

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+ apple + (> banana)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.