深入浅析MySQL 中 Identifier Case Sensitivity问题

在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大小写存储,大写就大写小写就小写,比较时统一转小写比较。

 

关于数据库名大小写敏感,会遇到下面问题:

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

总结

以上所述是小编给大家介绍的MySQL 中 Identifier Case Sensitivity问题,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对我们网站的支持!

(0)

相关推荐

  • mysql中IFNULL,IF,CASE的区别介绍

    假设有一数据表的状态字段设计为varchar类型,有以下值:NULL,pending,pending refund,refund,cancel. 我们知道查询状态为cancel的订单,SQL语句可以这样写:SELECT o.oid,o.moneyreceipt,o.moneyget,o.thecurrency,o.status FROM qorder o WHERE o.status = 'cancel' SQL语句能查询出正确的数据,但是当我们想查询状态为非cancel的订单时,可能会出麻烦,

  • MYSQL中有关SUM字段按条件统计使用IF函数(case)问题

    今天群里有人问了个问题是这样的:  然后有群友是这样回答的 复制代码 代码如下: select name,sum(case when stype=4 then money*(-1) else money end ) as M from table group by name 我想了想,应该可以用IF函数 于是改了下 复制代码 代码如下: select name,sum(money*IF(stype=4,-1,1)) as M from table group by name 两种方式那种更效率还

  • MySQL的CASE WHEN语句的几个使用实例

    使用CASE WHEN进行字符串替换处理 复制代码 代码如下: mysql> select * from sales; 9 rows in set (0.01 sec) SELECT name AS Name,    CASE category    WHEN "Holiday" THEN "Seasonal"    WHEN "Profession" THEN "Bi_annual"    WHEN "Lit

  • MySQL中的if和case语句使用总结

    Mysql的if既可以作为表达式用,也可在存储过程中作为流程控制语句使用,如下是做为表达式使用: IF表达式 复制代码 代码如下: IF(expr1,expr2,expr3) 如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL),则 IF()的返回值为expr2; 否则返回值则为 expr3.IF() 的返回值为数字值或字符串值,具体情况视其所在语境而定. 复制代码 代码如下: select *,if(sva=1,"男",&

  • MySQL 的CASE WHEN 语句使用说明

    mysql数据库中CASE WHEN语句. case when语句,用于计算条件列表并返回多个可能结果表达式之一. CASE 具有两种格式: 简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果. CASE 搜索函数计算一组布尔表达式以确定结果. 两种格式都支持可选的 ELSE 参数. 语法简单 CASE 函数: 复制代码 代码如下: CASE input_expression    WHEN when_expression THEN result_expression      

  • MySQL中使用case when 语句实现多条件查询的方法

    举例如下: 数据表为DemoTable,字段有id, condition1,condition2,condition3,condition4,condition5 要求是查询DemoTable中,condition1,condition2,condition3,condition4,condition5五个字段中符合任意两个或两个以上的条件的内容. 可使用case when来实现这个条件,需要嵌套子查询语句 sql语句代码示例如下: 复制代码 代码如下: SELECT * FROM DemoTa

  • 详解Mysql case then使用

    表的创建 CREATE TABLE `lee` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` char(20) DEFAULT NULL, `birthday` datetime DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 数据插入: insert into lee(name,birthday) values ('sam','1990-01-01'); i

  • 深入浅析MySQL 中 Identifier Case Sensitivity问题

    在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 leas

  • MySQL中存储过程的详细详解

    目录 概述 优点 缺点 MySQL存储过程的定义 存储过程的基本语句格式 存储过程的使用 定义一个存储过程 定义一个有参数的存储过程 定义一个流程控制语句 IF ELSE 定义一个条件控制语句 CASE 定义一个循环语句 WHILE 定义一个循环语句 REPEAT UNTLL 定义一个循环语句 LOOP 使用存储过程插入信息 存储过程的管理 显示存储过程 显示特定数据库的存储过程 显示特定模式的存储过程 显示存储过程的源码 删除存储过程 后端调用存储过程的实现 总结 概述 由MySQL5.0 版

  • MySQL排序中使用CASE WHEN的方法示例

    前言 在之前的一个项目中,使用到了SQL中使用 CASE WHEN 排序的功能.现在写篇博客备忘~ 数据库版本:MySQL 5.6.42 条件: 某字段代表该数据的状态取值为非负整数,0表示无状态. 需求: 以该字段升序排序,同时需要将值为0的数据放在最后. 首先我们看一下,表的结构: 正常的使用升序查询结果是这样的: 要让"小赤"排在最后,可以这样写: 也可以这样: 还可以使用 CASE WHEN 再不影响查询结果的前提下,改变排序的依据: 总结 以上就是这篇文章的全部内容了,希望本

  • MySQL中case when对NULL值判断的踩坑记录

    目录 前言 Mysql中case when语法: 案例实战: 总结: 前言 今天在开发程序中,从MySQL中提取数据的时候,使用到了case when的语法用来做判断,在使用过程中在判断NULL值的时候遇到个小问题 sql中的case when 有点类似于Java中的switch语句,比较灵活,但是在Mysql中对于Null的处理有点特殊 Mysql中case when语法: 语法1: CASE case_value WHEN when_value THEN statement_list [WH

  • Mysql中undo、redo与binlog的区别浅析

    目录 前言 [undo log] [redo log] [binlog] 总结 前言 MySQL中有六种日志文件,分别是:重做日志(redo log).回滚日志(undo log).二进制日志(binlog).错误日志(errorlog).慢查询日志(slow query log).一般查询日志(general log),中继日志(relay log). 其中重做日志和回滚日志与事务操作息息相关,二进制日志也与事务操作有一定的关系,这三种日志,对理解MySQL中的事务操作有着重要的意义.   与

  • MySQL中distinct和group by去重效率区别浅析

    目录 一.distinct 二.group by 三.distinct 和 group by 比较 总结 一.distinct distinct的作用 在mysql中,distinct关键字的主要作用就是对数据库表中一个或者多个字段重复的数据进行过滤,只返回其中的一条数据给用户,distinct只可以在select中使用 distinct的原理 distinct进行去重的主要原理是通过先对要进行去重的数据进行分组操作,然后从分组后的每组数据中去一条返回给客户端,在这个分组的过程可能会出现两种不同

  • mysql中的保留字段产生的问题

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server versio····这句话对于咱们并不陌生,无非就是多了","之类的问题.但是你如果无意之中添加了一个mysql中的保留字段作为数据库中存贮的字段名,悲剧就这样发生了. 前几天因为功能的临时变更,需要给数据表添加一个字段用来区别导入的批次,我就在mysql中添加了group字段,没想到我的噩梦就此展开·

  • 浅析mysql交互式连接&非交互式连接

    交互式操作:通俗的说,就是你在你的本机上打开mysql的客户端,就是那个黑窗口,在黑窗口下进行各种sql操作,当然走的肯定是tcp协议. 非交互式操作:就是你在你的项目中进行程序调用.比如一边是tomcat web服务器,一边是数据库服务器,两者怎么通信?在java web里,我们通常会选择hibernate或者是jdbc来连接.那么这时候就是非交互式操作. 在之前,我基本上不关系这两个属性,都是用的是mysql服务商推荐的默认值,就是8小时. 但是,从昨天开始,由于在新网租用了一个空间,而他的

随机推荐