Solve MySQL database Chinese fuzzy search problem _ MySQL

In MySQL, some irrelevant records are often returned during fuzzy search of Chinese characters. for example, when % a % is found, the returned records may contain Chinese characters, 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. (M?] SPEA 'a @) Vfia example: in MySQL, some irrelevant records are often returned during Chinese fuzzy search, such as when searching for "% a %, the returned result may contain Chinese characters, 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. (M?] SPEA 'a
@) V/fia
Example: jLihNH? R}
B] | 0dP;
You want to search the news library using the "title". The keywords may contain Chinese and English. the following SQL statement: 2WXj> & d
"T eLa | n
QUOTE :! 7 "f'ue ~>
Select id, title, name from achech_com.news where title like '% a %' 1 $ | CVhBb %
PBW66B1h
: @ AB /\
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. At * 5je
YUg/^ Q
Solution: Use the BINARY attribute for retrieval, such as:) M # [W'' =
^ "'; F |
QUOTE:) g4wt [= _ +
Select id, title, name from achech_com.news where binary title like '% a %') NyFPh
{S Af D | wq3 $
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. Now that we know that using the BINARY attribute can 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 connect characters. The following is the SQL statement after we completely solve the problem :. "_? J H
^-<* GF-X ^
QUOTE: Select id, title, name from achech_com.news \ Uhl4y/M
Where binary ucase (title) like concat ('%', ucase ('A'), '%') "+ b2KV! Ts
N; {I #6n0
G = Cwy
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. Of course, you can also write like this: y
L. gP ++ ~ B + p
QUOTE: hkM $ w ~ *
Select id, title, name from achech_com.news where binary ucase (title) like ucase ('% a %') ASs/] hs
"2}) CL2; B
W {xuu2o *,
The search results are satisfactory, but the speed may be slow for N milliseconds.