Mysql SQL statement Summary

Mysql only needs to write name varchar (20) binary when the field is created, and then write binary 'user' = 'www. bKjia. c0m' can be solved, mainly using binary.

The company website needs to verify the case sensitivity of the user name. In fact, at the beginning of his registration, it is case-sensitive, so there is no need to perform case-sensitive verification. But now the login authentication is case-sensitive, so it should also be verified during registration.

However, MySQL is case-insensitive when querying strings. In programming MySQL, The ISO-8859 character set is generally used as the default character set. This character set is case-insensitive, therefore, the case-sensitivity conversion of Chinese encoding Characters During the comparison process causes this phenomenon.

Then I found a temporary solution using binary.

The Code is as follows:	Copy code
SELECT * FROM · table name · WHERE binary 'user' = 'www. bKjia. c0m'

The key keyword binary can be case sensitive.

The Code is as follows:	Copy code
Create table name (name varchar (20) binary );

If the fields need to be case sensitive in the design table, you can write them like this.

Solution 2If you can modify the my. ini configuration, the Case sensitivity problem can be solved as follows:

A. the table name is case sensitive.

Add in my. conf:

Ower_case_table_names = 0

0: Case Sensitive, 1: case insensitive
 

B. Insert the query Time Zone in Case sensitivity

 
The field value must be set to the BINARY Attribute to be case sensitive.

You can set multiple methods:

Set at creation:

The Code is as follows:	Copy code
Create table t ( A varchar (10) BINARY );

Use alter to modify:

The Code is as follows:	Copy code
Alter table 'tablename' modify column 'cloname' VARCHAR (45) BINARY;

Select BINARY in mysql table editor.