By default, fields in the MySQL database (char, varchar) are retrieved (where) or sorted (order) in case-insensitive.
In many application programming, there is a case-sensitive requirement, which can be achieved by defining the binary attribute beforehand (recommended in principle) by building a table in MySQL.
For example:
CREATE TABLE Binary_column
COLUM01 varchar (primary) binary key,
Colum02 char (a) binary
);
CREATE TABLE Binary_column
COLUM01 varchar (primary) binary key,
Colum02 char (a) binary
);
For tables that already exist, you need to modify the table definition with the ALTER command
Altertable binary_column Modify Colum02 char (n) binary;
However, when you modify a field that is defined as a primary key, you need to first cancel the primary key definition and then modify it to be a binary attribute, the primary key attribute;
ALTER TABLE binary_column drop PRIMARY key;
ALTER TABLE Binary_column modify COLUM01 varchar () binary primary key, key;
Alternatively, you can use the binary command to force case-sensitive when issuing query statements (but programmers tend to forget the omission)
SELECT * from binary_column where colum02 = binary ' AbCdE ';
SELECT * from binary_column where binary colum02 = ' AbCdE ';
There are several sets of data tested, the execution speed of the two (explain), the former to fast, the reason is still unknown, which students know, please also inform. Is it because the former executes a binary, which needs to be executed several times?
Transfer from http://blog.sina.com.cn/s/blog_7f2122c501011ezh.html
"MySQL" in MySQL characters, string case problems