mysql-5.7.26 版本，表不区分区分大小写问题

目录

• 目录
  • 一、表不区分大小写
  • 二、原因是：
  • 三、解决：

 

---

回到顶部

目录

一、表不区分大小写

问题描述: 查询某张表没有记录，或者不存在，明明是有这个表的。

mysql> select * from cm_version;
+---------+--------------------------------------+---------------------+------+-----------------------------------+-----------------------+
| VERSION | GUID | LAST_UPDATE_INSTANT | TS | HOSTNAME | LAST_ACTIVE_TIMESTAMP |
+---------+--------------------------------------+---------------------+------+-----------------------------------+-----------------------+
| 5.13.3 | 1e440f97-6cea-403c-b712-f853b2a74752 | 1576748189778 | NULL | dataexa-cdh-test-01/192.168.1.228 | 1576830025460 |
+---------+--------------------------------------+---------------------+------+-----------------------------------+-----------------------+

mysql> select * from CM_VERSION;
ERROR 1146 (42S02): Table 'cm.cm_vsersion' doesn't exist

 

二、原因是：

5.6.+ 数据库里面的表默认区分大小写

lower_case_table_names参数详解：

lower_case_table_names=1

其中0：区分大小写，1：不区分大小写

 

mysql> show variables like '%lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
+------------------------+-------+
2 rows in set (0.00 sec)

 

三、解决：

在/etc/my.cnf

[mysqld] 插入

lower_case_table_names=1

重启mysql，在检查

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

[client] port = 31061 socket = /home/ap/mysql/mysql.sock [mysqld] server_id=10 port = 31061 user = mysql character-set-server = utf8mb4 default_storage_engine = innodb lower_case_table_names=1 log_timestamps = SYSTEM socket = /home/ap/mysql/mysql.sock basedir =/home/ap/mysql datadir = /home/ap/mysql/data pid-file = /home/ap/mysql/mysql.pid max_connections = 1000 max_connect_errors = 1000 table_open_cache = 1024 max_allowed_packet = 128M open_files_limit = 65535 server-id=1 gtid_mode=on enforce_gtid_consistency=on log-slave-updates=1 log-bin=master-bin log-bin-index = master-bin.index relay-log = relay-log relay-log-index = relay-log.index binlog_format=row log_error = /home/ap/mysql/log/mysql-error.log skip-name-resolve log-slave-updates=1 relay_log_purge = 0 slow_query_log = 1 long_query_time = 1 slow_query_log_file = /home/ap/mysql/log/mysql-slow.log

 

3.1重启mysql命令

/etc/init.d/mysql restart

 

3.2再次验证

mysql> show variables like '%lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 1 |
+------------------------+-------+
2 rows in set (0.00 sec)

 

mysql> select * from CM_VERSION;
+---------+--------------------------------------+---------------------+------+-----------------------------------+-----------------------+
| VERSION | GUID | LAST_UPDATE_INSTANT | TS | HOSTNAME | LAST_ACTIVE_TIMESTAMP |
+---------+--------------------------------------+---------------------+------+-----------------------------------+-----------------------+
| 5.13.3 | 1e440f97-6cea-403c-b712-f853b2a74752 | 1576748189778 | NULL | dataexa-cdh-test-01/192.168.1.228 | 1576830522188 |
+---------+--------------------------------------+---------------------+------+-----------------------------------+-----------------------+
1 row in set (0.00 sec)