MySQL库表名大小写的选择(大小写区分)

1.决定大小写是否敏感的参数

在 MySQL 中，数据库与 data 目录中的目录相对应。数据库中的每个表都对应于数据库目录中的至少一个文件（可能是多个文件，具体取决于存储引擎）。因此，操作系统的大小写是否敏感决定了数据库大小写是否敏感，而 Windows 系统是对大小写不敏感的，Linux 系统对大小写敏感。

默认情况下，库表名在 Windows 系统下是不区分大小写的，而在 Linux 系统下是区分大小写的。列名，索引名，存储过程、函数及事件名称在任何操作系统下都不区分大小写，列别名也不区分大小写。

除此之外，MySQL 还提供了 lower_case_table_names 系统变量，该参数会影响表和数据库名称在磁盘上的存储方式以及在 MySQL 中的使用方式，在 Linux 系统，该参数默认为 0 ，在 Windows 系统，默认值为 1 ，在 macOS 系统，默认值为 2 。下面再来看下各个值的具体含义：

Value	Meaning
0	库表名以创建语句中指定的字母大小写存储在磁盘上，名称比较区分大小写。
1	库表名以小写形式存储在磁盘上，名称比较不区分大小写。MySQL 在存储和查找时将所有表名转换为小写。此行为也适用于数据库名称和表别名。
2	库表名以创建语句中指定的字母大小写存储在磁盘上，但是 MySQL 在查找时将它们转换为小写。名称比较不区分大小写。

一般很少将 lower_case_table_names 参数设置为 2 ，下面仅讨论设为 0 或 1 的情况。Linux 系统下默认为 0 即区分大小写，我们来看下 lower_case_table_names 为 0 时数据库的具体表现：

#查看参数设置
mysql>showvariableslike'lower_case_table_names';
+------------------------+-------+
|Variable_name|Value|
+------------------------+-------+
|lower_case_table_names|0|
+------------------------+-------+
#创建数据库
mysql>createdatabaseTestDb;
QueryOK,1rowaffected(0.01sec)
mysql>createdatabasetestdb;
QueryOK,1rowaffected(0.02sec)
mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|TestDb|
|mysql|
|performance_schema|
|sys|
|testdb|
+--------------------+
mysql>usetestdb;
Databasechanged
mysql>useTestDb;
Databasechanged
mysql>useTESTDB;
ERROR1049(42000):Unknowndatabase'TESTDB'
#创建表
mysql>CREATETABLEifnotexists`test_tb`(
->`increment_id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'自增主键',
->`stu_id`int(11)NOTNULLCOMMENT'学号',
->`stu_name`varchar(20)DEFAULTNULLCOMMENT'学生姓名',
->PRIMARYKEY(`increment_id`),
->UNIQUEKEY`uk_stu_id`(`stu_id`)USINGBTREE
->)ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT='test_tb';
QueryOK,0rowsaffected(0.06sec)
mysql>CREATETABLEifnotexists`Student_Info`(
->`increment_id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'自增主键',
->`Stu_id`int(11)NOTNULLCOMMENT'学号',
->`Stu_name`varchar(20)DEFAULTNULLCOMMENT'学生姓名',
->PRIMARYKEY(`increment_id`),
->UNIQUEKEY`uk_stu_id`(`Stu_id`)USINGBTREE
->)ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT='Student_Info';
QueryOK,0rowsaffected(0.06sec)
mysql>showtables;
+------------------+
|Tables_in_testdb|
+------------------+
|Student_Info|
|test_tb|
+------------------+
#查询表
mysql>selectStu_id,Stu_namefromtest_tblimit1;
+--------+----------+
|Stu_id|Stu_name|
+--------+----------+
|1001|from1|
+--------+----------+
1rowinset(0.00sec)
mysql>selectstu_id,stu_namefromtest_tblimit1;
+--------+----------+
|stu_id|stu_name|
+--------+----------+
|1001|from1|
+--------+----------+
mysql>selectstu_id,stu_namefromTest_tb;
ERROR1146(42S02):Table'testdb.Test_tb'doesn'texist
mysql>selectStu_id,Stu_namefromtest_tbasAwhereA.Stu_id=1001;
+--------+----------+
|Stu_id|Stu_name|
+--------+----------+
|1001|from1|
+--------+----------+
1rowinset(0.00sec)
mysql>selectStu_id,Stu_namefromtest_tbasAwherea.Stu_id=1001;
ERROR1054(42S22):Unknowncolumn'a.Stu_id'in'whereclause'
#查看磁盘上的目录及文件
[root@localhost~]#:/var/lib/mysql#ls-lh
total616M
drwxr-x---2mysqlmysql20Jun314:25TestDb
...
drwxr-x---2mysqlmysql144Jun314:40testdb
[root@localhost~]#:/var/lib/mysql#cdtestdb/
[root@localhost~]#:/var/lib/mysql/testdb#ls-lh
total376K
-rw-r-----1mysqlmysql8.6KJun314:33Student_Info.frm
-rw-r-----1mysqlmysql112KJun314:33Student_Info.ibd
-rw-r-----1mysqlmysql8.6KJun314:40TEST_TB.frm
-rw-r-----1mysqlmysql112KJun314:40TEST_TB.ibd
-rw-r-----1mysqlmysql67Jun314:25db.opt
-rw-r-----1mysqlmysql8.6KJun314:30test_tb.frm
-rw-r-----1mysqlmysql112KJun314:30test_tb.ibd

通过以上实验我们发现 lower_case_table_names 参数设为 0 时，MySQL 库表名是严格区分大小写的，而且表别名同样区分大小写但列名不区分大小写，查询时也需要严格按照大小写来书写。同时我们注意到，允许创建名称同样但大小写不一样的库表名（比如允许 TestDb 和 testdb 库共存）。

你有没有考虑过 lower_case_table_names 设为 0 会出现哪些可能的问题，比如说：一位同事创建了 Test 表，另一位同事在写程序调用时写成了 test 表，则会报错不存在，更甚者可能会出现 TestDb 库与 testdb 库共存，Test 表与 test 表共存的情况，这样就更加混乱了。所以为了实现最大的可移植性和易用性，我们可以采用一致的约定，例如始终使用小写名称创建和引用库表。也可以将 lower_case_table_names 设为 1 来解决此问题，我们来看下此参数为 1 时的情况：

#将上述测试库删除并将lower_case_table_names改为1然后重启数据库
mysql>showvariableslike'lower_case_table_names';
+------------------------+-------+
|Variable_name|Value|
+------------------------+-------+
|lower_case_table_names|1|
+------------------------+-------+
#创建数据库
mysql>createdatabaseTestDb;
QueryOK,1rowaffected(0.02sec)
mysql>createdatabasetestdb;
ERROR1007(HY000):Can'tcreatedatabase'testdb';databaseexists
mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|mysql|
|performance_schema|
|sys|
|testdb|
+--------------------+
7rowsinset(0.00sec)
mysql>usetestdb;
Databasechanged
mysql>useTESTDB;
Databasechanged
#创建表
mysql>CREATETABLEifnotexists`test_tb`(
->`increment_id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'自增主键',
->`stu_id`int(11)NOTNULLCOMMENT'学号',
->`stu_name`varchar(20)DEFAULTNULLCOMMENT'学生姓名',
->PRIMARYKEY(`increment_id`),
->UNIQUEKEY`uk_stu_id`(`stu_id`)USINGBTREE
->)ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT='test_tb';
QueryOK,0rowsaffected(0.05sec)
mysql>createtableTEST_TB(idint);
ERROR1050(42S01):Table'test_tb'alreadyexists
mysql>showtables;
+------------------+
|Tables_in_testdb|
+------------------+
|test_tb|
+------------------+
#查询表
mysql>selectstu_id,stu_namefromtest_tblimit1;
+--------+----------+
|stu_id|stu_name|
+--------+----------+
|1001|from1|
+--------+----------+
1rowinset(0.00sec)
mysql>selectstu_id,stu_namefromTest_Tblimit1;
+--------+----------+
|stu_id|stu_name|
+--------+----------+
|1001|from1|
+--------+----------+
1rowinset(0.00sec)
mysql>selectstu_id,stu_namefromtest_tbasAwherea.stu_id=1002;
+--------+----------+
|stu_id|stu_name|
+--------+----------+
|1002|dfsfd|
+--------+----------+
1rowinset(0.00sec)

当 lower_case_table_names 参数设为 1 时，可以看出库表名统一用小写存储，查询时不区分大小写且用大小写字母都可以查到。这样会更易用些，程序里无论使用大写表名还是小写表名都可以查到这张表，而且不同系统间数据库迁移也更方便，这也是建议将 lower_case_table_names 参数设为 1 的原因。

2.参数变更注意事项

lower_case_table_names 参数是全局系统变量，不可以动态修改，想要变动时，必须写入配置文件然后重启数据库生效。如果你的数据库该参数一开始为 0 ，现在想要改为 1 ，这种情况要格外注意，因为若原实例中存在大写的库表，则改为 1 重启后，这些库表将会不能访问。如果需要将 lower_case_table_names 参数从 0 改成 1 ，可以按照下面步骤修改：

首先核实下实例中是否存在大写的库及表，若不存在大写的库表，则可以直接修改配置文件然后重启。若存在大写的库表，则需要先将大写的库表转化为小写，然后才可以修改配置文件重启。

当实例中存在大写库表时，可以采用下面两种方法将其改为小写：

1、通过 mysqldump 备份相关库，备份完成后删除对应库，之后修改配置文件重启，最后将备份文件重新导入。此方法用时较长，一般很少用到。

2、通过 rename 语句修改，具体可以参考下面 SQL：

#将大写表重命名为小写表
renametableTESTtotest;
#若存在大写库则需要先创建小写库然后将大写库里面的表转移到小写库
renametableTESTDB.test_tbtotestdb.test_tb;
#分享两条可能用到的SQL
#查询实例中有大写字母的表
SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
information_schema.`TABLES`
WHERE
TABLE_SCHEMANOTIN('information_schema','sys','mysql','performance_schema')
ANDtable_type='BASETABLE'
ANDTABLE_NAMEREGEXPBINARY'[A-Z]'

#拼接SQL将大写库中的表转移到小写库
SELECT
CONCAT('renametableTESTDB.',TABLE_NAME,'totestdb.',TABLE_NAME,';')
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA='TESTDB';