MySQL like Chinese search Problems

Update 20120531: MySQL case-insensitive query statement:

Select   Game_name
From   Game_list
Where   Binary   Ucase (   Game_name   )   Like   Concat (   Ucase (   'Q'   )   ,   '%'   )  

In the like Query of MySQL, we found that Chinese search is always not in competition. We have found several solutions on the Internet. You can look at them:

Method 1:

The solution is to add the "binary" attribute to a field that contains Chinese characters for Binary comparison. For example, change "name char (10)" to "name char (10) binary ".

Method 2:

If you use the source code to compile MySQL, you can use the -- with -- charset = GBK parameter when compiling MySQL, so that MySQL can directly support Chinese searching and sorting.

Method 3:

You can use the locate function of MySQL to determine. The preceding problem is used as an example:

Select * from table where locate ('lil', field)> 0;

Method 4:

Change your select statement to this way. Select * from table where fields like binary '% find % '.

The cause of the problem is:

In MySQL, Chinese Character sorting and search results are incorrect. This situation exists in many MySQL versions. If this problem is not solved, MySQL cannot actually process Chinese characters.

MySQL is case-insensitive when querying strings. In programming MySQL, The ISO-8859 character set is generally used as the default character set. Therefore, the case-sensitive conversion of Chinese encoded characters causes this phenomenon during comparison.

The third method I used is select * From table_name where locate ('keyword ', 'field name')> 0;

Locate (substr, STR)

Returns the position of the substring substr that appears for the first time in the STR string. If the substring substr does not exist in STR, the return value is 0:

Mysql> select locate ('bar', 'foobarbar ');

-> 4