MySQL Case sensitive description

Frequently encountered problems, some are not particularly important but more depressed things. For example today this MySQL is case sensitive. First, the test results.

Linux environment, not under Windows platform. The difference is very big. Attention.

A picture wins thousand words  mysql> show create table ac;+-------+--------------------------------------------------------------- ----------------------------------------------------------+| Table | Create Table                               &NBS P                          ,         &NB Sp                          ,         &NB Sp    |+-------+---------------------------------------------------------------------------------------- ---------------------------------+| Ac    | CREATE TABLE ' Ac ' (  ' a ' varchar () default null,  ' C ' varchar () default NULL) Engine=innodb default charset= UTF8 |+-------+------------------------------------------------------------------------------------------------- ------------------------+1 row in set (0.XX sec)  mysql>mysql> insert into Ac  values (' 1q ', ' 1q '); Query OK, 1 row Affected (0.00 sec)  mysql> INSERT into Ac  values (' 1Q ', ' 1Q '); Query OK, 1 row Affected (0.00 sec)  mysql> Select * from Ac WHERE a= ' 1q '; +------+------+| A    | C    |+------+------+| 1q   | 1q   | | 1Q   | 1Q   |+------+------+2 rows in Set (0.00 sec)  mysql> select * from AC; ERROR 1146 (42S02): Table ' Test. AC ' doesn ' t existmysql> select * from Ac  where a= ' 1Q '; +------+------+| A    | C    |+------+------+| 1q   | 1q   | | 1Q   | 1Q   |+------+------+2 rows in Set (0.00 sec)   The results above can reflect the following conclusions.  mysql in Linux database name, table name, column name, alias casing rules are as follows: 1, the database name and table name is strictly case-sensitive, 2, the table alias is strictly case-sensitive; 　　3, the column name and column alias in all cases are ignored case, 4, the field content is case insensitive by default. The case sensitivity of controlling database names and table names in MySQL is controlled by the parameter lower_case_table_names, which is 0, which is case-sensitive, and is 1 o'clock, which means that the name is converted to lowercase and stored, not case-sensitive. Mysql> Show variables like '%case% '; +------------------------+-------+| variable_name | Value |+------------------------+-------+| Lower_case_file_system | OFF | | Lower_case_table_names | 0 |+------------------------+-------+2 rows in Set (0.00 sec) to modify the CNF configuration file or compile, the service needs to be restarted. The fields stored by MySQL are case insensitive. It's kind of weird. Especially when the user registers the business time, will appear the joke. So it's hard to strictly limit case sensitivity, such as good. How to Prevent field content from being case sensitive. is to add a validation rule for the field. you can see that the field contents are case insensitive by default. Casing is not sensitive.  mysql> CREATE TABLE AA (a varchar (20) BINARY, c varchar (20)); Query OK, 0 rows affected (0.10 sec)  mysql> Show create table aa;+-------+-------------------------------------- --------------------------------------------------------------------------------------------------------------- --------+| Table | Create Table                               &NBS P                          ,         &NB Sp                          ,         &NB Sp                          ,         &NB Sp    |+-------+---------------------------------------------------------------------------------------- ---------------------------------------------------------------------+| AA    | CREATE TABLE ' AA ' (  ' a' varchar (20)  character SET UTF8 COLLATE utf8_bin DEFAULT NULL, ' C ' varchar (default NULL) engine=innodb default Charset=utf8 |+-------+------------------------------------------ --------------------------------------------------------------------------------------------------------------- ----+1 row in Set (0.00 sec) mysql> SELECT * FROM aa;+------+------+| A | C |+------+------+| A | C | | A | C | | A | C |+------+------+3 rows in Set (0.00 sec) mysql> select * from AA where a = ' a '; +------+------+| A | C |+------+------+| A | C | | A | C |+------+------+2 rows in Set (0.00 sec) mysql> select * from AA where a = ' a '; +------+------+| A | C |+------+------+| A | C |+------+------+1 row in Set (0.00 sec) for the following reasons: The case of the field value is written by the MySQL's proofing rules to control。 When it comes to proofing rules, you have to say character sets. A character set is a set of symbols and encodings that are used to compare characters within a set of rules. In general, the collation rule begins with its associated character set name, usually including a language name, and ends with _ci (case insensitive), _cs (case sensitive), or _bin (two). For example, the UTF8 character set, utf8_general_ci, is not case-sensitive, this is the default collation of the UTF8 character set, Utf8_general_cs is case-sensitive, Utf8_bin represents a binary comparison, and is also case-sensitive. Keep it down!

MySQL Case sensitive description