Implementation of MySQL fuzzy query with multiple fields in a single table

MySQL fuzzy query is often encountered. The following describes how to implement MySQL fuzzy query. I hope it will help you learn MySQL fuzzy query.

MySQL fuzzy query is often encountered. The following describes how to implement MySQL fuzzy query. I hope it will help you learn MySQL fuzzy query.

MySQL fuzzy query is often encountered. The following describes how to implement MySQL fuzzy query. I hope it will help you learn MySQL fuzzy query.

In a recent project, you need to perform a MySQL fuzzy query with multiple keywords for multiple fields in a single 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 is required or Chinese characters are converted to PinYin (splitting Chinese characters is not feasible. MySQL uses the default FT minimum byte of 4), 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:

CONCAT (str1, 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, the preceding MySQL fuzzy query can be implemented through the following SQL query:

SELECT * FROM 'magazine' where concat ('title', 'tag', 'description') LIKE '% keyword %'