Implementation of Multi-Keyword fuzzy query for a single MySQL table

The implementation of multi-Keyword fuzzy query in a single MySQL table is a question that many people want to know very much. So, what method can be used to implement multi-Keyword fuzzy query in a single MySQL table?

In a recent project, you need to perform fuzzy search for multiple keywords in a single MySQL table. However, these keywords do not necessarily exist in a field. For example, in an existing table, there are three fields: title, tag, and description, respectively recording the title, tag, and description of a data item. Then, based on the query request entered by the user, the input string is divided into multiple keywords by space, and then the records containing these keywords are queried in these three fields.

The problem is that these keywords may exist in any or multiple of the three fields, but the three fields must contain all the keywords. If fuzzy match is performed on each field, the required requirements cannot be met. Therefore, two methods can be used:

When inserting a record, combine the fields that require multi-field fuzzy query into a string and add it to a new field. Then, perform fuzzy query on the new field. Full-text search is used, but Chinese Word Segmentation or conversion of Chinese characters into Pinyin is not feasible. MySQL uses a minimum FT byte of 4 by default, which is not conducive to future maintenance.
After crawling online for two days, I did not find a satisfactory solution to the problem. Finally, I found the use of CONCAT In the MySQL authoritative guide, the description of CONCAT in the book is:

CONCATstr1, str2 ,...)
Return Value: the string obtained by merging all input and output parameters. If the input parameter has a NULL value, NULL is returned. CONCAT allows only one input parameter.

Therefore, MySQL Single-Table multi-Keyword fuzzy query can be achieved through the following SQL query
SELECT * FROM 'magazine' where concat 'title', 'tag', 'description') LIKE '% keyword %'

Default length of common MySql Fields

Length of int data type in mysql

General INSERT usage in MySQL

MySQL index type

How to add or delete a primary key in mysql