In a recent project, it is necessary to implement a single-table multi-keyword fuzzy query in MySQL, but these keywords do not necessarily exist in a certain field.For example, the existing table table has three fields title, tag, and description, which record the title, tag, and introduction of a piece of information.Then, according to the query request input by the user, the input string is divided into multiple keywords by spaces, and then the records containing these keywords are searched in these three fields.


The problem currently encountered is that these keywords may exist in any one or more of the three fields, but it is required that the three fields must contain all keywords.If you perform fuzzy matching on each field separately, it is impossible to achieve the required requirements, so two methods can be thought of:

When inserting the record, merge the fields that need to be multi-field fuzzy query into one The string is added to a new field, and then a fuzzy query is performed on this new field.Use full-text search, but this requires Chinese word segmentation or conversion of Chinese characters into pinyin (separation of Chinese characters is not feasible, MySQL defaults to the minimum FT byte of 4), and it is not conducive to future maintenance.
After two days of crawling on the Internet, I did not find a satisfactory solution to this problem.Finally, I turned to the use of CONCAT in the "MySQL Authoritative Guide".The description of CONCAT in the book is:

CONCAT(str1,str2,...)
Return value: The string obtained by combining all the input and output parameters.As long as there is a NULL value in the input parameter, NULL is returned.CONCAT allows only one input parameter.

Therefore, MySQL single-table multi-keyword fuzzy query can be realized by the following SQL query
SELECT * FROM `magazine` WHERE CONCAT(`title`,`tag`,`description`) LIKE'% Keyword %'