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

This article introduces the relevant knowledge of "how to use the full-text search function of mysql5.7.25". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Preface

Sometimes the full-text retrieval function is needed in the project. If the number of full-text retrieval is relatively small, and you do not want to build a special indexing tool such as elasticsearch, you can consider using the full-text retrieval function of mysql.

The full-text retrieval function of mysql 5.7.25 is convenient to a certain extent.

Before MySQL 5.7.6, full-text indexing only supports English full-text indexing, but not Chinese full-text indexing. It is necessary to use a word splitter to preprocess Chinese paragraphs into words and store them in the database.

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

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

1. Create tables with full-text indexing: CREATE TABLE `create_ content` (`id`bigint (40) NOT NULL AUTO_INCREMENT, `article_ title`varchar (60) COMMENT 'title', `article_ content`varchar 'COMMENT' summary', `article_ content`text NOT NULL COMMENT 'content', `article_ id`bigint (40) NOT NULL COMMENT 'correspond to ID', `create_ date`datetime NOT NULL COMMENT', `modified_ date`datetime NOT NULL COMMENT 'update time, PRIMARY KEY (`id`) USING BTREE KEY `artid` (`artid`) 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.

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

two。 Add full-text index

If a full-text index is not added when the table is created, it can be added after the table is created.

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', 'article title', 'article summary', 'article content','2 articles', '2022-02-05 13-14-47 VALUES 55', '2022-02-05 13-14-47 VALUES'); 4. Execute query mysql > select * FROM tbl_article_content222 WHERE MATCH (article_content) AGAINST ('content') +-+ | id | article_title | | article_summary | article_content | article_id | create_date | modified_date | +-+ | -+ | 2 | Article title | Article summary | Article content | 2 | 2022-02-05 13:47:55 | 2022-02-05 13:47:59 | + -+ 1 row in set

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

5. Syntax 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. Name n') AGAINST ('word 1, word 2, word 3. The word m')

That is, MATCH is the equivalent of the column to match, and AGAINST is what you are looking for.

This is the end of the content of "how to use the full-text search function of mysql5.7.25". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.