如果你现在问我，数据库的库名、表名、字段名，是否建议保持跟 Java 一样采用驼峰命名规则，我的答案是：除非是遗留系统，新系统绝对不建议，建议随大流，采用下划线命名法。
如果你要问我原因，那么，本文提到的坑就是理由。

有时候，随大流可能会少吃亏，可能会少走弯路。大流一定有大流的道理（跑题儿了，哈哈…）。

前言

  • 如果你的 MySQL 数据库需要同时跑在 windows 和 linux 上（比如在 windows 上开发，部署在 linux 上），或者需要在阿里 RDS 与 linux 自建 MySQL 上运行，那么一定要知道配置参数lower_case_table_names，因为一不小心就会踩到坑。
  • 数据库命名规则采用下划线命名法，能有效避免因数据库服务器环境差异带来的坑。

踩坑

曾经有一个项目，当时的表名及字段名的命名规范采用的是驼峰命名规则（不建议采用的命名方式），线上用的是阿里云 RDS，上线时做数据导入，表名全部被转为小写了，当时阿里的 RDS 还不支持配置lower_case_table_names 参数，lower_case_table_names 默认为 1，也就是存入数据库时，会自动把所有大写字母转换为小写，查询时不区分大小写。后来由于项目调整，改为用自建 MySQL 5.6，从 RDS 导入之后，由于表名、字段名全是小写，所以需要将 MySQL 配置成大小写不敏感，于是调整了数据库配置，添加了：lower_case_table_names=1，重启之后，项目就可以正常操作数据库了。

如果到这儿就结束了，那么，这是一个比较顺利（完美）的解决方案。

可是，再后来，要迁移服务器，新服务器上的 MySQL 是 8.x，而且已经有数据库实例了，同时，默认大小写敏感，这种场景，就不能简单的添加 lower_case_table_names=1 或者修改这个配置，因为 MySQL 8.0 不支持数据库初始化后修改该参数，后面会提到。

是不是有点像编故事呢？如果说这是一个故事的话，那么，这个故事是真实的。

言归正传，以下正式介绍 lower_case_table_names

lower_case_table_names 参数说明

lower_case_table_names 是 mysql 一个大小写敏感设置的属性，此参数不可以动态修改。

  • 8.0 之前的版本，可以通过配置文件修改，修改后重启数据库生效。
  • 8.0 之后的版本，只允许在初始化时指定，之后就不允许修改了。

修改配置

  • 修改文件 my.ini （linux 下是 my.cnf，默认位于 /etc/mysql/），在 [mysqld] 配置节点下添加 lower-case-table-names=value，value 为 0-2 的整数。

默认值

  • unix,linux 下 默认值为 0
  • Windows 下默认值是 1
  • Mac OS 下默认值是 2

官方文档

参数说明

  1. lower_case_table_names=0 表名存储为给定的大小写。比较时：区分大小写。大小写敏感（Unix，Linux默认）。

    创建的数据库和表，将原样保存在磁盘上。如 create database TeSt; 将会创建一个 TeSt 的目录，create table AbCCC ... 将会原样生成 AbCCC.frm。SQL语句也会原样解析。

    • 注意：大小写敏感要操作系统支持，Windows 本身大小写不敏感，因此不能设置成大小写敏感，即不能设置为 lower_case_table_names=0。在 win10 下设置为 lower_case_table_names=0，初始化时，会报以下错误（后面还会提到）：
      1
      [ERROR] [MY-010158] [Server] The server option 'lower_case_table_names' is configured to use case sensitive table names but the data directory is on a case-insensitive file system which is an unsupported combination. Please consider either using a case sensitive file system for your data directory or switching to a case-insensitive table name mode.

  1. lower_case_table_names=1 表名存储为小写。比较时：不区分大小写。大小写不敏感（Windows默认）。

    创建的库表时，MySQL 将所有的库表名转换成小写存储在磁盘上。SQL 语句同样会将库表名转换成小写。如需要查询以前创建的 Test_table（生成 Test_table.frm 文件），即便执行 select * from Test_table，也会被转换成 select * from test_table，这样就会找不到表而报错。这种情况需要做数据迁移，具体迁移方案后面会提到。

  2. lower_case_table_names=2 表名存储为给定的大小写。比较时：小写。

    创建的库表将原样保存在磁盘上。但SQL语句将库表名转换成小写。

8.0 以前的版本，修改 lower_case_table_names 导致的常见不良隐患：如果在 lower_case_table_names=0 时，创建了含有大写字母的库表，改为 lower_case_table_names=1 后，则会无法被查到。

8.0 以前的版本，将默认的 lower_case_tables_name 由 0 设置成 1

需先将已经存在的库名和表名都转换为小写。

  • 针对仅表名存在大写字母的情况：
    • lower_case_tables_name=0 时，执行 rename table 将表名改成小写。
    • 设置 lower_case_tables_name=1，重启生效。
  • 针对库名存在大写字母的情况：
    • lower_case_tables_name=0 时，使用 mysqldump 导出，并删除老的数据库。
    • 设置 lower_case_tables_name=1，重启生效。
    • 创建只有小写字母的数据库实例，导入数据至该实例。

在 win10 上安装 MySQL 报错

my.ini 的参数 lower_case_tables_name=0 的情况下

1
2
3
4
C:\dev\mysql-5.7.23-winx64\mysql-5.7.23-winx64\bin>mysqld --initialize --console
2018-12-09T13:04:02.545614Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-12-09T13:04:03.309670Z 0 [ERROR] The server option 'lower_case_table_names' is configured to use case sensitive table names but the data directory is on a case-insensitive file system which is an unsupported combination. Please consider either using a case sensitive file system for your data directory or switching to a case-insensitive table name mode.
2018-12-09T13:04:03.314831Z 0 [ERROR] Aborting

大小写敏感要操作系统支持，Windows 本身大小写不敏感，因此不能设置成大小写敏感。

该问题，可以参考 5.7 官方文档：Identifier Case Sensitivity

摘录 5.7 的官方文档如下：
If you are using MySQL on only one platform, you do not normally have to change the lower_case_table_names variable from its default value. However, you may encounter difficulties if you want to transfer tables between platforms that differ in file system case sensitivity. For example, on Unix, you can have two different tables named my_table and MY_TABLE, but on Windows these two names are considered identical. To avoid data transfer problems arising from lettercase of database or table names, you have two options:

  • Use lower_case_table_names=1 on all systems. The main disadvantage with this is that when you use SHOW TABLES or SHOW DATABASES, you do not see the names in their original lettercase.
  • Use lower_case_table_names=0 on Unix and lower_case_table_names=2 on Windows. This preserves the lettercase of database and table names. The disadvantage of this is that you must ensure that your statements always refer to your database and table names with the correct lettercase on Windows. If you transfer your statements to Unix, where lettercase is significant, they do not work if the lettercase is incorrect.

my.ini 的参数 lower_case_tables_name 改为 2 或 1，则可正常安装，不过，设置为 2 的时候，用 MySQL Workbench 连接时会提示配置有问题：The server is in a system that does not properly support the selected lower_case_table_names option value. Some problems may occur.，这个问题下面会专门写。

以下是正常安装的提示：

1
2
3
4
5
6
7
C:\dev\mysql-5.7.23-winx64\mysql-5.7.23-winx64\bin>mysqld --initialize --console
2018-12-09T14:25:32.965759Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-12-09T14:25:34.370212Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-12-09T14:25:34.700644Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-12-09T14:25:34.842230Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 4a949feb-fbbe-11e8-beee-002535150600.
2018-12-09T14:25:34.857245Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-12-09T14:25:34.912538Z 1 [Note] A temporary password is generated for root@localhost: kDLL5K<iGsmo

MySQL Workbench 连接到 MySQL 8.0.21 提示配置有问题

windows 环境，lower_case_tables_name=2 的情况下得到的提示：

1
2
3
4
5
6
7
8
9
10
11
12
[Window Title]
MySQL Workbench

[Main Instruction]
Server Configuration Problems

[Content]
A server configuration problem was detected.
The server is in a system that does not properly support the selected lower_case_table_names option value. Some problems may occur.
Please consult the MySQL server documentation.

[ ] Don't show this message again  [OK]

实践证明，有此提示的情况下，并不影响正常使用，该提示可以忽略。

要彻底解决这个问题，就是在初始化时，将该参数的值指定为 1，或者不指定，使用默认值即可。

尝试修改 lower_case_table_names 参数

lower_case_table_names 当前值为 2：

1
lower_case_table_names = 2

改为 1

1
lower_case_table_names = 1

重启报错：

1
2
3
4
5
6
7
C:\Windows\system32>net start mysql
MySQL 服务正在启动 .
MySQL 服务无法启动。

服务没有报告任何错误。

请键入 NET HELPMSG 3534 以获得更多的帮助。

用 mysqld --console 命令查看具体的错误

1
2
3
4
5
6
7
8
C:\Windows\system32>mysqld --console
2020-07-25T07:11:31.005253Z 0 [System] [MY-010116] [Server] C:\dev\MySQL\mysql-8.0.21-winx64\bin\mysqld.exe (mysqld 8.0.21) starting as process 6556
2020-07-25T07:11:31.022631Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-07-25T07:11:31.467105Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-07-25T07:11:31.481285Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('2').
2020-07-25T07:11:31.484999Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-07-25T07:11:31.487849Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-07-25T07:11:31.546111Z 0 [System] [MY-010910] [Server] C:\dev\MySQL\mysql-8.0.21-winx64\bin\mysqld.exe: Shutdown complete (mysqld 8.0.21)  MySQL Community Server - GPL.

查看官网 8.0 的文档（5.7.x 无此内容），可以看到：lower_case_table_names can only be configured when initializing the server. Changing the lower_case_table_names setting after the server is initialized is prohibited.

意思是只能在初始化时指定 lower_case_table_names 参数，初始化之后该参数不允许修改。

针对大小写配置问题，对照 8.0 和 5.7 的文档，你会发现，5.7 版本支持在初始化之后修改 lower_case_table_names 参数，而且还给出了在不同值下创建的数据库的迁移方案。

而到了 8.0，只支持初始化时指定该参数，初始化之后，如果修改了该参数，启动就会报错，因为不允许在初始化之后修改这个值了。

针对大小写配置问题，针对不同版本的官网文档

以下是目前用得比较多的两个版本的文档：
8.0 官网文档链接：https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html
5.7 官网文档链接：https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html

解决

知道 MySQL 的规则就好办了，如果不需要数据迁移：

  • 删除 data 目录下的所有文件，重新初始化并且指定 lower_case_table_names 值。
  • 卸载 MySQL，然后重装，重装之后，在初始化数据库时指定 lower_case_table_names 值。

如果需要数据迁移，大致步骤如下：

  • 先转化数据库名、表表、字段名为想要的大小写，然后导出数据。
  • 用新的lower_case_table_names值重新初始化数据库。
  • 创建新实例，导入之前导出的数据。

指定 lower_case_table_names 的两种方式

  1. 初始化时指定，如：mysqld --initialize --console --lower-case-table-names=1，参考：https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names

    1
    2
    3
    4
    5
    C:\Windows\system32>mysqld --initialize --console --lower-case-table-names=1
    2020-07-25T08:39:17.886931Z 0 [System] [MY-013169] [Server] C:\dev\MySQL\mysql-8.0.21-winx64\bin\mysqld.exe (mysqld 8.0.21) initializing of server in progress as process 17116
    2020-07-25T08:39:17.901930Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
    2020-07-25T08:39:19.157823Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
    2020-07-25T08:39:20.411299Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: g6+fg/+;qouD

  2. 初始化前，修改文件 my.ini （linux 下是 my.cnf，默认位于 /etc/mysql/），在 [mysqld] 配置节点下添加 lower-case-table-names=1。