MySQL query size-sensitive SQL statement Summary

The company website has the need for User name verification to verify the case. In fact, at the beginning of his registration is all the case, so there is no need to do the case verification. But now to log in the case of the verification, it is registered in the time to verify this.

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

Later, a temporary solution using binary was found

The code is as follows	Copy Code
SELECT * from table name · WHERE binary ' user ' = ' www.111cn.net '

The main keyword binary has it you can distinguish the case.

The code is as follows	Copy Code
CREATE table table name (name varchar () binary);

If any of the fields need to be case-sensitive in the design table, you can write this.

workaround Two If you can modify the My.ini configuration to solve the case problem as follows

A. Table names are case-sensitive

Join in my.conf:

Ower_case_table_names = 0

0: Case-sensitive, 1: case-insensitive

B. Case sensitivity when inserting a query

field values need to set the binary property to be case-sensitive.

There are several ways to set up:

Set when created:

The code is as follows	Copy Code
CREATE TABLE T ( A VARCHAR (A) BINARY );

Modify using Alter:

The code is as follows	Copy Code
ALTER TABLE ' tablename ' MODIFY COLUMN ' cloname ' VARCHAR (BINARY);

Directly tick binary in MySQL table editor