MySQL—设置数据库（库、表等）不区分大小写_mysql建表语句设置不区分大小写

查看MySQL版本

mysql -V或者mysql --version

修改my.cnf

1. 用root用户登录mysql部署节点，vim /etc/my.cnf配置文件；
2. 在[mysqld]下加入一行：lower_cas_table_names=1

重启MySQL

service mysqld restart

验证配置生效

1）登录mysql命令行

[bdoc@mysqlNode]$ mysql -uroot -p'123456'
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 1715
Server version: 5.7.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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.
1
2
3
4
5
6
7
8
9
10
11
12
13

2）查看配置：show variables like '%case%';

mysql> show variables like '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set
1
2
3
4
5
6
7
8

我们可以看到lower_case_table_names已经是1了，表示配置更改成功了。再去创建大写英文名的表，就发现不会出现大写。

3）验证大小写忽略

mysql> create DATABASE DB01;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db01               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database DB01;
Query OK, 0 rows affected (0.10 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

我们可以看到创建的是大写的DB01，显示的是小写的db01数据库。同样，我们也可以通过DB01去删除库。