MySQL full-text fuzzy search MATCH AGAINST method example

MySQL 4.x and above provide full-text search support MATCH ... AGAINST mode (case-insensitive)

The storage engine type of the table for which the full-text index is created must be MyISAM

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

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

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

FULLTEXT(title, body) creates a full-text index for the title and body columns. When searching later, be sure to specify both columns.

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 search test

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

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

The default character length that MySQL supports for full-text search is 4. You can use SHOW VARIABLES LIKE 'ft_min_word_len' to view the specified character length. You can also change the minimum character length in the MySQL configuration file my.ini by adding a line to my.ini, such as: ft_min_word_len = 2. After changing it, restart MySQL.

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

MySQL will first calculate the weight of each appropriate word in the set and query. A word that appears in multiple documents will have a lower weight (possibly even a zero weight) because it has a lower semantic value in this particular set. Otherwise, if the word is less, it will get a higher weight. The default threshold of MySQL is 50%. In the above, 'you' appears in every document, so it is 100%. Only words below 50% will appear in the result set.

Full text search syntax

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

Indicates AND, which means it must be included. - means NOT, which means not included.

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

There is a space between apple and banana, which means 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);

Apple must be included, but it will be given a higher weight if banana is also included.

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

~ is the familiar exclusive-or operator. The returned records must contain apple, but if they also contain banana, the weight will be reduced. But it is not as strict as +apple -banana because the latter will not return anything 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. But the weight of the record containing both apple and banana is higher than the record containing both apple and orange.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.