How to realize fuzzy query of single table and multi-fields in MySQL
Today, I will talk to you about how to achieve single-table multi-field fuzzy query in MySQL, many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.
How to realize MySQL single-table and multi-field fuzzy query
For example, the existing table table has three title,tag,description fields that record the title, label and introduction of a piece of data. Then, according to the query request entered by the user, the input string is divided into multiple keywords by spaces, and then the records containing these keywords are queried in these three fields.
But the problem now is that these keywords may exist in any one or more of the three fields, but all three fields must contain all the keywords. If you fuzzy match each field separately, it is impossible to achieve the required requirements, so you can think of two ways:
At the same time of inserting records, the fields that need to be fuzzy queried by MySQL single table and multi-fields are merged into a string and added to a new field, and then the new field is vaguely queried.
Use full-text search, but this requires the use of Chinese word segmentation or the conversion of Chinese characters into pinyin (splitting Chinese characters is not feasible, MySQL default FT minimum byte is 4), and is not conducive to future maintenance.
How to realize MySQL single-table and multi-field fuzzy query
The use of CONCAT in the authoritative Guide to MySQL is described in the book as follows:
CONCAT (str1,str2, …)
Return value: a string obtained by combining all the entry and exit parameters. NULL is returned as long as there is a null value in the input parameter. CONCAT allows for cases where there is only one input parameter.
Therefore, the MySQL single-table multi-field fuzzy query can be implemented by the following SQL query
SELECT* FROM`magazine`WHERECONCAT (`title`, `tag`, `substitution`) LIKE'% keyword%'
After reading the above, do you have any further understanding of how to implement single-table multi-field fuzzy query in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.
