MySQL simple implementation of multi-field fuzzy query

MySQL simple implementation of multi-field fuzzy queryOriginal May 03, 2016 16:40:20

• Label:
• Java /
• Programming /
• MySQL /

• 11648
• Edit
• Delete

I did the mall project before the new requirements, ask the front desk search products in addition to through the product name search, but also through another information search, such as: Product number, details description and so on, similar to full-text search. The first thing I think about is lucene, but the amount of changes to the code is too great .... The current online version if this change is afraid to test what the moth, if re-build the table to store this information in addition and feel good trouble ... So I think about the SQL statement above the fuss. Search on the internet there is really a way. That's MySQL single-label multi-field fuzzy query.

MySQL single-table multi-field fuzzy query refers to a fuzzy query that implements multiple keywords in a single table, but these keywords do not necessarily exist in a field. Of course, your multi-table related queries can also be done.

For example, existing table tables, which have title,tag,description three fields, record the title, label, and description of a piece of data, respectively. Then, based on the query request entered by the user, the input string is separated into several keywords through a space, and then the records containing these keywords are queried in these three fields.

The problem is that these keywords are either one or more of the three fields, but require three fields to contain all the keywords. If you are blurring each field separately, it is not possible to achieve the required requirements, and there are two ways to think about it:

While inserting a record, merge the fields of the MySQL single-table multi-field fuzzy query into a single string and add it to a new field, and then make a fuzzy query on the new field.
Use full-text search, but this requires the use of Chinese word segmentation or conversion of Chinese characters to pinyin (it is not feasible to split Chinese characters, mysql default ft minimum byte is 4), and is not conducive to future maintenance.
The use of concat in the MySQL authoritative guide, described in the book as Concat, is:

CONCAT (STR1,STR2,...)
Return value: The string that is obtained by merging all the access parameters together. Null is returned as long as there is a null value in the input parameter. Concat allows only one input parameter.

Therefore, MySQL single-table multi-field fuzzy query can be implemented by the following SQL query

[SQL]View PlainCopyPrint?

1. SELECT  *  from ' magazine ' WHERE CONCAT (' title ', ' tag ', ' description ') like '% keyword% '

SELECT * from ' magazine ' WHERE CONCAT (' title ', ' tags ', ' description ') like '% ' keyword% '

But there is a problem, if the three fields have a value of NULL, then the return is also null, then this record may be missed, how to handle it, I use ifnull to judge, then SQL instead:

[SQL]View PlainCopyPrint?

1. <pre name = "Code" class= "SQL" > SELECT  *  from ' magazine ' WHERE CONCAT (ifnull (' title ','), ifnull (' tag ','), Ifnull (' Description ',') like '% keyword% '

<pre name= "code" class= "SQL" >select * from ' magazine ' WHERE CONCAT (ifnull (' title ', '), ifnull (' tag ', '), Ifnull (' Description ', ') like '% keyword% '

Similar to this, a simple multi-field fuzzy search is possible.

MySQL simple implementation of multi-field fuzzy query