Board logo

标题: 忽略Linux下MariaDB10.3的表名的英文字母大小写 [打印本页]
作者: admin    时间: 2022-7-12 09:11     标题: 忽略Linux下MariaDB10.3的表名的英文字母大小写

笺注：MariaDB的安装可参考 CentOS8安装LNMP+phpMyAdmin MariaDB的版本信息： MariaDB [(none)]> select version(); +-----------------+ | version() | +-----------------+ | 10.3.17-MariaDB | +-----------------+ 1 row in set (0.000 sec) MariaDB [(none)]> 默认情况下，Linux下MariaDB的表名是区分英文字母大小写： MariaDB [(none)]> show variables like '%low%'; +------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+ | innodb_disallow_writes | OFF | | log_slow_admin_statements | ON | | log_slow_disabled_statements | sp | | log_slow_filter | admin,filesort,filesort_on_disk,filesort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk | | log_slow_rate_limit | 1 | | log_slow_slave_statements | ON | | log_slow_verbosity | | | low_priority_updates | OFF | | lower_case_file_system | OFF | | lower_case_table_names | 0 | | max_allowed_packet | 16777216 | | slave_max_allowed_packet | 1073741824 | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | redhat8-slow.log | +------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+ 15 rows in set (0.002 sec) MariaDB [(none)]> 注释：lower_case_table_names = 0 测试： MariaDB [(none)]> create database data1; Query OK, 1 row affected (0.001 sec) MariaDB [(none)]> use data1; Database changed MariaDB [data1]> MariaDB [data1]> create table Table_1(id int); Query OK, 0 rows affected (0.101 sec) MariaDB [data1]> MariaDB [data1]> select * from Table_1; Empty set (0.001 sec) MariaDB [data1]> select * from table_1; ERROR 1146 (42S02): Table 'data1.table_1' doesn't exist MariaDB [data1]> ############ ############ 修改MariaDB的主配置文件，忽略表名的英文字母的大小写： [root@redhat8 ~]# vi /etc/my.cnf 在[mysqld]下添加： lower_case_table_names = 1 效果如下： [root@redhat8 ~]# cat /etc/my.cnf |grep -A2 "mysqld]" [mysqld] lower_case_table_names = 1 port = 3306 [root@redhat8 ~]# 重启MariaDB：（重启成功了） [root@redhat8 ~]# systemctl restart mysqld [root@redhat8 ~]# 更改已经生效： MariaDB [(none)]> show variables like '%low%'; +------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+ | innodb_disallow_writes | OFF | | log_slow_admin_statements | ON | | log_slow_disabled_statements | sp | | log_slow_filter | admin,filesort,filesort_on_disk,filesort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk | | log_slow_rate_limit | 1 | | log_slow_slave_statements | ON | | log_slow_verbosity | | | low_priority_updates | OFF | | lower_case_file_system | OFF | | lower_case_table_names | 1 | | max_allowed_packet | 1048576 | | slave_max_allowed_packet | 1073741824 | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | redhat8-slow.log | +------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+ 15 rows in set (0.002 sec) MariaDB [(none)]> 注释：lower_case_table_names = 1 再次测试： MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | data1 | | information_schema | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.001 sec) MariaDB [(none)]> MariaDB [(none)]> use data1; Database changed MariaDB [data1]> MariaDB [data1]> show tables; +-----------------+ | Tables_in_data1 | +-----------------+ | Table_1 | +-----------------+ 1 row in set (0.001 sec) MariaDB [data1]> MariaDB [data1]> select * from Table_1; ERROR 1146 (42S02): Table 'data1.table_1' doesn't exist MariaDB [data1]> MariaDB [data1]> select * from table_1; ERROR 1146 (42S02): Table 'data1.table_1' doesn't exist MariaDB [data1]> 解决方法： 删除库data1： MariaDB [data1]> drop database data1; Query OK, 1 row affected (0.007 sec) MariaDB [(none)]> MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.000 sec) MariaDB [(none)]> 重新创建库data1： MariaDB [(none)]> create database data1; Query OK, 1 row affected (0.001 sec) 切换到库data1： MariaDB [(none)]> use data1; Database changed 至此，MariaDB的表名不区分英文字母大小写了： MariaDB [data1]> create table Table_1(id int); Query OK, 0 rows affected (0.358 sec) MariaDB [data1]> MariaDB [data1]> select * from Table_1; Empty set (0.001 sec) MariaDB [data1]> MariaDB [data1]> select * from table_1; Empty set (0.000 sec) MariaDB [data1]> 不切换到库data1，也可以指定表名： MariaDB [(none)]> select * from data1.Table_1; Empty set (0.001 sec) MariaDB [(none)]> MariaDB [(none)]> select * from data1.table_1; Empty set (0.000 sec) MariaDB [(none)]> 查看库data1的表Table_1的表结构： MariaDB [(none)]> desc data1.Table_1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.001 sec) MariaDB [(none)]> MariaDB [(none)]> desc data1.table_1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.001 sec) MariaDB [(none)]> 相关文章： 忽略Linux下MySQL5.5的表名的英文字母大小写



欢迎光临 blog.zhuohua.store (http://blog.zhuohua.store/) Powered by Discuz! 7.2