How to use the full text search function of mysql5.7.25

Preface

Sometimes the full-text search function needs to be used in the project. If the number of full-text searches is relatively small, and you do not want to build a separate dedicated indexing tool like elasticsearch, you can consider using the full-text search that comes with mysql. Function.

Mysql 5.7.25 comes with a full-text search function, which is very convenient.

Before MySQL 5.7.6, full-text index only supported English full-text index and did not support Chinese full-text index. It was necessary to use a word segmenter to preprocess the Chinese paragraphs and split them into words, and then store them in the database.

Starting from MySQL 5.7.6, MySQL has a built-in ngram full-text parser to support Chinese, Japanese, and Korean word segmentation.

The MySQL version used in this article is 5.7.25, InnoDB database engine.

1. Create a table with a full-text index

CREATE TABLE `tbl_article_content` (
  `id` bigint(40) NOT NULL AUTO_INCREMENT,
  `article_title` varchar(60) COMMENT '标题',
  `article_summary` varchar(120) COMMENT '摘要',
  `article_content` text NOT NULL COMMENT '内容',
  `article_id` bigint(40) NOT NULL COMMENT '对应文章ID',
  `create_date` datetime NOT NULL COMMENT '创建时间',
  `modified_date` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `artid` (`article_id`) USING BTREE,
  FULLTEXT KEY `article_content` (`article_content`) /*!50100 WITH PARSER `ngram` */ 
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

/*!50100 */ It means version 5.01.00 or higher, only execute.

WITH PARSER `ngram` is the specified word segmentation engine.

2. Add full-text index

If the full-text index is not added when creating the table, you can add it after creating the table.

create fulltext index article_content on tbl_article_content(article_content) WITH PARSER ngram;

3. Add test data

INSERT INTO `tbl_article_content` VALUES ('2', '文章标题', '文章摘要', '文章内容', '2', '2022-02-05 13:47:55', '2022-02-05 13:47:59');

4. Execute query

mysql> select * FROM tbl_article_content222 WHERE MATCH(article_content) AGAINST('内容');
+----+---------------+-----------------+-----------------+------------+---------------------+---------------------+
| id | article_title | article_summary | article_content | article_id | create_date         | modified_date       |
+----+---------------+-----------------+-----------------+------------+---------------------+---------------------+
|  2 | 文章标题      | 文章摘要        | 文章内容        |          2 | 2022-02-05 13:47:55 | 2022-02-05 13:47:59 |
+----+---------------+-----------------+-----------------+------------+---------------------+---------------------+
1 row in set

The keywords for full-text query are MATCH and AGAINST.

5. Grammar

MATCH (col1,col2,...) AGAINST (expr [search_modifier])
search_modifier: { IN BOOLEAN MODE | WITH QUERY EXPANSION }

For example: SELECT * FROM tab_name WHERE MATCH ('Column name 1, column name 2...Column name n') AGAINST ('Word 1 Word 2 Word 3 ... word m');

That is: MATCH is equivalent to the column to be matched, and AGAINST is the content to be found.

The above is the detailed content of How to use the full text search function of mysql5.7.25. For more information, please follow other related articles on the PHP Chinese website!