How to solve the problem of MySQL Chinese fuzzy search

In MySQL, some irrelevant fuzzy search results are often returned.
Record. For example, if "% a %" is found, 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 way to easily solve the problem and get satisfactory results.

Example:
· You want to search the news library by using the "title". The keywords may contain Chinese and English characters, as shown in figure
SQL statement:
Select id, title, name from achech_com.news where title like ''% a %''
In the returned results, some title fields contain the "a" keyword, while some are only Chinese,
But it is also returned in the search results.
Solution: Use the BINARY Attribute for retrieval, such:
Select id, title, name from achech_com.news where binary title like ''% a %''
The returned results are correct, but English letters are case-sensitive"
The results are different from those of "achech.
Now that we know that using the BINARY Attribute can solve the preceding problem, let's look at what MySQL supports.
UCASE and CONCAT functions, in which UCASE converts all English letters into uppercase letters, while the CONCAT Function
Number is used to connect characters. The following is the SQL statement after we completely solve the problem:
Select id, title, name from achech_com.news where binary ucase (title) like concat (''%'', ucase (''a'), ''% '')
The retrieval procedure is to first specify the attribute as BINARY to precisely retrieve the result, and the like title
The content may contain uppercase and lowercase letters. Therefore, the ucase function is used to convert all the content of the field to uppercase.
Write letters and then perform the like operation. The like operation uses the fuzzy method and concat
The advantage is that the input can be a direct keyword, without the "%" universal character, directly using "''a '"
With your variables, everything is easy in any language.
You can also write the following statement:
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.

Author: Lin Xinlu Linxinglu @ ihsf-com.cn

Related information:
Relate:
20.16 Case Sensitivity in Searches
By default, MySQL searches are case-insensitive (although there are some character sets that are never case insensitive, such as czech ). that means that if you search with col_name LIKE 'a % '', you will get all column values that start with a or. if you want to make this search case-sensitive, use something like INDEX (col_name, "A") = 0 to check a prefix. or use STRCMP (col_name, "A") = 0 if the column value must be exactly "".