深入浅析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下:都不区分大小写

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

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 changedmysql 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后,重启MySQL服务后,进行如下测试:

mysql use mydb;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql 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大小写存储,大写就大写小写就小写,比较时大小写敏感。

  1. 设置成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

参考资料:

总结

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

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图