详解记录MySQL中lower_case_table_names的坑

在 MySQL 中，lower_case_table_names 是一个非常重要的参数。它用于设置大小写敏感的表名是否被转换为小写。

设置

在 MySQL 的配置文件（my.cnf）中，可以通过以下方式对其进行设置：

[mysqld]
lower_case_table_names=1

它有以下三个取值：

• 0：大小写敏感
• 1：转换为小写
• 2：转换为小写，但保留原始的表名大小写

坑点1：切换值后需要重建表

当 lower_case_table_names 的值从 0 或 2 切换到 1 时，必须重建所有表，因为表名的大小写将被转换为小写。同样地，当该值从 1 切换到 0 或 2 时，表名的大小写将变得重要，可能导致查询失败。

以下是该坑点的示例说明：

• 将 lower_case_table_names 的值从 0 修改为 1，需要重建 test 表：

```sql
-- 原始表名
CREATE TABLE test (id INT PRIMARY KEY, name VARCHAR(20));

-- 修改配置文件中的 lower_case_table_names 值为 1
-- 重启 MySQL

-- 查询 test 表(应该会报错)，然后重命名并重新建立该表
RENAME TABLE test TO test_old;
CREATE TABLE test (id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO test (id, name) VALUES (1, 'hello');
SELECT * FROM test;
```

• 将 lower_case_table_names 的值从 1 修改为 0，也需要重建 test 表：

```sql
-- 原始表名(转换为小写)
CREATE TABLE test (id INT PRIMARY KEY, name VARCHAR(20));

-- 修改配置文件中的 lower_case_table_names 值为 0
-- 重启 MySQL

-- 查询 TEST 表(应该会报错)，然后重命名并重新建立该表(大小写敏感)
RENAME TABLE test TO test_old;
CREATE TABLE TEST (id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO TEST (id, name) VALUES (1, 'hello');
SELECT * FROM TEST;
```

坑点2：大小写不敏感可能导致数据丢失

在 lower_case_table_names 的值为 1 和 2 时，如果存在同名但大小写不同的表，可能会导致数据丢失。

以下是该坑点的示例说明：

• 以 lower_case_table_names 的值为 1 为例：

```sql
-- 创建 name 表(转换为小写)
CREATE TABLE name (id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO name (id, name) VALUES (1, 'name1');

-- 查询 NAME 表
SELECT * FROM NAME;

-- 创建 NAME 表
CREATE TABLE NAME (id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO NAME (id, name) VALUES (2, 'name2');

-- 查询 name 表
SELECT * FROM name;
```

这时候会发现，第二个插入的数据 "name2" 并未插入到数据库中，因为最后一个 SELECT 语句查询时使用的表名是小写的。

• 以 lower_case_table_names 的值为 2 为例：

```sql
-- 创建 name 表(转换为小写)
CREATE TABLE name (id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO name (id, name) VALUES (1, 'name1');

-- 查询 NAME 表
SELECT * FROM NAME;

-- 创建 NAME 表(保留原始大小写)
CREATE TABLE NAME (id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO NAME (id, name) VALUES (2, 'name2');

-- 查询 name 表
SELECT * FROM name;
```

这时候会发现，插入的数据均被保留，因为最后一个 SELECT 语句查询时使用的表名是小写的。

结论

在使用 MySQL 数据库时，需要注意 lower_case_table_names 参数，合理地设置它的值并且谨慎修改。在设计表结构时，也需要避免同名但大小写不同的表的存在。