关于mysql:MySQL不区分大小写设置

5次阅读

共计 6521 个字符，预计需要花费 17 分钟才能阅读完成。

前言：

个别在数据库应用标准中，咱们都会看到这么一条：库名及表名一律应用小写英文。你有没有思考过，为什么举荐应用小写呢？库表名是否应该辨别大小写呢？带着这些疑难，咱们一起来看下本篇文章。

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 参数，也能够参考下本篇文章哦。

正文完
mysql
发表至： mysql
2021-06-07
 0
关于数据库:MySQL-与-PostgreSQL-比较哪个更好我们该选用哪个
关于mysql:MySQL中的事务
关于mysql:MySQL-8034-在密码管理上的改进
关于mysql:mysql按时间范围分区

站内搜索

-「」