A solution to the problem of character set matching in MySQL Chinese query

My question when I write a query condition is as follows:

If I want to write all the records in a field that contain the word "Li"

? $str = "Lee";

SELECT * FROM table where field like '% $STR% ';

In addition to the records that contain the word "Li", there are records that do not contain the word "Li". Why?

In MySQL, the sorting and searching of Chinese characters are wrong when they are sorted and searched. This situation exists in many versions of MySQL. If this problem is not resolved, then MySQL will not be able to actually handle Chinese.

The reason for this problem is: MySQL in the query string is not sensitive to the case, when compiling the MySQL in general with the ISO-8859 character set as the default character set, so in the comparison process of Chinese encoded character case conversion caused this phenomenon.

Method One:

The workaround is to add the "binary" attribute to the field containing Chinese as a binary comparison, such as changing "name char (10)" to "name char" binary.

Method Two:

If you use the source code to compile MySQL, you can compile MySQL using the--WITH--CHARSET=GBK parameters, so that MySQL will directly support Chinese search and sorting.

Method Three:

You can use the Mysql locate function to determine. Take the above questions for example, using the following methods:

SELECT * FROM table WHERE locate (field, ' Lee ') > 0;

This site is the use of this method, it feels good. :P

Method Four:

Change your SELECT statement to this, select * from TABLE WHERE FIELDS like BINARY '%find% '!