A solution to the problem of MySQL Chinese fuzzy retrieval

Under MySQL, in the Chinese fuzzy search, often return some unrelated records, such as looking for "%a%", the return may have Chinese characters, but no a character exists. I have encountered similar problems before, after a detailed reading of MySQL manual, found that there can be a convenient way to solve and get satisfactory results.

Example:

You want to retrieve the news library by "title", which may contain both Chinese and English, the following SQL statement:

QUOTE:

Select Id,title,name from achech_com.news where title like '%a% '

Returns, some title fields are determined with the "a" keyword, while others have only Chinese, but are returned in the search results.

Workaround, using the BINARY property for retrieval, such as:

QUOTE:

Select Id,title,name from achech_com.news where binary title like '%a% '

The returned results are more correct than before, but the English letters are case-sensitive, so there are times when the results of retrieval such as "Achech" and "Achech" are different. Know the use of the BINARY property can solve the previous problem, and then look at the MySQL-supported UCASE and CONCAT functions, where UCASE is to all uppercase in English, and the function of the CONCAT function is to connect the characters, the following is our fully resolved SQL statement:

QUOTE:

Select Id,title,name from Achech_com.news

where binary UCase (title) like Concat ('% ', UCase (' a '), '% ')

The retrieval step is to specify the attribute as BINARY to accurately retrieve the result, while the title content of like is the possibility of uppercase and lowercase letters, so first use the UCase function to convert all the field contents to uppercase letters, and then do like operations, while like operations using the Fuzzy method, using the CO NCAT's advantage is that it can be a direct keyword, do not need to take the "%" million, the "' A '" directly into your variables, in any language everything is all right. Of course, you can also write this:

QUOTE:

Select Id,title,name from achech_com.news where binary UCase (title) Like UCase ('%a% ')

The results of the search are satisfactory, but the speed may be slower than n milliseconds.