中数据库与表名是忽略大小写的,在大多数Unix中

作者: www.9159.com  发布:2019-10-13

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

这就意味着数据库和表名在 Windows 中是大小写不敏感的,而在大多数类型的 Unix 系统中是大小写敏感的。一个特例是 Mac OS X,当缺省的 HFS+ 文件系统使用时。然而 Mac OS X 还支持 UFS 卷,那些在 Mac OS X 是大小写敏感的就如他们在任一 Unix 上一样。查看章节 1.8.3 MySQL 对 ANSI SQL92 的扩展。
注意:尽管在 Windows 中数据库与表名是忽略大小写的,你不应该在同一个查询中使用不同的大小写来引用一个给定的数据库和表。下面的查询将不能工作,因为它以 my_table 和 MY_TABLE 引用一个表:

mysql的表的大小写区分

在linux的环境下,在mysql的配置里会对大小写有一定的要求的。尤其是一些云平台提供的固定环境是配置好这些要求,所以需要对这些有一定的了解。(比如阿里云就会设定lower_case_table_names=1,具体分析在后面)

 

 

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

复制代码 代码如下:

1、表名大小写

Linux默认情况下区分大小写(另外,MySQL在Windows下数据库名、表名、列名、别名都不区分大小写,即使设置lower_case_table_names=0,在查询时还是不会区分大小写。只是在导入导出时会对大小写有区别。原因是大小写的功能是在操作系统的文件系统对大小对区别的基础上实现的。WINDOWS的文件系统查询时不区分文件大小写。)

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.

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

mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;

(1)变量含义解释

lower_case_file_system:数据库所在的文件系统对文件名大小写敏感度。
ON表示大小写不敏感 OFF表示敏感

lower_case_table_names:表名大小写敏感度
0表示使用Create语句指定的大小写保存文件
1表示大小写敏感,文件系统以小写保存
2表示使用Create语句指定的大小写保存文件,但MySQL会将之转化为小写
(当Linux设置为2时,错误日志显示[Warning]
lower_case_table_names was set to 2, even though your the file system '/home/mysql/master_a/data/' is case sensitive.
Now setting lower_case_table_names to 0 to avoid future problems.)

 

2 大小写区分规则
    linux下:

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

列名与列的别名在所有的情况下均是忽略大小写的。
表的别名是区分大小写的。下面的查询将不能工作,因为它用 a 和 A 引用别名:
mysql> SELECT col_name FROM tbl_name AS a WHERE a.col_name = 1 OR A.col_name = 2;
如果记忆数据库和表名的字母大小写有困难,建议采用一个一致一约定,例如总是以小写字母创建数据库和表。
避免这个问题的另一个办法就是以 -O lower_case_table_names=1 参数启动 mysqld。缺省地在 Windows 中这个选项为 1 ,在 Unix 中为 0。
如果 lower_case_table_names 为 1 ,MySQL 将在存储与查找时将所有的表名转换为小写字线。(从 MySQL 4.0.2 开始,这个选项同样适用于数据库名。) 注意,当你更改这个选项时,你必须在启动 mysqld 前首先将老的表名转换为小写字母。
$ pwd
/data1/etl/aiinsight/ScheduleServer/mysql/support-files
$ ./mysql.server stop
$ pwd
/data1/etl/aiinsight/ScheduleServer/mysql/bin
./mysqld_safe --lower_case_table_names
方法2:修改my.cnf配置文件
在[mysqld]节下加入
lower_case_table_names=1

(2)变量更新

mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
+------------------------+-------+
2 rows in set (0.01 sec)

--修改发现变量为只读变量
mysql> set global lower_case_table_names=1;
ERROR 1238 (HY000): Variable 'lower_case_table_names' is a read only variable

修改my.cnf后重启数据库

lower_case_table_names=1

mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 1 |
+------------------------+-------+
2 rows in set (0.01 sec)

因为修改了配置lower_case_table_names=1,原来在mysql的show tables里面有表的记录, select操作时会显示查不到表

+--------------------------------+
| Tables_in_testdb |
+--------------------------------+
| TABLE1|
| TABLE2|

但是
mysql> select * from TABLE1;
ERROR 1146 (42S02): Table 'testdb.TABLE1' doesn't exist
主要原因是大小写敏感以及文件系统以小写文件名写入,而原来的表名是大写的。

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

3 参数说明(lower_case_table_names)
    unix下lower_case_table_names默认值为 0 .Windows下默认值是 1 .Mac OS X下默认值是 2 .

Windows 中是大小写不敏感的,而在大多数类型的 Unix 系统中是大小写敏感的。一个特例是 Mac OS X,当缺省的 HFS...

2、表字段数据大小写

表字段数据的校对,bin与cs区分大小写,ci不区分大小写
MySQL对校对约定的命名方式如下:
*_ci:case insensitive collation,不区分大小写
*_cs:case sensitive collation,区分大小写
*_bin: 表示的是binary case sensitive collation,将字符串每个字符串用二进制数据编译存储。 区分大小写,而且可以存二进制的内容

 

 

(1)表字段数据区分大小写

指定校对为区分大小写

mysql> create table b(id varchar(10)) default charset=utf8 default collate=utf8_bin;
Query OK, 0 rows affected (0.03 sec)sec)
mysql> insert into b values ('A'),('a'),('B');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from b where id like 'a';
+------+
| id |
+------+
| a |
+------+
1 row in set (0.00 sec)


mysql> select id,count(*) from b group by id;
+------+----------+
| id | count(*) |
+------+----------+
| A | 1 |
| B | 1 |
| a | 1 |
+------+----------+
3 rows in set (0.03 sec)

 Linux系统:

                            参数值                         解释
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)表字段数据不区分大小写

指定校对为忽略大小写

mysql> create table t (name varchar(10)) default charset=utf8 default collate=utf8_general_ci;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t values('a'),('A'),('B'),('b'),('c');
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from t where name like 'a';
+------+
| name |
+------+
| a |
| A |
+------+
2 rows in set (0.01 sec)

mysql> select name,count(*) from t group by name;
+------+----------+
| name | count(*) |
+------+----------+
| a | 2 |
| B | 2 |
| c | 1 |
+------+----------+
3 rows in set (0.04 sec)

不过如果字段类型是binary则肯定会区分大小写

mysql> alter table t change name name varchar(10) binary;
Query OK, 5 rows affected (0.08 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from t where name like 'A';
+------+
| name |
+------+
| A |
+------+
1 row in set (0.00 sec)

mysql> select name,count(*) from t group by name;
+------+----------+
| name | count(*) |
+------+----------+
| A | 1 |
| B | 1 |
| a | 1 |
| b | 1 |
| c | 1 |
+------+----------+
5 rows in set (0.01 sec)

 

在linux的环境下,在mysql的配置里会对大小写有一定的要求的。尤其是一些云平台提供的固定环境是配置好这些要求,...

 

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

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

 

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

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

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

****************************************************************************************
    原文地址:
    博客主页:
****************************************************************************************

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

 

  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> 

 

www.9159.com 2

 

可以看到此时不管是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> 

 

参考资料:

 

本文由9159.com发布于www.9159.com,转载请注明出处:中数据库与表名是忽略大小写的,在大多数Unix中

关键词:

上一篇:InnoDB锁冲突案例演示
下一篇:没有了