MySQL case-insensitive settings

Foreword:

Generally, in the database usage specification, we will see such a rule: lowercase English is used for library names and table names. Have you ever thought about why lowercase is recommended? Should the library table name be case sensitive? With these questions, let's take a look at this article.

1. The parameters that determine whether the case is sensitive

In MySQL, the database corresponds to the directory in the data directory. Each table in the database corresponds to at least one file (possibly multiple files, depending on the storage engine) in the database directory. Therefore, the case sensitivity of the operating system determines whether the database is case sensitive, while the Windows system is not case sensitive, and the Linux system is case sensitive.

By default, the library table name is case-insensitive under Windows system, but it is case-sensitive under Linux system. Column names, index names, stored procedure, function and event names are not case-sensitive under any operating system, and column aliases are also not case-sensitive.

In addition, MySQL also provides the lower_case_table_names system variable. This parameter will affect the storage method of the table and database names on the disk and the use in MySQL. In the Linux system, the parameter defaults to 0. In the Windows system, the default The value is 1. On macOS, the default value is 2. Let's look at the specific meaning of each value:

Value	Meaning
0	The library table name is stored on the disk in the letter case specified in the creation statement, and the name is case-sensitive.
1	The library table name is stored on the disk in lowercase, and the name comparison is not case sensitive. MySQL converts all table names to lowercase when storing and searching. This behavior also applies to database names and table aliases.
2	The library table names are stored on the disk in the letter case specified in the creation statement, but MySQL converts them to lowercase when looking up. The name comparison is not case sensitive.

Generally, the lower_case_table_names parameter is rarely set to 2, and only the case of setting it to 0 or 1 will be discussed below. The default value of 0 in Linux system is case-sensitive. Let's look at the specific behavior of the database when lower_case_table_names is 0:

# 查看参数设置
mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+

# 创建数据库
mysql> create database TestDb;
Query OK, 1 row affected (0.01 sec)

mysql> create database testdb;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TestDb             |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+

mysql> use testdb;
Database changed
mysql> use TestDb;
Database changed
mysql> use TESTDB;
ERROR 1049 (42000): Unknown database 'TESTDB'

# 创建表
mysql> CREATE TABLE if not exists `test_tb` (
    ->   `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    ->   `stu_id` int(11) NOT NULL COMMENT '学号',
    ->   `stu_name` varchar(20) DEFAULT NULL COMMENT '学生姓名',
    ->   PRIMARY KEY (`increment_id`),
    ->   UNIQUE KEY `uk_stu_id` (`stu_id`) USING BTREE
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='test_tb';
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE TABLE if not exists `Student_Info` (
    ->   `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    ->   `Stu_id` int(11) NOT NULL COMMENT '学号',
    ->   `Stu_name` varchar(20) DEFAULT NULL COMMENT '学生姓名',
    ->   PRIMARY KEY (`increment_id`),
    ->   UNIQUE KEY `uk_stu_id` (`Stu_id`) USING BTREE
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Student_Info';
Query OK, 0 rows affected (0.06 sec)
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| Student_Info     |
| test_tb          |
+------------------+

# 查询表
mysql> select Stu_id,Stu_name from test_tb limit 1;
+--------+----------+
| Stu_id | Stu_name |
+--------+----------+
|   1001 | from1    |
+--------+----------+
1 row in set (0.00 sec)
mysql> select stu_id,stu_name from test_tb limit 1;
+--------+----------+
| stu_id | stu_name |
+--------+----------+
|   1001 | from1    |
+--------+----------+

mysql> select stu_id,stu_name from Test_tb;
ERROR 1146 (42S02): Table 'testdb.Test_tb' doesn't exist
mysql> select Stu_id,Stu_name from test_tb as A where A.Stu_id = 1001; 
+--------+----------+
| Stu_id | Stu_name |
+--------+----------+
|   1001 | from1    |
+--------+----------+
1 row in set (0.00 sec)
mysql> select Stu_id,Stu_name from test_tb as A where a.Stu_id = 1001;
ERROR 1054 (42S22): Unknown column 'a.Stu_id' in 'where clause'

# 查看磁盘上的目录及文件
[root@localhost ~]#:/var/lib/mysql# ls -lh
total 616M
drwxr-x--- 2 mysql mysql   20 Jun  3 14:25 TestDb
...
drwxr-x--- 2 mysql mysql  144 Jun  3 14:40 testdb
[root@localhost ~]#:/var/lib/mysql# cd testdb/
[root@localhost ~]#:/var/lib/mysql/testdb# ls -lh
total 376K
-rw-r----- 1 mysql mysql 8.6K Jun  3 14:33 Student_Info.frm
-rw-r----- 1 mysql mysql 112K Jun  3 14:33 Student_Info.ibd
-rw-r----- 1 mysql mysql 8.6K Jun  3 14:40 TEST_TB.frm
-rw-r----- 1 mysql mysql 112K Jun  3 14:40 TEST_TB.ibd
-rw-r----- 1 mysql mysql   67 Jun  3 14:25 db.opt
-rw-r----- 1 mysql mysql 8.6K Jun  3 14:30 test_tb.frm
-rw-r----- 1 mysql mysql 112K Jun  3 14:30 test_tb.ibd

Through the above experiments, we found that when the lower_case_table_names parameter is set to 0, the MySQL library table name is strictly case-sensitive, and the table alias is also case-sensitive but the column name is not case-sensitive, and the query also needs to be written strictly in accordance with the case. At the same time, we noticed that it is allowed to create library table names with the same name but different capitalization (for example, allowing TestDb and testdb libraries to coexist).

Have you considered the possible problems if lower_case_table_names is set to 0. For example, if a colleague creates a Test table, and another colleague writes a test table when writing a program call, an error will be reported that it does not exist, or even worse There may be cases where the TestDb library and the testdb library coexist, and the Test table and the test table coexist, which makes it even more confusing. So in order to achieve maximum portability and ease of use, we can adopt a consistent convention, such as always using lowercase names to create and reference library tables. You can also set lower_case_table_names to 1 to solve this problem. Let's look at the situation when this parameter is 1:

# 将上述测试库删除 并将 lower_case_table_names 改为 1 然后重启数据库
mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+

# 创建数据库
mysql> create database TestDb;
Query OK, 1 row affected (0.02 sec)

mysql> create database testdb;
ERROR 1007 (HY000): Can't create database 'testdb'; database exists
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
7 rows in set (0.00 sec)

mysql> use testdb;
Database changed
mysql> use TESTDB;
Database changed

# 创建表
mysql> CREATE TABLE if not exists `test_tb` (
    ->   `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    ->   `stu_id` int(11) NOT NULL COMMENT '学号',
    ->   `stu_name` varchar(20) DEFAULT NULL COMMENT '学生姓名',
    ->   PRIMARY KEY (`increment_id`),
    ->   UNIQUE KEY `uk_stu_id` (`stu_id`) USING BTREE
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='test_tb';
Query OK, 0 rows affected (0.05 sec)
mysql> create table TEST_TB (id int);
ERROR 1050 (42S01): Table 'test_tb' already exists

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test_tb          |
+------------------+

# 查询表
mysql> select stu_id,stu_name from test_tb limit 1;
+--------+----------+
| stu_id | stu_name |
+--------+----------+
|   1001 | from1    |
+--------+----------+
1 row in set (0.00 sec)

mysql> select stu_id,stu_name from Test_Tb limit 1;       
+--------+----------+
| stu_id | stu_name |
+--------+----------+
|   1001 | from1    |
+--------+----------+
1 row in set (0.00 sec)

mysql> select stu_id,stu_name from test_tb as A where a.stu_id = 1002;
+--------+----------+
| stu_id | stu_name |
+--------+----------+
|   1002 | dfsfd    |
+--------+----------+
1 row in set (0.00 sec)

When the lower_case_table_names parameter is set to 1, it can be seen that the library table names are stored in lowercase uniformly, and the query is case-insensitive and can be found in upper and lower case letters. This will be easier to use. This table can be found in the program regardless of whether the table name is uppercase or lowercase. It is also more convenient to migrate databases between different systems. This is why it is recommended to set the lower_case_table_names parameter to 1.

2. Precautions for parameter changes

The lower_case_table_names parameter is a global system variable and cannot be modified dynamically. When you want to change, you must write the configuration file and restart the database to take effect. If the parameter of your database was 0 at the beginning, and now you want to change it to 1, you need to pay special attention to this situation, because if there are uppercase library tables in the original instance, change it to 1 after restarting, these library tables will not be accessible . If you need to change the lower_case_table_names parameter from 0 to 1, you can modify it according to the following steps:

First, verify whether there are uppercase libraries and tables in the example. If there is no uppercase library table, you can directly modify the configuration file and restart. If there is an uppercase library table, you need to convert the uppercase library table to lowercase first, and then you can modify the configuration file to restart.

When there is an uppercase library table in the instance, you can use the following two methods to change it to lowercase:

1. Back up related libraries through mysqldump, delete the corresponding libraries after the backup is completed, modify the configuration file to restart, and finally re-import the backup file. This method takes a long time and is generally rarely used.
2. Modify through the rename statement. For details, please refer to the following SQL:

# 将大写表重命名为小写表 
rename table TEST to test;

# 若存在大写库 则需要先创建小写库 然后将大写库里面的表转移到小写库
rename table TESTDB.test_tb to testdb.test_tb;

# 分享两条可能用到的SQL
# 查询实例中有大写字母的表
SELECT
    TABLE_SCHEMA,
    TABLE_NAME
FROM
    information_schema.`TABLES` 
WHERE
    TABLE_SCHEMA NOT IN ( 'information_schema', 'sys', 'mysql', 'performance_schema' ) 
    AND table_type = 'BASE TABLE' 
    AND TABLE_NAME REGEXP BINARY '[A-Z]'
  
 
# 拼接SQL 将大写库中的表转移到小写库
SELECT
    CONCAT( 'rename table TESTDB.', TABLE_NAME, ' to testdb.', TABLE_NAME, ';' ) 
FROM
    information_schema.TABLES 
WHERE
    TABLE_SCHEMA = 'TESTDB';

summary:

This article mainly introduces the case of MySQL database tables. I believe that after reading this article, you should understand why lowercase English is recommended for database table names. If you want to change the lower_case_table_names parameter, you can also refer to the next article.