关于mysql大小写敏感

想必大家肯定遇到过因为大小写敏感所造成的找不到表或字段的困扰，比如以下场景：

• 执行 create table Tb01 但是 show tables; 看到表名被创建为 tb01
• 执行 create table Tb02 但是代码报错 Table tb02 doesn't exist 或 unknown column
• 利用 mysqldump 或 show create table 所生成的脚本，在目标示例执行报错提示表已存在

下面详细介绍一下 MysSQL 关于大小写敏感的相关设置及相关规范。

[TOC]

如何确认

• 登录mysql，执行以下任一语句查看是否启用大小写敏感，若返回1则表示未启用，返回0则表示已启用
• 下面以默认情况下未启用大小写敏感（lower_case_table_names=1）为例：

mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+

mysql> select @@lower_case_table_names;
+--------------------------+
| @@lower_case_table_names |
+--------------------------+
|                        1 |
+--------------------------+

如何启用

mysql 5.x 如何启用大小写

• 第1步，修改 my.cnf 设置 lower_case_table_names=0 则启用大小写敏感（改为1则不区分大小写）

[mysqld]
lower_case_table_names=0

• 第2步，重启mysql服务生效

ps -ef | grep mysql | grep -v grep | awk '{print $2}' | xargs kill -9
mysqld_safe --defaults-file=/etc/my.cnf &

mysql 8.x 如何启用大小写

• 通常，mysql 8 若要启用大小写，需要在初始化的时候指定 --lower-case-table-names=0，而在数据目录初始化之后，不再允许更改

./bin/mysqld --no-defaults --initialize --user=mysql --lower-case-table-names=0 --basedir=/opt/mysql/ --datadir=/data/mysql_3306/data

• 在 mysql 8 已有数据的情况下，若要启用大小写，可以尝试：执行实例备份 -> 重新初始化并指定 --lower-case-table-names=0 -> 导入实例备份

mysql对大小写的支持情况

库表字段的支持情况

• 未启用场景：分别执行建库、建表测试

mysql> create database testdb;
Query OK, 1 row affected (1.00 sec)

mysql> create database TESTDB;
ERROR 1007 (HY000): Can't create database 'testdb'; database exists

mysql> create table testdb.tb01(id int);
Query OK, 0 rows affected (0.13 sec)

mysql> create table testdb.Tb01(id int); 
ERROR 1050 (42S01): Table 'tb01' already exists

mysql> create table testdb.tb02(id int, ID int);
ERROR 1060 (42S21): Duplicate column name 'ID'

• 已启用场景：分别执行建库、建表测试

mysql> create database testdb;
Query OK, 1 row affected (1.00 sec)

mysql> create database TESTDB;
Query OK, 1 row affected (0.00 sec)

mysql> create table testdb.tb01(id int);
Query OK, 0 rows affected (0.13 sec)

mysql> create table testdb.Tb01(id int); 
Query OK, 0 rows affected (0.03 sec)

mysql> create table testdb.tb02(id int, ID int);
ERROR 1060 (42S21): Duplicate column name 'ID'

• 基于以上测试，汇总如下：

是否启用大小写敏感	库名	表名	字段名
未启用的mysql实例	不支持大小写	不支持大小写	不支持大小写
已启用的mysql实例	支持大小写	支持大小写	不支持大小写

字段值的支持情况

• 字段值的大小写由mysql的排序规则来控制
• 无论MySQL是否启用大小写，在不指定排序规则的情况下，where筛序均不区分字符型的大小写

#.已启用大小写敏感的mysql示例
mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+

mysql> create table tb03(id int, name varchar(10));
mysql> insert into tb03 values(1,'user'),(2,'USER'),(3,'User');

mysql> select * from tb03 where name='user';
+------+------+
| id   | name |
+------+------+
|    1 | user |
|    2 | USER |
|    3 | User |
+------+------+

• 排序规则通常以字符集开头，以ci（大小写不敏感）、cs（大小写敏感）或bin（二元，也区分大小写）结尾，比如utf8字符集的默认排序规则 utf8_general_ci 则不区分大小写

mysql 8.0之前，默认字符集为 latin1 ，而 latin1 默认的排序规则为  latin1_swedish_ci
mysql 8.0之后，默认字符集为 utf8mb4，而 utf8mb4 默认的排序规则为 utf8mb4_general_ci

• 如果建库或建表的时候没有指定排序规则对字符大小写敏感，但是查询又需要对字符比较大小写敏感，则可以通过指定 collate 来实现

#.未启用大小写敏感的mysql示例
mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+

mysql> create table tb03(id int, name varchar(10));
mysql> create table tb04(id int, name varchar(10)) collate utf8_bin;

mysql> insert into tb03 values (1,'user'),(2,'USER'),(3,'User');
mysql> insert into tb04 values (1,'user'),(2,'USER'),(3,'User');

mysql> select * from tb03 where name='user';
+------+------+
| id   | name |
+------+------+
|    1 | user |
|    2 | USER |
|    3 | User |
+------+------+

mysql> select * from tb04 where name='user';
+------+------+
| id   | name |
+------+------+
|    1 | user |
+------+------+

mysql> select * from tb03 where name='user' collate utf8_bin;
+------+------+
| id   | name |
+------+------+
|    1 | user |
+------+------+

双引号和单引号在字符串上的区别

• MySQL 中单引号和双引号引起来的字符串都是 string，并无区别，但反引号``包裹的则表示字段或关键词

mysql> create table tb05(id int, name varchar(10));
mysql> insert into tb05 values (1,'user'),(2,'USER'),(3,'User');  

mysql> select 'name',"name",`name` from tb05;
+------+------+------+
| name | name | name |
+------+------+------+
| name | name | user |
| name | name | USER |
| name | name | User |
+------+------+------+

如何避免

• 所有的库名、表名、字段名统一使用小写，采用下划线_分割，禁止以中划线-或小数点.分割
• 如果需要对字段值做筛选，务必指定字符集和排序规则