Five solutions to MySQL string case sensitivity Problems

The case sensitivity of the MySQL string often causes us to suffer from the case sensitivity of the registration name when registering a website. The cause of this problem is that MySQL is case insensitive when querying strings, when programming MySQL, The ISO-8859 character set is generally used as the default character set, this character set is not case sensitive to MySQL string, therefore, the case-sensitivity conversion of Chinese Encoded Characters During the comparison process causes the MySQL string to be case-insensitive.

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 (field, 'lil')> 0;

Method 4:
Change your Select statement to this way. SELECT * from table where fields like binary '% FIND %!

Method 5:
If you use compiled MySQL in Windows, you can modify the character set options in My. ini.
Default-character-set = gb2312

If your server uses the win system, Method 5 is the best solution! For a linux system, use method 2! If it is not your own machine, you can use method 1, so that your user name is case sensitive!

MySQL string segmentation implementation

Replace string with the MySQL replace Function

Usage of MySQL string connection functions

Mysql query case

How to Implement MySQL full-text Query