Temporary solutions for inaccurate like fuzzy query of Chinese fields in Mysql database

In mysql, some records irrelevant to query words are sometimes returned during like search. For example, when "% s %" is searched, the returned results may contain Chinese characters, but there is no s character, which is related to the Chinese encoding rules of the database.

If you want to search for all news with the title containing the letter s:

Select * from test. news where title like '% s %'

Some of the returned results contain s, while others only have Chinese characters, which is depressing (and evil, hey ).

After testing, we found that one solution is to use BINARY (YesCAST (StrAs binary)(Short) Mandatory conversion for retrieval

Select * from test. news where binary title like '% s %'

In this way, the results are relatively accurate, but there is another problem: the letters are case-sensitive and the retrieval is still incorrect, so we have to convert them again:

Select * from test. news where binary ucase (title) like '% s %'

In this case, the temporary solution will solve the problem, but the speed will slow down. We hope that the problem can be solved in the future mysql version.