How to Implement Multi-field fuzzy query in a single MySQL table

Multi-field fuzzy query in a single MySQL table refers to fuzzy query of multiple keywords in multiple fields in a single table, but these keywords do not necessarily exist in a specific 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, merge the fields that require multi-field fuzzy query in a single MySQL table 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.
The usage of CONCAT in MySQL authoritative guide is described as follows:

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, MySQL Single-Table multi-field fuzzy query can be achieved through the following SQL query
SELECT * FROM 'magazine' where concat ('title', 'tag', 'description') LIKE '% keyword %'
 

Quick table creation in mysql

In-depth study on MySQL's data deletion from multiple tables

Combination of multiple MySQL table result sets

Implementation of MySQL table sharding

How to copy data from a MySQL table to a new table