本文转载自微信公众号《MySQL技术》，作者：MySQL技术。转载本文请联系MySQL技术公众号。前言：一般在数据库使用规范中，我们都会看到这样的条款：数据库名和表名都是小写英文。你有没有想过为什么推荐使用小写字母？库表名是否应该区分大小写？带着这些疑问，我们来看看这篇文章。1、判断是否区分大小写的参数在MySQL中，数据库对应data目录下的目录。数据库中的每一张表对应于数据库目录中的至少一个文件（可能是多个文件，取决于存储引擎）。所以操作系统的大小写决定了数据库是否区分大小写，而Windows系统不区分大小写，Linux系统区分大小写。默认情况下，库表名称在Windows下不区分大小写，但在Linux下区分大小写。列名、索引名、存储过程、函数和事件名在任何操作系统下都是不区分大小写的，列别名也是不区分大小写的。此外，MySQL还提供了lower_case_table_names系统变量，它将影响表和数据库名称在磁盘上的存储方式以及它们在MySQL中的使用方式。在Linux系统中，该参数默认为0，在Windows系统中，默认为1，macOS系统中默认为2。我们来看下每个值的具体含义：值含义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|+------------------------+--------+#Createdatabasemysql>createdatabaseTestDb;QueryOK,1rowaffected(0.01sec)mysql>createdatabasetestdb;QueryOK,1rowaffected(0.02sec)mysql>showdatabases;+----------------+|数据库|+------------------+|information_schema||TestDb||mysql||performance_schema||sys||testdb|+-----------------+mysql>usetestdb;Databasechangedmysql>useTestDb;Databasechangedmysql>useTESTDB;ERROR1049(42000):Unknowndatabase'TESTDB'#Createtablemysql>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'texistmysql>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-lhtotal616Mdrwxr-x---2mysqlmysql20Jun314:25TestDb...drwxr-x---2mysqlmysql144Jun314:40testdb[root@localhost~]#:/var/lib/mysql#cdtestdb/[root@localhost~]#:/var/lib/mysql/testdb#ls-lhtotal376K-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库表名严格区分大小写，表别名也区分大小写但列名不区分大小写，并且query也需要严格按case写同时，我们注意到允许创建同名不同大小写的库表名（比如允许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|+--------------------+------+#Createdatabasemysql>createdatabaseTestDb;QueryOK,1rowaffected(0.02sec)mysql>createdatabasetestdb;ERROR1007(HY000):Can'tcreatedatabase'testdb';databaseexistsmysql>showdatabases;+----------------------+|数据库|+--------------------+|information_schema||mysql||performance_schema||sys||testdb|+------------------+7rowsinset(0.00sec)mysql>usetestdb;Databasechangedmysql>useTESTDB;Databasechanged#createtablemysql>CREATETABLEifnotexists`test_tb`(->`increment_id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'self-自增主键',->`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'alreadyexistsmysql>showtables;+---------------+|Tables_in_testdb|+----------------+|test_tb|+----------------+#查询表mysql>selectstu_id,stu_namefromtest_tblimit1;+-------+----------+|stu_id|stu_name|+--------+------------+|1001|from1|+--------+------------+1rowinset(0.00sec)mysql>选择stu_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#查询例子中有大写字母的表SELECTTABLE_SCHEMA,TABLE_NAMEFROMinformation_schema.`TABLES`WHERETABLE_SCHEMANOTIN('information_schema','sys','mysql','performance_schema')ANDtable_type='BASETABLE'ANDTABLE_NAMEREGEXPBINARY'[A-Z]'#拼接SQL将大写库中的表转移到小写库中SELECTCONCAT('renametableTESTDB.',TABLE_NAME,'totestdb.',TABLE_NAME,';')FROMinformation_schema.TABLESWHERETABLE_SCHEMA='TESTDB';摘要：本文主要介绍MySQL数据库表的大小写。相信看完这篇文章，你应该明白为什么推荐数据库表名使用小写英文了。如果要更改lower_case_table_names参数，也可以参考这篇文章。
