MySQL8.0.34 中查询表要求区别大小写

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.34    |
+-----------+
1 row in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.03 sec)

mysql> SHOW VARIABLES LIKE 'collation_database';
+--------------------+--------------------+
| Variable_name      | Value              |
+--------------------+--------------------+
| collation_database | utf8mb4_0900_ai_ci |
+--------------------+--------------------+
1 row in set (0.03 sec)

mysql> SHOW TABLE STATUS LIKE 'account';
1046 - No database selected
mysql> SHOW VARIABLES LIKE 'collation_%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.03 sec)

mysql> SHOW FULL COLUMNS FROM users;
1046 - No database selected
mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.03 sec)

查询数据库中默认是忽略了大小写字符识别的，但执行SELECT查询语句时还是要求表名及字段名称要一致，

在my.cnf中加上 lower_case_table_names=1，执行 systemctl start mysqld时失败

[root@daihatestdb12178 log]# vi /etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
lower_case_table_names=1

• 删除原来的myql目录中内容，并重新初始化mysql

[root@daihatestdb12178 ~]# rm -rf /var/lib/mysql/*
[root@daihatestdb12178 ~]# mysqld --initialize --user=mysql
[root@daihatestdb12178 ~]# chown -R mysql:mysql /var/lib/mysql
[root@daihatestdb12178 ~]# chmod -R 755 /var/lib/mysql
[root@daihatestdb12178 ~]# cat /var/log/mysqld.log
2025-09-16T02:21:39.921422Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-09-16T02:21:41.641258Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-09-16T02:21:45.659982Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: wYsf)ha_w9i2

• 修改MYSQL账号的密码：

[root@daihatestdb12178 lib]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 145
Server version: 8.0.34

Copyright (c) 2000, 2023, 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> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set (0.02 sec)

mysql> ALTER  USER  'root'@'localhost'  IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.06 sec)

mysql> create user 'root'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> flush PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

• 启动MYSQL服务

[root@daihatestdb12178 ~]# systemctl start mysqld
[root@daihatestdb12178 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2025-09-16 11:07:38 CST; 3h 58min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 16596 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 16624 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/mysqld.service
           └─16624 /usr/sbin/mysqld

Sep 16 11:07:35 daihatestdb12178 systemd[1]: Starting MySQL Server...
Sep 16 11:07:38 daihatestdb12178 systemd[1]: Started MySQL Server.