返回列表 发帖

忽略Linux下MySQL5.5的表名的英文字母大小写

笺注:这是在 LNMP一键安装包(lnmp_CentOS6.9) 的基础上进行的。


MySQL的版本信息:
mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.48-log |
+------------+
1 row in set (0.00 sec)

mysql>



默认情况下,Linux下MySQL的表名是区分英文字母大小写:
show variables like '%low%';
图片1.png
2022-7-10 10:31

注释:lower_case_table_names = 0



测试:
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql> use db1;
Database changed
mysql>
mysql> create table Table1(id int);
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> select * from Table1;
Empty set (0.00 sec)

mysql>
mysql> select * from table1;
ERROR 1146 (42S02): Table 'db1.table1' doesn't exist
mysql>



############
############

查看MySQL的主配置文件:(初始状态)
[root@localhost ~]# cat /etc/my.cnf |grep -v "^#" |grep -v "^$"
[client]
port            = 3306
socket          = /tmp/mysql.sock
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
datadir = /usr/local/mysql/var
skip-external-locking
key_buffer_size = 32M
max_allowed_packet = 1M
table_open_cache = 128
sort_buffer_size = 768K
net_buffer_length = 8K
read_buffer_size = 768K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
thread_cache_size = 16
query_cache_size = 16M
tmp_table_size = 32M
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
log-bin=mysql-bin
binlog_format=mixed
server-id       = 1
expire_logs_days = 10
default_storage_engine = InnoDB
innodb_data_home_dir = /usr/local/mysql/var
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/var
innodb_buffer_pool_size = 128M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 32M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 32M
sort_buffer_size = 768K
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[root@localhost ~]#



修改MySQL的主配置文件,忽略表名的英文字母的大小写:
[root@localhost ~]# vi /etc/my.cnf
[mysqld]下添加:
lower_case_table_names = 1



再次查看MySQL的主配置文件:
[root@localhost ~]# cat /etc/my.cnf |grep -v "^#" |grep -v "^$" |grep -A3 "mysqld]"
[mysqld]
lower_case_table_names = 1
port            = 3306
socket          = /tmp/mysql.sock
[root@localhost ~]#


重启MySQL:
[root@localhost ~]# service mysql restart
Shutting down MySQL.... SUCCESS!
Starting MySQL... SUCCESS!
[root@localhost ~]#


更改已经生效:
图片2.png
2022-7-10 10:34

注释:lower_case_table_names = 1


再次测试:
mysql> use db1;
Database changed
mysql>
mysql> select * from Table1;
ERROR 1146 (42S02): Table 'db1.table1' doesn't exist
mysql>
mysql> select * from table1;
ERROR 1146 (42S02): Table 'db1.table1' doesn't exist
mysql>


解决方法:
删除库db1:
mysql> drop database db1;
ERROR 1010 (HY000): Error dropping database (can't rmdir './db1', errno: 39)
mysql>


换种方式删除库db1:
[root@localhost ~]# find / -name "db1"
/usr/local/mysql/var/db1
[root@localhost ~]#
[root@localhost ~]# rm -rf /usr/local/mysql/var/db1
[root@localhost ~]#
[root@localhost ~]# find / -name "db1"
[root@localhost ~]#


确认库db1已经被删除:
mysql> drop database db1;
ERROR 1008 (HY000): Can't drop database 'db1'; database doesn't exist
mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

mysql>


重新创建库db1:
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)


至此,MySQL的表名不区分英文字母大小写了:
mysql> use db1;
Database changed
mysql>
mysql> create table Table1(id int);
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> select * from Table1;
Empty set (0.00 sec)

mysql>
mysql> select * from table1;
Empty set (0.00 sec)

mysql>




相关文章:
忽略Linux下MySQL8.0的表名的英文字母大小写
忽略Linux下MariaDB10.3的表名的英文字母大小写

返回列表