MySQL大小写敏感

MySQL大小写敏感问题

本文档适用于Linux平台（红帽6，MySQL5.7.24）,windows和mac不建议参考

MySQL数据库在Linux平台默认是区分大小写，这和windows不一致。大小写敏感包括库名、表名、字段名、别名、字段内容、变量等。在Linux服务上，MySQL提供了配置参数 lower_case_table_names 来控制打开大小写敏感的开关。同时，大小写敏感还和排序规则有关。

结论：

1、lower_case_table_names 参数控制库名和表名的大小写敏感，值为0是代表大小写敏感，值为1代表大小写不敏感。linux平台默认为0（大小写敏感）

2、排序规则控制字段内容大小写敏感，utf8_general_ci 大小写不敏感 ，utf8_bin大小写敏感

3、字段名和字段别名任何情况下都不敏感

4、用户变量不区分大小写

5、反引号对大小写不影响，反引号只是影响关键字作为表名、字段名。和其他数据库用的双引号不一样

大小写是否敏感	库名	表名	表别名	字段名	字段别名	字段内容	变量
lower_case_table_name = 0  utf8_general_ci	是	是	是	否	否	否	否
lower_case_table_name = 1  utf8_general_ci	否	否	否	否	否	否	否
lower_case_table_name = 0  utf8_bin	是	是	是	否	否	是	否
lower_case_table_name = 1  utf8_bin	否	否	否	否	否	是	否

验证步骤：

配置参数 lower_case_table_names = 0，排序规则默认 utf8_general_ci。

#库名区分大小写mysql> create database db;Query OK, 1 row affected (0.35 sec)mysql> create database DB;Query OK, 1 row affected (0.00 sec)#表名区分大小写mysql> create table t1(a int,b varchar(5));Query OK, 0 rows affected (0.02 sec)mysql> create table T1(a int,b varchar(5));Query OK, 0 rows affected (0.03 sec)# 表的别名区分大小写mysql> select * from test t where t.a = 1;+------+| a|+------+|1 |+------+1 row in set (0.12 sec)mysql> select * from test t where T.a = 1;ERROR 1054 (42S22): Unknown column 'T.a' in 'where clause'# 自定义变量不区分大小写mysql> set @age = 18;Query OK, 0 rows affected (0.00 sec)mysql> set @AGE = 19;Query OK, 0 rows affected (0.00 sec)mysql> select @age, @AGE;+------+------+| @age | @AGE |+------+------+|19 |19 |+------+------+1 row in set (0.00 sec)# 字段名不区分大小写mysql> create table test(a int,B int,A int);ERROR 1060 (42S21): Duplicate column name 'A'mysql> create table test(a int,B int);Query OK, 0 rows affected (0.14 sec)#数据内容不区分大小写mysql> create table test(a int,b varchar(5));Query OK, 0 rows affected (0.14 sec)mysql> insert into test values(1,'aa'),(2,'AA'),(3,'Aa');Query OK, 3 rows affected (0.01 sec)Records: 3  Duplicates: 0  Waings: 0mysql> select * from test where b = 'aa';+------+------+| a| b|+------+------+|1 | aa||2 | AA||3 | Aa|+------+------+3 rows in set (0.00 sec)

配置参数 lower_case_table_names = 1，排序规则默认 utf8_general_ci

#库名大小写不敏感mysql> create database db;Query OK, 1 row affected (1.75 sec)mysql> create database DB;ERROR 1007 (HY000): Can’t create database 'db'; database exists# 表名大小写不敏感mysql> create table t(a int);Query OK, 0 rows affected (0.12 sec)mysql> create table T(a int);ERROR 1050 (42S01): Table 't' already exists# 自定义变量不区分大小写mysql> set @age = 18;Query OK, 0 rows affected (0.00 sec)mysql> set @AGE = 19;Query OK, 0 rows affected (0.00 sec)mysql> select @age, @AGE;+------+------+| @age | @AGE |+------+------+|19 |19 |+------+------+1 row in set (0.00 sec)mysql> select * from test t where T.a = 1;+------+| a|+------+|1 |+------+1 row in set (0.00 sec)#数据内容不区分大小写mysql> create table test(a int,b varchar(5));Query OK, 0 rows affected (0.14 sec)mysql> insert into test values(1,'aa'),(2,'AA'),(3,'Aa');Query OK, 3 rows affected (0.01 sec)Records: 3  Duplicates: 0  Waings: 0mysql> select * from test where b = 'aa';+------+------+| a| b|+------+------+|1 | aa||2 | AA||3 | Aa|+------+------+3 rows in set (0.00 sec)

从上变例子可以看出 lower_case_table_names 配置参数，用来控制库名、表名、表别名大小写敏感，值为0时区分大小写，值为1时不区分大小写。

有时会遇到这样一种情况，执行条件检索时，返回的数据和期忘的数据不一致。例如，查询 name = 'a'时，name = 'A' 的数据也一并返回了。这时就需要设置字段内容的大小写敏感。

当使用utf8字符集时，默认的排序规则为uft8_general_ci，大小写时不敏感的。如果设置大小写敏感，提供以下几种解决方案：

1、创建表时，字段标记为binary。二进制大小写是敏感的，不建议使用，会使索引字段失败。

mysql> create table test(a int,b varchar(5) binary);Query OK, 0 rows affected (0.18 sec)mysql> insert into test values(1,'AA'),(2,'aa'),(3,'Aa');Query OK, 3 rows affected (0.42 sec)Records: 3  Duplicates: 0  Waings: 0mysql> select * from test where b = 'aa';+------+------+| a| b|+------+------+|2 | aa|+------+------+1 row in set (0.00 sec)

2、建表时，指定排序规则为utf8_bin

mysql> create table test(a int,b varchar(5)) character set utf8 collate utf8_bin;Query OK, 0 rows affected (1.38 sec)mysql> insert into test values(1,'AA'),(2,'aa'),(3,'Aa');Query OK, 3 rows affected (0.16 sec)Records: 3  Duplicates: 0  Waings: 0mysql> select * from test where b = 'aa';+------+------+| a| b|+------+------+|2 | aa|+------+------+1 row in set (0.21 sec)

3、建表时，指定字段排序规则为utf8_bin

mysql> create table test(a int,b varchar(5) collate utf8_bin, c varchar(5));Query OK, 0 rows affected (0.17 sec)mysql> insert into test values(1,'b','c'),(2,'B','c'),(3,'b','C');Query OK, 3 rows affected (0.14 sec)Records: 3  Duplicates: 0  Waings: 0mysql> select * from test where b = 'b';+------+------+------+| a| b| c|+------+------+------+|1 | b| c||3 | b| C|+------+------+------+2 rows in set (0.13 sec)mysql> select * from test where c = 'C';+------+------+------+| a| b| c|+------+------+------+|1 | b| c||2 | B| c||3 | b| C|+------+------+------+3 rows in set (0.00 sec)

4、也可以修改字段的排序规则

alter table test3 modify name varchar(20) collate utf8_bin;

以上所有结论均经过实验得到，如有错误，欢迎指正。

注意事项（后补充）：

在修改大小写敏感参数之前，需要注意以下几点：

1.核实实例中是否存在大写的库及表
2.将大写的库名及表名改为小写
更改库名可参考：https://www.cnblogs.com/gomysql/p/3584881.html
更改表名：rename table TEST_TB to test_tb;
3.设置lower_case_table_names = 1
4.重启数据库

在实际运维中曾遇到因修改lower_case_table_names之前没有修改表名，导致原存的大写表名无法查询，且在drop库时，无法正常drop引起主从连接中断等一系列问题