Simple implementation of MySQL multi-field fuzzy query

In the background of the project, will often encounter, is to find information, here is a simple example, such as search for a product information, in a table has more than one field, and the content of the search may be one of the fields, so that only a multi-field query can be implemented.

MySQL multi-field fuzzy query: A fuzzy query that makes multiple keywords in a form, and one of the keywords in all fields (you can also associate multiple table queries).

For example, the table product contains four field IDs, name, title, description product IDs, names, titles, and descriptions, so you can find the product's information as soon as you enter one of the keywords.

A more accurate search method is to work with multiple keywords while searching, typically by dividing the user-entered string by a space, forming multiple keywords, and then finding the records that contain these keywords from these fields.

It is obvious that a single query is being made on a field, which is unrealistic, to implement a multi-field query using the MySQL concat () function:

Syntax: concat (str1, str2, ...)

Return value: By all input parameters str1, str2, ... The string to be merged together. Null is returned as long as the value of the input parameter is the Null,concat () function.

MySQL implementation of multi-field query can be how to write:

1 Select  from where ' % keyword% ';

This way of writing, to ensure that the concat inside the field Id,name,title,description value can not be null, otherwise the data detected is incomplete.

If a field is not set to NOT NULL when the product table is being built, then the MySQL multi-field Search SQL statement changes to:

1 Select  from where concat (ifnull (ID,'), ifnull (name,'), Ifnull (title,'), Ifnull ( Description,'% keyword%';

This way, there is no missing data.

Simple implementation of MySQL multi-field fuzzy query