忽略Linux下MySQL8.0的表名的英文字母大小写
笺注：MySQL8.0的安装可参考 CentOS8_Yum安装MySQL8.0
MySQL的版本信息：
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.26 |
+-----------+
1 row in set (0.00 sec)
mysql>
默认情况下，Linux下MySQL的表名是区分英文字母大小写：
show variables like '%low%';
注释：lower_case_table_names = 0
测试：
mysql> create database data1;
Query OK, 1 row affected (0.00 sec)
mysql> use data1;
Database changed
mysql>
mysql> create table Table1(id int);
Query OK, 0 rows affected (0.10 sec)
mysql>
mysql> select * from Table1;
Empty set (0.00 sec)
mysql>
mysql> select * from table1;
ERROR 1146 (42S02): Table 'data1.table1' doesn't exist
mysql>
############
############
修改MySQL的主配置文件，忽略表名的英文字母的大小写：
[root@redhat8 ~]# vi /etc/my.cnf
在[mysqld]下添加：
lower_case_table_names = 1
效果如下：
[root@redhat8 ~]# cat /etc/my.cnf |grep -v "^#" |grep -v "^$" |grep -A4 "mysqld]"
[mysqld]
lower_case_table_names = 1
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
[root@redhat8 ~]#
注释：MySQL数据目录为 /var/lib/mysql
重启MySQL：（重启失败了）
[root@redhat8 ~]# systemctl restart mysqld
Job for mysqld.service failed because the control process exited with error code.
See "systemctl status mysqld.service" and "journalctl -xe" for details.
[root@redhat8 ~]#
解决方法：
先删除MySQL的数据目录：
[root@redhat8 ~]# rm -rf /var/lib/mysql
[root@redhat8 ~]#
再初始化MySQL：
[root@redhat8 ~]# find / -name mysqld
/run/mysqld
/usr/sbin/mysqld
[root@redhat8 ~]#
[root@redhat8 ~]# /usr/sbin/mysqld --initialize --user=mysql --datadir=/var/lib/mysql --lower-case-table-names=1
2022-07-10T06:18:51.608547Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.26) initializing of server in progress as process 1858
2022-07-10T06:18:51.639963Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-07-10T06:18:52.372930Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-07-10T06:18:53.236022Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2022-07-10T06:18:53.236270Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2022-07-10T06:18:53.275457Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 7h?)1PPBwyKa
[root@redhat8 ~]#
注释：
MySQL初始化后，会出现数据库管理员root@localhost新的初始密码：7h?)1PPBwyKa
重启MySQL：（重启成功了）
[root@redhat8 ~]# systemctl restart mysqld
[root@redhat8 ~]#
使用数据库管理员root@localhost登录MySQL：
[root@redhat8 ~]# mysql -u"root" -h"localhost" -p"7h?)1PPBwyKa"
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.26
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show variables like '%low%';
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>
必须要修改数据库管理员root@localhost的密码：（密码要符合复杂性要求）
mysql> ALTER USER root@'localhost' IDENTIFIED BY 'P@ssw7rd';
Query OK, 0 rows affected (0.00 sec)
mysql>
使用数据库管理员root@localhost重新登录MySQL：
[root@redhat8 ~]# mysql -u"root" -h"localhost" -p"P@ssw7rd"
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.26 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
更改已经生效：
注释：lower_case_table_names = 1
再次测试：
mysql> use data1;
ERROR 1049 (42000): Unknown database 'data1'
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
注释：之前自定义创建的库data1已经没有了。
重新创建库data1：
mysql> create database data1;
Query OK, 1 row affected (0.00 sec)
至此，MySQL的表名不区分英文字母大小写了：
mysql> use data1;
Database changed
mysql>
mysql> create table Table1(id int);
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from Table1;
Empty set (0.01 sec)
mysql>
mysql> select * from table1;
Empty set (0.00 sec)
mysql>
相关文章：
忽略Linux下MySQL5.5的表名的英文字母大小写 |
