关于MySQL8.0数据库表名⼤⼩写的问题
引⾔
今天导库, 启动项⽬, 结果项⽬起不来, changelog疯狂执⾏, 正常情况下不会出现这样的. 了好半天原因才发现库中存在同名但是⼤⼩写不⼀致的表名.
背景知识
MySQL 8.0 新增了data dictionary的概念，数据初始化的时候在linux下默认使⽤lower-case-table-names=0的参数，数据库启动的时候读取的myf⽂件中的值。若⼆者值不⼀致则在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
mysql下载starting the server配置⽂件
myf中加⼊如下的配置
[mysqld]
lower_case_table_names=1
到这⾥通过service mysql restart基本就可以正常启动了.
lower-case-table-names和lower_case_table_names区别
mysql 配置 myf 与变量show variables like ''; 可能不⼀样。
在 myf中有中杠, 有下划线; 不过中杠与下划线都⽀持。
注意：如果在配置⽂件⾥给出的某个选项是mysqld⽆法识别的，MySQL服务器将不启动。
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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。