忽略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的表名的英文字母大小写 |