MySQL中Identifier Case Sensitivity
2018-08-15 22:51 潇湘隐者 阅读(1751) 评论(1) 编辑 收藏 举报在MySQL当中,有可能遇到表名大小写敏感的问题。其实这个跟平台(操作系统)有关,也跟系统变量lower_case_table_names有关系。下面总结一下,有兴趣可以查看官方文档“Identifier Case Sensitivity”
In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Triggers also correspond to files. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names. This means such names are not case-sensitive in Windows, but are case-sensitive in most varieties of Unix. One notable exception is macOS, which is Unix-based but uses a default file system type (HFS+) that is not case-sensitive. However, macOS also supports UFS volumes, which are case-sensitive just as on any Unix. See Section 1.8.1, “MySQL Extensions to Standard SQL”. Thelower_case_table_names system variable also affects how the server handles identifier case sensitivity, as described later in this section.
在 MySQL 中, 数据库对应于数据目录中的目录。数据库中的每个表对应于数据库目录中至少一个文件 (可能更多, 具体取决于存储引擎)。触发器也对应于文件。因此, 底层操作系统的区分大小写在数据库、表和触发器名称的大小写敏感度方面起着重要作用。这意味着这些名称在 Windows 中不区分大小写, 但在大多数类型的 Unix 中都是区分大小写的。一个显著的例外是 macOS, 它是基于 Unix 的, 但使用的是不区分大小写的默认文件系统类型 (HFS+)。但是, macOS 还支持 UFS 卷, 它们与任何 Unix 一样都是区分大小写的。参见1.8.1 节, "MySQL Extensions to Standard SQL"。lower_case_table_names 系统变量还影响服务器处理标识符大小写灵敏度的方式, 如本节后面所述。
Linux系统:
数据库名与表名是严格区分大小写的;
表的别名是严格区分大小写的;
列名与列的别名在所有的情况下均是忽略大小写的;
变量名也是严格区分大小写的;
Windows系统:
都不区分大小写
Mac OS下(非UFS卷):
都不区分大小写
注意事项:列名、索引、存储过程、事件名称在任何平台上都不区分大小写,列别名也不区分大小写。
Notice:Column, index, stored routine, and event names are not case sensitive on any platform, nor are column aliases.
下面在测试环境为Red Hat Enterprise Linux Server release 5.7, MySQL 5.6.20:
mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 0 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql>
mysql> use mydb;
Database changed
mysql> create table test(id int);
Query OK, 0 rows affected (0.07 sec)
mysql> create table TEST(id int);
Query OK, 0 rows affected (0.09 sec)
mysql> insert into test values(1);
Query OK, 1 row affected (0.03 sec)
mysql> insert into TEST value(2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select * from TEST;
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
mysql>
在配置文件my.cnf中设置lower_case_table_names=1后(1表示不区分大小写,0表示区分大小写),重启MySQL服务后,进行如下测试:
mysql> use mydb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from test;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select * from TEST;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql>
可以看到此时不管是test、TEST抑或Test,都是访问的test,此时不能访问"TEST"表了,系统变量lower_case_table_names是只读变量,也无法在当前会话修改,这种设置下,如果存在相同的表名的话,使用mysqldump备份数据库时会遇到下面错误:
mysqldump: Got error: 1066: Not unique table/alias: 'test' when using LOCK TABLES
遇到这种情况就比较麻烦了,必须在配置文件my.cnf中设置变量lower_case_table_names=0,重启MySQL服务,所以提前规划,使用统一的命名规则就非常重要,可以避免这样的问题出现。另外系统变量lower_case_table_names有三个值:分别是0、1、2.
1. 设置成0:表名按你写的SQL大小写存储,大写就大写小写就小写,比较时大小写敏感。
2. 设置成1:表名转小写后存储到硬盘,比较时大小写不敏感。
3. 设置成2:表名按你写的SQL大小写存储,大写就大写小写就小写,比较时统一转小写比较。
Value |
Meaning |
0 |
Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASEstatement. Name comparisons are case sensitive. You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or macOS). If you force this variable to 0 with --lower-case-table-names=0 on a case-insensitive file system and access MyISAM tablenames using different lettercases, index corruption may result. |
1 |
Table names are stored in lowercase on disk and name comparisons are not case-sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases. |
2 |
Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASEstatement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case-sensitive! InnoDB table names and view names are stored in lowercase, as forlower_case_table_names=1. |
关于数据库名大小写敏感,会遇到下面问题:
1:ERROR 1010 (HY000): Error dropping database (can't rmdir './xxxx', errno: 39)
1:ERROR 1010 (HY000): Error dropping database (can't rmdir './xxxx', errno: 39)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| MyDB |
| mydb |
| mysql |
| performance_schema |
| tmonitor |
| xiangrun |
+--------------------+
7 rows in set (0.01 sec)
mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 1 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql> drop database mydb;
ERROR 1010 (HY000): Error dropping database (can't rmdir './mydb', errno: 39)
mysql>
解决方法:在配置文件my.cnf中设置变量lower_case_table_names=0,重启MySQL服务,然后就可以drop 掉数据库了。
2: ERROR 1049 (42000): Unknown database 'xxx'
mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 1 |
+------------------------+-------+
1 row in set (0.01 sec)
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| MyDB |
| mysql |
| performance_schema |
| tmonitor |
| xiangrun |
+--------------------+
6 rows in set (0.01 sec)
mysql> use MyDB;
ERROR 1049 (42000): Unknown database 'mydb'
mysql>
参考资料:
https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html

【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
2017-08-15 ORACLE等待事件:SQL*Net message from client & SQL*Net message to client
2016-08-15 RMAN命令LIST操作总结
2013-08-15 Linux系统安装Apache 2.4.6