Windows金沙网址 中不区分大小写

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

 

金沙网址 1

 

能够观察那儿无论是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> 

 

参照他事他说加以调查资料:

 

相关文章