MySQL Full-text Indexing Guide

Full-text indexing requires special query syntax. Full-text search can be performed with or without an index, but the existence of an index will increase the matching speed. The index of the full-text index is stored in a special structure to facilitate finding the content corresponding to the search keyword in the document. In our daily lives, the most common full-text search is the Internet search engine. Although the data in web search engines is very large and relational databases are usually not used, the principles are similar.

Full-text indexing supports character-based retrieval (CHAR, VARCHAR, and TEXT type columns), and also supports natural language mode (Natural Language Mode, default) and Boolean mode (Boolean Mode). For example, when we search for "database engine", content including "database", "engine" and "database engine" will be retrieved. The implementation of full-text indexing has a lot of limitations and is very complex. However, since it is built into the MySQL server and can meet the requirements of many applications, it is widely used.

In versions prior to MySQL 5.6, only the MyISAM storage engine supported full-text indexing. To create a full-text index, you need to specify a column to mark as a full-text index, such as the content column below.

CREATE TABLE t_news (
	id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	content TEXT,
	author VARCHAR(32),
	title VARCHAR(128),
  FULLTEXT (content)
)ENGINE=InnoDB;

Before MySQL 5.6, the support for Chinese search was not very good. You had to perform word segmentation yourself and then pre-process the paragraphs into words before storing them in the database. MySQL 5.7.6 and later had a built-in word segmenter, ngram. Ngram supports setting the length of word segments, which can split Chinese into different words by length (although it is not very smart, it meets most scenarios). You can set the word segmentation length through the MySQL global variable ngram_token_size. The default value is 2, and it supports 1-10 options. For the above example, you need to specify a word breaker to build a full-text index.

CREATE TABLE t_news (
  id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	content TEXT,
	author VARCHAR(32),
	title VARCHAR(128),
  FULLTEXT KEY idx(content) WITH PARSER ngram
)ENGINE=InnoDB;

Insert a data test.

INSERT INTO `t_news` 
(`id`, `content`, `author`, `title`) 
VALUES ('1', 'I have a database and engine', 'Coder on the island', 'Database engine');

In simple fuzzy search, LIKE can be used to complete it, while for full-text search, the following statement needs to be used:

SELECT * FROM t_news 
WHERE MATCH (content) AGAINST ('data engine' IN NATURAL LANGUAGE MODE)

This way you can retrieve the content that was just inserted, which is not possible using LIKE. It also supports sorting by relevance and inserting another piece of data:

INSERT INTO `t_news`
(`id`, `content`, `author`, `title`) 
VALUES (2, 'I have a database', 'Coder on the island', 'Database')

Then perform a sort query:

SELECT *, MATCH (content) AGAINST ('data engine' ) AS relevance
FROM t_news 
WHERE MATCH (content) AGAINST ('data engine' ) 
ORDER BY relevance ASC

Here, the matching values ​​are queried as a column so that they can be sorted using their aliases. The higher the correlation, the larger the corresponding relevance value, so it can be used for sorting. If the investment is irrelevant, the relevance value is 0.

Boolean mode allows for more control, such as including the use of + to retain matching results and - to exclude matching results. The following matches the data but excludes the data containing the engine. For more operators, please refer to the official MySQL documentation: Full-text index operators.

SELECT * FROM t_news 
WHERE MATCH (content) AGAINST ('+data*-engine' IN BOOLEAN MODE);

The above is the detailed content of the MySQL full-text index usage guide. For more information about MySQL full-text index, please pay attention to other related articles on 123WORDPRESS.COM!