Solve the Problem of MySQL database Chinese fuzzy search

InMySQLUnderChinese fuzzy searchIf you want to query "% a %", some irrelevant records are often returned. For example, if you want to query "% a %", Chinese characters may be returned, but no a character exists. I have encountered similar problems before. After reading MySQL Manual in detail, I found that there is a convenient solution and satisfactory results.

Example:

You want to search the news library using the "title". The keywords may contain Chinese and English. The following SQL statement:

QUOTE:

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

In the returned results, some title fields are identified with the "a" keyword, while some are only Chinese, but are also returned in the search results.

Solution: Use the BINARY Attribute for retrieval, such:

QUOTE:

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

The returned results are correct, but the English letters are case-sensitive. Therefore, sometimes the results for searching such as "Achech" and "achech" are different. Once you know that you can use the BINARY Attribute to solve the preceding problem, let's look at the UCASE and CONCAT functions supported by MySQL. UCASE converts all English words into uppercase letters, the function CONCAT is used to concatenate characters. The following is the SQL statement after we completely solve the problem:

QUOTE:

Select id, title, name from achech_com.news

Where binary ucase (title) like concat ('%', ucase ('A'), '% ')

The search procedure is to first specify the attribute as BINARY for exact search results. The content of the like title may contain uppercase/lowercase letters, therefore, the ucase function is used to convert all the fields into uppercase letters and then perform the like operation. The like operation uses the fuzzy method, the advantage of using concat is that the input can be a direct keyword, without the "%" universal character, replace "'A'" with your variable directly, everything is easy in any language. You can also write the following statement:

QUOTE:

Select id, title, name from achech_com.news where binary ucase (title) like ucase ('% a % ')

The search results are satisfactory, but the speed may be slow for N milliseconds.