Please enable Javascript to view the contents

如何配置MySQL8中的lower_case_table_names来让其忽略大小写?

 ·  ☕ 4 分钟

在MySQL数据库中我们可以通过配置 lower_case_table_names=1 来让其忽略大小写。在 MySQL 8 以下版本我们很好配置,但在 MySQL 8 中配置起来却有些麻烦。本文将尝试说明其中的问题,并给出解决方式。

lower_case_table_names 的作用

lower_case_table_names 的值:

  • 如果设置为 0,表名将按指定方式存储,并且在对比表名时区分大小写。
  • 如果设置为 1,表名将以小写形式存储在磁盘上,在对比表名时不区分大小写。
  • 如果设置为 2,则表名按给定格式存储,但以小写形式进行比较。

此选项还适用于数据库名称和表别名。有关其他详细信息,请参阅第 9.2.3 节 “标识符区分大小写"。

由于 MySQL 最初依赖于文件系统来作为其数据字典,因此默认设置是依赖于文件系统是否 区分大小写。

在 Windows 上,默认值为 1。在 macOS 上,默认值是 2。在 Linux 上,不支持值 2;服 务器会将该值设置为 0。

在不区分大小写的文件系统上(比如 Windows 或 macOS 中的文件系统),运行 MySQL 时 ,不应使用 --lower_case_table_names=0 。 这将是不受支持的组合。

如果尝试在不区分大小写的文件系统上使用--lower_case_table_names=0 启动服务器, 则会打印错误消息并退出服务器。

如果使用 InnoDB 表,则应在所有平台上将此变量设置为 1,以强制将名称转换为小写。

必须在初始化 MySQL 服务器(安装 MySQL 后的首次启动)之前将 lower_case_table_names 配置为所需的值。 在大多数情况下,这需要在首次启动 MySQL 服务器之前在 MySQL 配置文件中设置 lower_case_table_names

但是,当你在 Debian 和 Ubuntu 使用 APT 安装 MySQL 时,系统将自动为您初始化 MySQL,并且没有机会事先在配置文件中设置该值。因此,在使用 APT 安装 MySQL 之前, 必须使用 debconf-set-selection 实用程序来启用小写的表名。为此,请在使用 APT 安 装 MySQL 之前运行此命令(官方给出的命令,我没用过):

1
shell> sudo debconf-set-selections <<< "mysql-server mysql-server/lowercase-table-names select Enabled

在 MySQL 8 中,数据目录初始化之后,不再允许更改 lower_case_table_names = 1 的 值;

MySQL 基于某些原因,禁止在重新启动 MySQL 服务时将 lower_case_table_names 设置 成不同于初始化 MySQL 服务时设置的 lower_case_table_names 值。

也就是说启动(重启)MySQL 时,lower_case_table_names的值必须于,初始化 MySQL 时(安装 MySQL 后的首次启动)的值相同。

如果您尝试使用与 MySQL 初始化时不同的lower_case_table_names值来启动 MySQL 8, 您将收到类似的错误:

1
2
3
4
5
# lower_case_table_names 在数据目录的值为 0 ,而 mysql 服务器中的值为 1
2019-04-14T03:57:19.095459Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
2019-04-14T03:57:19.097773Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-04-14T03:57:19.098425Z 0 [ERROR] [MY-010119] [Server] Aborting
2019-04-14T03:57:20.784893Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.15)  MySQL Community Server - GPL.

安装 MySQL 8时配置不区分大小写

那么用 lower_case_table_names = 1 初始化 MySQL 8 的步骤是什么?

在 CentOS 中安装MySQL8:

  1. 添加 MySQL YUM repository (添加 MySQL 的 yam 仓库,略)
  2. 卸载当前系统中的其它 MySQL。(如果需要同时安装不同版本的 MySQL,请使用 tarball 发行版。)
  3. 清除数据目录: 为了能够初始化 MySQL,数据目录必须为空。 您可以选择对数据目录 使用非默认位置;也可以删除 /var/lib/mysql 目录。 如果要保留旧的数据目录,请 先进行备份!
  4. 安装 MySQL 8 :通过 yam install
  5. 初始化前指定 lower_case_table_names = 1: 方法,在初次使用 systemd 启动 mysqld 之前,在MySQL配置文件( /etc/my.cnf )中添加 lower_case_table_names=1
  6. 初始化: systemctl start mysqld

在 Debian 和 Ubuntu 中:

  • 与之类似,但在安装 MySQL 8 之前,需要借助 debconf-set-selections 运行上文讲 到过的命令。

为已安装的MySQL8设置不区分大小写

  • 停止MySQL
  • 删除数据目录,即删除 /var/lib/mysql 目录
  • 在MySQL配置文件( /etc/my.cnf )中添加 lower_case_table_names=1
  • 启动 MySQL

为Docker中的MySQL8容器设置不区分大小写

官方MySQL镜像,是基于 debian 系统的,FROM debian:buster-slim,并在该系统中使用 apt-get 安装 mysql-community-server ;详见其 Dockerfile

使用下面的方式运行 MySQL 8 容器:

设置字符集和排序方式

1
docker run --name some-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

然后再来配置 mysql 8 忽略大小写:

  • 先使用上面的命令创建并运行容器 some-mysql
  • 在主机中创建配置文件,比如 /root/docker/mysql8/conf.d/config-file.cnf,文件内容如下:
[mysqld]
  lower_case_table_names = 1
  • 使用下面的命令将该文件复制到容器中
1
docker cp /root/docker/mysql8/conf.d/config-file.cnf some-mysql:/etc/mysql/conf.d/
  • 停止容器
  • 删除数据目录,比如删除 /var/lib/mysql
  • 重启容器

参考

您的鼓励是我最大的动力
alipay QR Code

Felix
作者
Felix
如无必要,勿增实体。

3

目录