您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

关于MySQL库表名大小写问题

时间:2021-06-07 13:49:08  来源:  作者: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> 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 时的情况:

# 将上述测试库删除 并将 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:

# 将大写表重命名为小写表 
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 参数,也可以参考下本篇文章哦。



Tags:MySQL库表名   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
前言:一般在数据库使用规范中,我们都会看到这么一条:库名及表名一律使用小写英文。你有没有思考过,为什么推荐使用小写呢?库表名是否应该区分大小写呢?带着这些疑问,我们一起来看下...【详细内容】
2021-06-07  Tags: MySQL库表名  点击:(70)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(6)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(17)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(16)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(19)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(27)  评论:(0)  加入收藏
相关文章
    无相关信息
最新更新
栏目热门
栏目头条