MySQL string case-insensitive default behavior

Today I encountered a file with a primary key conflict. I think the primary keys of the two records are obviously different. One is JAA in upper case and the other is JAA in lower case, mo Gray is that MySQL is not case sensitive to field values. I checked it online (by default) and found an article: From: Example;
2. log on to the/etc/My. add lower_case_table_names = 1 after [mysqld] In CNF, and restart the MySQL service. The settings are successful: Case Insensitive to table names; Detailed description of lower_case_table_names parameters:
Lower_case_table_names = 0
0: case-sensitive; 1: case-insensitive MySql in Linux database names, table names, column names, alias case-sensitive rules are as follows:
1. The database name and table name are case sensitive;
2. Table aliases are case sensitive;
3. The column name and column alias are case-insensitive in all cases;
4. Variable names are also case sensitive. MySQL is case insensitive in windows. 3. If you want to query the case sensitivity of the time zone field value, you need to set the Binary Attribute for the field value. You can set multiple methods:
A. set at creation:
Create Table T (
A varchar (10) binary
); B. Use alter to modify:
Alter table 'tablename' Modify column 'cloname' varchar (45) binary; C. Directly Select Binary in MySQL table editor. From: http://zhangzuanqian.javaeye.com/blog/210456mysql
Recently, it is reported that if you have registered Aaron. A lei cannot be registered! This is a MySQL problem! Many forums that use MySQL have this problem. It is not just a question of these two words. The cause of this problem is: 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 not case sensitive, therefore, the case-sensitivity conversion of Chinese encoding Characters During the comparison process causes this phenomenon. 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 example shows how to 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 by default, MySQL search is case insensitive (but some character sets are always case sensitive, such as Czech ). This means that if you use col_name like 'a % 'for search, you will get all columns starting with a or. If you want to make the search area case sensitive, make sure that one of the operands is case sensitive or binary. For example, if you are comparing columns and strings of the Latin1 character set, you can use the Collate operator to enable one operand to be proofread by latin1_general_cs or latin1_bin. For example:
Col_name collate latin1_general_cs like 'a %'
Col_name like 'a % 'collate latin1_general_cs
Col_name collate latin1_bin like 'a %'
Col_name like 'a % 'collate latin1_bin if you want to always process columns in case sensitive mode, you can use case sensitive or binary checking to declare it. Use the binary flag when creating a table.
Create Table T (
A varchar (10) binary
); If your server uses the win system, Method 5 is the best method! 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!