图片 2

MySQL中Identifier Case Sensitivity

1:ERROR
1010 (HY000): Error dropping database (can’t rmdir ‘./xxxx’, errno:
39)

 

遇到这种情况就比较麻烦了,必须在配置文件my.cnf中设置变量lower_case_table_names=0,重启MySQL服务,所以提前规划,使用统一的命名规则就非常重要,可以避免这样的问题出现。另外系统变量lower_case_table_names有三个值:分别是0、1、2. 

                            参数值                         解释
0 使用CREATE TABLE或CREATE DATABASE语句指定的大小写字母在硬盘上保存表名和数据库名。名称比较对大小写敏感。在大小写不敏感的操作系统如windows或Mac OS x上我们不能将该参数设为0,如果在大小写不敏感的文件系统上将–lowercase-table-names强制设为0,并且使用不同的大小写访问MyISAM表名,可能会导致索引破坏。
1 表名在硬盘上以小写保存,名称比较对大小写不敏感。MySQL将所有表名转换为小写在存储和查找表上。该行为也适合数据库名和表的别名。该值为Windows的默认值。
2 表名和数据库名在硬盘上使用CREATE TABLE或CREATE DATABASE语句指定的大小写字母进行保存,但MySQL将它们转换为小写在查找表上。名称比较对大小写不敏感,即按照大小写来保存,按照小写来比较。注释:只在对大小写不敏感的文件系统上适用! innodb表名用小写保存。

 

2
大小写区分规则

    linux下:

    数据库名与表名是严格区分大小写的;
    表的别名是严格区分大小写的;
    列名与列的别名在所有的情况下均是忽略大小写的;
    变量名也是严格区分大小写的;
    windows下:
    都不区分大小写
    Mac OS下(非UFS卷):
    都不区分大小写

 

http://blog.csdn.net/jesseyoung/article/details/40617031

2.
设置成1:表名转小写后存储到硬盘,比较时大小写不敏感。 

1 简介
   
在MySQL中,数据库对应数据目录中的目录。数据库中的每个表至少对应数据库目录中的一个文件(也可能是多个,取决于存储引擎)。因此,所使用操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性。
   
在大多数Unix中数据库名和表名对大小写敏感,而在Windows中对大小写不敏感。一个显著的例外情况是Mac
OS X,它基于Unix但使用默认文件系统类型(HFS+),对大小写不敏感。然而,Mac
OS X也支持UFS卷,该卷对大小写敏感,就像Unix一样。
   
变量lower_case_file_system说明是否数据目录所在的文件系统对文件名的大小写敏感。ON说明对文件名的大小写不敏感,OFF表示敏感。
    例如在windows下查看:
图片 1

 

4
由大小写敏感转换为不敏感方法

   
如果原来所建立库及表都是对大小写敏感的,想要转换为对大小写不敏感,主要需要进行如下3步:
    1.将数据库数据通过mysqldump导出。
    2.在my.cnf中更改lower_case_tables_name = 1,并重启mysql数据库。
    3.将导出的数据导入mysql数据库。

下面在测试环境为Red
Hat Enterprise Linux Server release 5.7, MySQL 5.6.20:

5
注意事项

   
为了避免大小写引发的问题,一种推荐的命名规则是:在定义数据库、表、列的时候全部采用小写字母加下划线的方式,不使用任何大写字母
   
在任何系统中可以使用lower_case_tables_name=1。使用该选项的不利之处是当使用SHOW
TABLES或SHOW DATABASES时,看不出名字原来是用大写还是小写。
    请注意在Unix中如果以前lower_case_tables_name =
0将lower_case_tables_name设置为1之前,重启mysqld之前,必须先将旧的数据库名和表名转换为小写。

 
 
数据库名与表名是严格区分大小写的;

 

 

    说明windows系统对大小写不敏感,mysql也默认设置为对大小写不敏感。

 
 
都不区分大小写

3
参数说明(lower_case_table_names)

    unix下lower_case_table_names默认值为 0 .Windows下默认值是 1 .Mac
OS X下默认值是 2 .

 

****************************************************************************************
   
原文地址:http://blog.csdn.net/jesseyoung/article/details/40617031
    博客主页:http://blog.csdn.net/jesseyoung
****************************************************************************************


MySQL 中,
数据库对应于数据目录中的目录。数据库中的每个表对应于数据库目录中至少一个文件
(可能更多, 具体取决于存储引擎)。触发器也对应于文件。因此,
底层操作系统的区分大小写在数据库、表和触发器名称的大小写敏感度方面起着重要作用。这意味着这些名称在
Windows 中不区分大小写, 但在大多数类型的 Unix
中都是区分大小写的。一个显著的例外是 macOS, 它是基于 Unix 的,
但使用的是不区分大小写的默认文件系统类型 (HFS+)。但是, macOS 还支持 UFS
卷, 它们与任何 Unix 一样都是区分大小写的。参见1.8.1 节, “MySQL
Extensions to Standard SQL”。lower_case_table_names
系统变量还影响服务器处理标识符大小写灵敏度的方式,
如本节后面所述。

 

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

 

注意事项:列名、索引、存储过程、事件名称在任何平台上都不区分大小写,列别名也不区分大小写。

 

 

 

 
 
都不区分大小写

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.

解决方法:在配置文件my.cnf中设置变量lower_case_table_names=0,重启MySQL服务,然后就可以drop
掉数据库了。

1.
设置成0:表名按你写的SQL大小写存储,大写就大写小写就小写,比较时大小写敏感。 

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

 

 

 

 
 
变量名也是严格区分大小写的;

可以看到此时不管是test、TEST抑或Test,都是访问的test,此时不能访问”TEST”表了,系统变量lower_case_table_names是只读变量,也无法在当前会话修改,这种设置下,如果存在相同的表名的话,使用mysqldump备份数据库时会遇到下面错误:

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> 

 

 
 
列名与列的别名在所有的情况下均是忽略大小写的;

 

2:
ERROR 1049 (42000): Unknown database ‘xxx’

 

 

在MySQL当中,有可能遇到表名大小写敏感的问题。其实这个跟平台(操作系统)有关,也跟系统变量lower_case_table_names有关系。下面总结一下,有兴趣可以查看官方文档“Identifier
Case Sensitivity”

在配置文件my.cnf中设置lower_case_table_names=1后(1表示不区分大小写,0表示区分大小写),重启MySQL服务后,进行如下测试:

图片 2

3.
设置成2:表名按你写的SQL大小写存储,大写就大写小写就小写,比较时统一转小写比较。

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> 

 
Windows系统

 

 

 

 Linux系统:

 

mysqldump:
Got error: 1066: Not unique table/alias: ‘test’ when using LOCK
TABLES

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> 

 

 

 

 

 
 
表的别名是严格区分大小写的;

 

 

 

Notice:Column,
index, stored routine, and event names are not case sensitive on any
platform, nor are column aliases.

https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html

 
 
Mac OS下(非UFS卷):

 

 

参考资料: