办公常用软件技术学习网

办公常用软件技术学习网、Excel技术学习、PPT技术学习、Word技术学习、AI技术学习、PS技术学习,开发技术,数据库开发,SEO教程

联系客服联系客服

您现在的位置是:首页>数据库>关于MySQL库表名大小写问题

关于MySQL库表名大小写问题

2021-06-04 10:30 栏目:数据库

前言:

一般在数据库使用规范中,我们都会看到这么一条:库名及表名一律使用小写英文。你有没有思考过,为什么推荐使用小写呢?库表名是否应该区分大小写呢?带着这些疑问,我们一起来看下本篇文章。

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 时数据库的具体表现:

  1. # 查看参数设置 
  2. mysql> show variables like 'lower_case_table_names'
  3. +------------------------+-------+ 
  4. | Variable_name          | Value | 
  5. +------------------------+-------+ 
  6. | lower_case_table_names | 0     | 
  7. +------------------------+-------+ 
  8.  
  9. # 创建数据库 
  10. mysql> create database TestDb; 
  11. Query OK, 1 row affected (0.01 sec) 
  12.  
  13. mysql> create database testdb; 
  14. Query OK, 1 row affected (0.02 sec) 
  15.  
  16. mysql> show databases; 
  17. +--------------------+ 
  18. Database           | 
  19. +--------------------+ 
  20. | information_schema | 
  21. | TestDb             | 
  22. | mysql              | 
  23. | performance_schema | 
  24. | sys                | 
  25. | testdb             | 
  26. +--------------------+ 
  27.  
  28. mysql> use testdb; 
  29. Database changed 
  30. mysql> use TestDb; 
  31. Database changed 
  32. mysql> use TESTDB; 
  33. ERROR 1049 (42000): Unknown database 'TESTDB' 
  34.  
  35. # 创建表 
  36. mysql> CREATE TABLE if not exists `test_tb` ( 
  37.     ->   `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键'
  38.     ->   `stu_id` int(11) NOT NULL COMMENT '学号'
  39.     ->   `stu_name` varchar(20) DEFAULT NULL COMMENT '学生姓名'
  40.     ->   PRIMARY KEY (`increment_id`), 
  41.     ->   UNIQUE KEY `uk_stu_id` (`stu_id`) USING BTREE 
  42.     -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='test_tb'
  43. Query OK, 0 rows affected (0.06 sec) 
  44. mysql> CREATE TABLE if not exists `Student_Info` ( 
  45.     ->   `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键'
  46.     ->   `Stu_id` int(11) NOT NULL COMMENT '学号'
  47.     ->   `Stu_name` varchar(20) DEFAULT NULL COMMENT '学生姓名'
  48.     ->   PRIMARY KEY (`increment_id`), 
  49.     ->   UNIQUE KEY `uk_stu_id` (`Stu_id`) USING BTREE 
  50.     -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Student_Info'
  51. Query OK, 0 rows affected (0.06 sec) 
  52. mysql> show tables; 
  53. +------------------+ 
  54. | Tables_in_testdb | 
  55. +------------------+ 
  56. | Student_Info     | 
  57. | test_tb          | 
  58. +------------------+ 
  59.  
  60. # 查询表 
  61. mysql> select Stu_id,Stu_name from test_tb limit 1; 
  62. +--------+----------+ 
  63. | Stu_id | Stu_name | 
  64. +--------+----------+ 
  65. |   1001 | from1    | 
  66. +--------+----------+ 
  67. 1 row in set (0.00 sec) 
  68. mysql> select stu_id,stu_name from test_tb limit 1; 
  69. +--------+----------+ 
  70. | stu_id | stu_name | 
  71. +--------+----------+ 
  72. |   1001 | from1    | 
  73. +--------+----------+ 
  74.  
  75. mysql> select stu_id,stu_name from Test_tb; 
  76. ERROR 1146 (42S02): Table 'testdb.Test_tb' doesn't exist 
  77. mysql> select Stu_id,Stu_name from test_tb as A where A.Stu_id = 1001;  
  78. +--------+----------+ 
  79. | Stu_id | Stu_name | 
  80. +--------+----------+ 
  81. |   1001 | from1    | 
  82. +--------+----------+ 
  83. 1 row in set (0.00 sec) 
  84. mysql> select Stu_id,Stu_name from test_tb as A where a.Stu_id = 1001; 
  85. ERROR 1054 (42S22): Unknown column 'a.Stu_id' in 'where clause' 
  86.  
  87. # 查看磁盘上的目录及文件 
  88. [root@localhost ~]#:/var/lib/mysql# ls -lh 
  89. total 616M 
  90. drwxr-x--- 2 mysql mysql   20 Jun  3 14:25 TestDb 
  91. ... 
  92. drwxr-x--- 2 mysql mysql  144 Jun  3 14:40 testdb 
  93. [root@localhost ~]#:/var/lib/mysql# cd testdb/ 
  94. [root@localhost ~]#:/var/lib/mysql/testdb# ls -lh 
  95. total 376K 
  96. -rw-r----- 1 mysql mysql 8.6K Jun  3 14:33 Student_Info.frm 
  97. -rw-r----- 1 mysql mysql 112K Jun  3 14:33 Student_Info.ibd 
  98. -rw-r----- 1 mysql mysql 8.6K Jun  3 14:40 TEST_TB.frm 
  99. -rw-r----- 1 mysql mysql 112K Jun  3 14:40 TEST_TB.ibd 
  100. -rw-r----- 1 mysql mysql   67 Jun  3 14:25 db.opt 
  101. -rw-r----- 1 mysql mysql 8.6K Jun  3 14:30 test_tb.frm 
  102. -rw-r----- 1 mysql mysql 112K Jun  3 14:30 test_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 时的情况:

  1. # 将上述测试库删除 并将 lower_case_table_names 改为 1 然后重启数据库 
  2. mysql> show variables like 'lower_case_table_names'
  3. +------------------------+-------+ 
  4. | Variable_name          | Value | 
  5. +------------------------+-------+ 
  6. | lower_case_table_names | 1     | 
  7. +------------------------+-------+ 
  8.  
  9. # 创建数据库 
  10. mysql> create database TestDb; 
  11. Query OK, 1 row affected (0.02 sec) 
  12.  
  13. mysql> create database testdb; 
  14. ERROR 1007 (HY000): Can't create database 'testdb'; database exists 
  15. mysql> show databases; 
  16. +--------------------+ 
  17. Database           | 
  18. +--------------------+ 
  19. | information_schema | 
  20. | mysql              | 
  21. | performance_schema | 
  22. | sys                | 
  23. | testdb             | 
  24. +--------------------+ 
  25. rows in set (0.00 sec) 
  26.  
  27. mysql> use testdb; 
  28. Database changed 
  29. mysql> use TESTDB; 
  30. Database changed 
  31.  
  32. # 创建表 
  33. mysql> CREATE TABLE if not exists `test_tb` ( 
  34.     ->   `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键'
  35.     ->   `stu_id` int(11) NOT NULL COMMENT '学号'
  36.     ->   `stu_name` varchar(20) DEFAULT NULL COMMENT '学生姓名'
  37.     ->   PRIMARY KEY (`increment_id`), 
  38.     ->   UNIQUE KEY `uk_stu_id` (`stu_id`) USING BTREE 
  39.     -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='test_tb'
  40. Query OK, 0 rows affected (0.05 sec) 
  41. mysql> create table TEST_TB (id int); 
  42. ERROR 1050 (42S01): Table 'test_tb' already exists 
  43.  
  44. mysql> show tables; 
  45. +------------------+ 
  46. | Tables_in_testdb | 
  47. +------------------+ 
  48. | test_tb          | 
  49. +------------------+ 
  50.  
  51. # 查询表 
  52. mysql> select stu_id,stu_name from test_tb limit 1; 
  53. +--------+----------+ 
  54. | stu_id | stu_name | 
  55. +--------+----------+ 
  56. |   1001 | from1    | 
  57. +--------+----------+ 
  58. 1 row in set (0.00 sec) 
  59.  
  60. mysql> select stu_id,stu_name from Test_Tb limit 1;        
  61. +--------+----------+ 
  62. | stu_id | stu_name | 
  63. +--------+----------+ 
  64. |   1001 | from1    | 
  65. +--------+----------+ 
  66. 1 row in set (0.00 sec) 
  67.  
  68. mysql> select stu_id,stu_name from test_tb as A where a.stu_id = 1002; 
  69. +--------+----------+ 
  70. | stu_id | stu_name | 
  71. +--------+----------+ 
  72. |   1002 | dfsfd    | 
  73. +--------+----------+ 
  74. 1 row in set (0.00 sec) 

当 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:

  1. # 将大写表重命名为小写表  
  2. rename table TEST to test; 
  3.  
  4. # 若存在大写库 则需要先创建小写库 然后将大写库里面的表转移到小写库 
  5. rename table TESTDB.test_tb to testdb.test_tb; 
  6.  
  7. # 分享两条可能用到的SQL 
  8. # 查询实例中有大写字母的表 
  9. SELECT 
  10.  TABLE_SCHEMA, 
  11.  TABLE_NAME 
  12. FROM 
  13.  information_schema.`TABLES`  
  14. WHERE 
  15.  TABLE_SCHEMA NOT IN ( 'information_schema''sys''mysql''performance_schema' )  
  16.  AND table_type = 'BASE TABLE'  
  17.  AND TABLE_NAME REGEXP BINARY '[A-Z]' 
  18.    
  19.   
  20. # 拼接SQL 将大写库中的表转移到小写库 
  21. SELECT 
  22.  CONCAT( 'rename table TESTDB.', TABLE_NAME, ' to testdb.', TABLE_NAME, ';' )  
  23. FROM 
  24.  information_schema.TABLES  
  25. WHERE 
  26.  TABLE_SCHEMA = 'TESTDB'

总结:

本篇文章主要介绍了 MySQL 库表大小写问题,相信你看了这篇文章后,应该明白为什么库表名建议使用小写英文了。如果你想变更 lower_case_table_names 参数,也可以参考下本篇文章哦。

【编辑推荐】

  1. 鸿蒙官方战略合作共建——HarmonyOS技术社区
  2. 聊聊MySQL 8.0中的Json增强
  3. MySQL查询优化之一
  4. 数据库:MySQL、HBase、ElasticSearch三者对比
  5. MySQL 一棵 B+ 树能存多少条数据?
  6. 美团二面:Redis与MySQL双写一致性如何保证?

上一篇:美团二面:Redis与MySQL双写一致性如何保证?

下一篇:InnoDB秒级快照原理与当前读