关于MySQL 8.0数据库表名大小写的问题

2021-09-08 22:28:23

原文地址: http://blog.duhbb.com/2022/02/09/on-mysql-low-case-table-names/

引言

今天导库, 启动项目, 结果尼玛项目起不来, changelog疯狂执行, 正常情况下不会出现这样的. 找了好半天原因才发现库中存在同名但是大小写不一致的表名.

背景知识

bug: https://bugs.mysql.com/bug.php?id=90695

MySQL 8.0 新增了data dictionary的概念，数据初始化的时候在linux下默认使用lower-case-table-names=0的参数，数据库启动的时候读取的my.cnf文件中的值。若二者值不一致则在mysql的错误日志中记录报错信息。

在MySQL 5.7 之前则允许数据库初始化和启动的值不一致且以启动值为准。在MySQL 官方提供的RPM包中默认是使用lower-case-table-names=0，不太适合生产环境部署。在生产环境建议使用官方的二进制包。

官方解释：

After initialization, is is not allowed to change this setting.So “lower_case_table_names” needs to be set together with --initialize .

It is prohibited to start the server with a lower_case_table_names setting that is different from the setting used when the server was initialized. The restriction is necessary because collations used by various data dictionary table fields are determined by the setting defined when the server is initialized, and restarting the server with a different setting would introduce inconsistencies with respect to how identifiers are ordered and compared.

It is therefore necessary to configure lower_case_table_names to the desired setting before initializing the server. In most cases, this requires configuring lower_case_table_names in a MySQL option file before starting the MySQL server for the first time. For APT installations on Debian and Ubuntu, however, the server is initialized for you, and there is no opportunity to configure the setting in an option file beforehand. You must therefore use the debconf-set-selection utility prior to installing MySQL using APT to enable lower_case_table_names.

解决办法：

在mysql数据库初始化的时候指定不区分大小写，在数据库实例启动的时候也要指定不区分大小写。即数据库初始化时lower_case_table_names的值和数据库启动时的值需要一样。

在实际开发生产的应用中多是不区分大小写的即lower-case-table-names=1。

MySQL 8.0表名大小写问题

MySQL默认的lower-case-table-names的值为0, 也就是大小写敏感, 这个需要在初始化的时候指定, 不指定就是0, 1表示忽略表名大小写.

mysqld --user=mysql --lower-case-table-names=1 --initialize-insecure --basedir=/usr/local/mysql --datadir=/data/mysql/node1

反正我在debian上用apt-get安装时mysql data目录默认是/var/lib/mysql.

debian上的配置

删除data目录

先删除mysql的data目录, 由于我是本机环境, 所以不用备份.

重新初始化

mysqld --user=mysql --lower_case_table_names=1 --initialize-insecure

配置文件

my.cnf中加入如下的配置

[mysqld]
lower_case_table_names=1

到这里通过service mysql restart基本就可以正常启动了.

lower-case-table-names和lower_case_table_names区别

mysql 配置 my.cnf 与变量show variables like ''; 可能不一样。
在 my.cnf中有中杠, 有下划线; 不过中杠与下划线都支持。

注意：如果在配置文件里给出的某个选项是mysqld无法识别的，MySQL服务器将不启动。

原文链接：https://blog.csdn.net/u011665746/article/details/79067637

MySQL查看默认变量的值

There are several ways to see the names and values of system variables:

To see the values that a server uses based on its compiled-in defaults and any option files that it reads, use this command:

mysqld --verbose --help

To see the values that a server uses based only on its compiled-in defaults, ignoring the settings in any option files, use this command:

mysqld --no-defaults --verbose --help