MySQL库表名大小写的选择

吾爱主题阅读：269 2024-04-02 08:03:02 评论：0

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 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 # 查看参数设置 mysql> show variables like 'lower_case_table_names' ; + ------------------------+-------+ | Variable_name | Value | + ------------------------+-------+ | lower_case_table_names | 0 | + ------------------------+-------+ # 创建数据库 mysql> create database TestDb; Query OK, 1 row affected (0.01 sec) mysql> create database testdb; Query OK, 1 row affected (0.02 sec) mysql> show databases; + --------------------+ | Database | + --------------------+ | information_schema | | TestDb | | mysql | | performance_schema | | sys | | testdb | + --------------------+ mysql> use testdb; Database changed mysql> use TestDb; Database changed mysql> use TESTDB; ERROR 1049 (42000): Unknown database 'TESTDB' # 创建表 mysql> CREATE TABLE if not exists `test_tb` ( -> `increment_id` int (11) NOT NULL AUTO_INCREMENT COMMENT '自增主键' , -> `stu_id` int (11) NOT NULL COMMENT '学号' , -> `stu_name` varchar (20) DEFAULT NULL COMMENT '学生姓名' , -> PRIMARY KEY (`increment_id`), -> UNIQUE KEY `uk_stu_id` (`stu_id`) USING BTREE -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT= 'test_tb' ; Query OK, 0 rows affected (0.06 sec) mysql> CREATE TABLE if not exists `Student_Info` ( -> `increment_id` int (11) NOT NULL AUTO_INCREMENT COMMENT '自增主键' , -> `Stu_id` int (11) NOT NULL COMMENT '学号' , -> `Stu_name` varchar (20) DEFAULT NULL COMMENT '学生姓名' , -> PRIMARY KEY (`increment_id`), -> UNIQUE KEY `uk_stu_id` (`Stu_id`) USING BTREE -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT= 'Student_Info' ; Query OK, 0 rows affected (0.06 sec) mysql> show tables; + ------------------+ | Tables_in_testdb | + ------------------+ | Student_Info | | test_tb | + ------------------+ # 查询表 mysql> select Stu_id,Stu_name from test_tb limit 1; + --------+----------+ | Stu_id | Stu_name | + --------+----------+ | 1001 | from1 | + --------+----------+ 1 row in set (0.00 sec) mysql> select stu_id,stu_name from test_tb limit 1; + --------+----------+ | stu_id | stu_name | + --------+----------+ | 1001 | from1 | + --------+----------+ mysql> select stu_id,stu_name from Test_tb; ERROR 1146 (42S02): Table 'testdb.Test_tb' doesn 't exist mysql> select Stu_id,Stu_name from test_tb as A where A.Stu_id = 1001; +--------+----------+ | Stu_id | Stu_name | +--------+----------+ | 1001 | from1 | +--------+----------+ 1 row in set (0.00 sec) mysql> select Stu_id,Stu_name from test_tb as A where a.Stu_id = 1001; ERROR 1054 (42S22): Unknown column ' a.Stu_id ' in ' where clause' # 查看磁盘上的目录及文件 [root@localhost ~]#:/var/lib/mysql# ls -lh total 616M drwxr-x --- 2 mysql mysql 20 Jun 3 14:25 TestDb ... drwxr-x --- 2 mysql mysql 144 Jun 3 14:40 testdb [root@localhost ~]#:/var/lib/mysql# cd testdb/ [root@localhost ~]#:/var/lib/mysql/testdb# ls -lh total 376K -rw-r ----- 1 mysql mysql 8.6K Jun 3 14:33 Student_Info.frm -rw-r ----- 1 mysql mysql 112K Jun 3 14:33 Student_Info.ibd -rw-r ----- 1 mysql mysql 8.6K Jun 3 14:40 TEST_TB.frm -rw-r ----- 1 mysql mysql 112K Jun 3 14:40 TEST_TB.ibd -rw-r ----- 1 mysql mysql 67 Jun 3 14:25 db.opt -rw-r ----- 1 mysql mysql 8.6K Jun 3 14:30 test_tb.frm -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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 # 将上述测试库删除并将 lower_case_table_names 改为 1 然后重启数据库 mysql> show variables like 'lower_case_table_names' ; + ------------------------+-------+ | Variable_name | Value | + ------------------------+-------+ | lower_case_table_names | 1 | + ------------------------+-------+ # 创建数据库 mysql> create database TestDb; Query OK, 1 row affected (0.02 sec) mysql> create database testdb; ERROR 1007 (HY000): Can 't create database ' testdb '; database exists mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb | +--------------------+ 7 rows in set (0.00 sec) mysql> use testdb; Database changed mysql> use TESTDB; Database changed # 创建表 mysql> CREATE TABLE if not exists `test_tb` ( -> `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT ' 自增主键 ', -> `stu_id` int(11) NOT NULL COMMENT ' 学号 ', -> `stu_name` varchar(20) DEFAULT NULL COMMENT ' 学生姓名 ', -> PRIMARY KEY (`increment_id`), -> UNIQUE KEY `uk_stu_id` (`stu_id`) USING BTREE -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' test_tb '; Query OK, 0 rows affected (0.05 sec) mysql> create table TEST_TB (id int); ERROR 1050 (42S01): Table ' test_tb' already exists mysql> show tables; + ------------------+ | Tables_in_testdb | + ------------------+ | test_tb | + ------------------+ # 查询表 mysql> select stu_id,stu_name from test_tb limit 1; + --------+----------+ | stu_id | stu_name | + --------+----------+ | 1001 | from1 | + --------+----------+ 1 row in set (0.00 sec) mysql> select stu_id,stu_name from Test_Tb limit 1; + --------+----------+ | stu_id | stu_name | + --------+----------+ | 1001 | from1 | + --------+----------+ 1 row in set (0.00 sec) mysql> select stu_id,stu_name from test_tb as A where a.stu_id = 1002; + --------+----------+ | stu_id | stu_name | + --------+----------+ | 1002 | dfsfd | + --------+----------+ 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 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 # 将大写表重命名为小写表 rename table TEST to test; # 若存在大写库则需要先创建小写库然后将大写库里面的表转移到小写库 rename table TESTDB.test_tb to testdb.test_tb; # 分享两条可能用到的SQL # 查询实例中有大写字母的表 SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_SCHEMA NOT IN ( 'information_schema' , 'sys' , 'mysql' , 'performance_schema' ) AND table_type = 'BASE TABLE' AND TABLE_NAME REGEXP BINARY '[A-Z]' # 拼接SQL 将大写库中的表转移到小写库 SELECT CONCAT( 'rename table TESTDB.' , TABLE_NAME, ' to testdb.' , TABLE_NAME, ';' ) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'TESTDB' ;

总结：

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

以上就是MySQL库表名大小写的选择的详细内容，更多关于MySQL库表名大小写的资料请关注服务器之家其它相关文章！

原文链接：https://mp.weixin.qq.com/s/YdQq1t0uAY1xSMd-wQZeQQ

可以去百度分享获取分享代码输入这里。

声明

1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，请转载时务必注明文章作者和来源，不尊重原创的行为我们将追究责任；3.作者投稿可能会经我们编辑修改或补充。