In-depth analysis of the Identifier Case Sensitivity problem in MySQL

In MySQL, you may encounter the problem of case sensitivity of table names. In fact, this is related to the platform (operating system) and the system variable lower_case_table_names. To summarize, you can check out the official document "Identifier Case Sensitivity" if you are interested.

In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Triggers also correspond to files. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names. This means such names are not case-sensitive in Windows, but are case-sensitive in most varieties of Unix. One notable exception is macOS, which is Unix-based but uses a default file system type (HFS+) that is not case-sensitive. However, macOS also supports UFS volumes, which are case-sensitive just as on any Unix. See Section 1.8.1, “MySQL Extensions to Standard SQL”. Thelower_case_table_names system variable also affects how the server handles identifier case sensitivity, as described later in this section.

In MySQL, databases correspond to directories within the data directory. Each table in a database corresponds to at least one file (possibly more, depending on the storage engine) in the database directory. Triggers also correspond to files. Therefore, the case sensitivity of the underlying operating system plays an important role in the case sensitivity of database, table, and trigger names. This means that the names are case-insensitive on Windows, but are case-sensitive on most flavors of Unix. A notable exception is macOS, which is based on Unix but uses a case-insensitive default file system type (HFS+). However, macOS also supports UFS volumes, which, like any Unix, are case-sensitive. See Section 1.8.1, “MySQL Extensions to Standard SQL“. The lower_case_table_names system variable also affects how the server handles case sensitivity of identifiers, as described later in this section.

Linux:

Database names and table names are strictly case-sensitive;
Table aliases are strictly case-sensitive;
Column names and column aliases are case-insensitive in all cases;
Variable names are also strictly case-sensitive;

Windows:

Not case sensitive
Under Mac OS (non-UFS volume):
Not case sensitive

Note: Column names, indexes, stored procedures, and event names are not case-sensitive on any platform, and column aliases are also not case-sensitive.

Notice: Column, index, stored routine, and event names are not case sensitive on any platform, nor are column aliases.

The following test environment is Red Hat Enterprise Linux Server release 5.7, MySQL 5.6.20:

mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 0 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql> 
mysql> use mydb;
Database changed
mysql> create table test(id int);
Query OK, 0 rows affected (0.07 sec)
mysql> create table TEST(id int);
Query OK, 0 rows affected (0.09 sec)
mysql> insert into test values(1);
Query OK, 1 row affected (0.03 sec)
mysql> insert into TEST value(2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+
|id|
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select * from TEST;
+------+
|id|
+------+
| 2 |
+------+
1 row in set (0.00 sec)
mysql>

After setting lower_case_table_names=1 in the configuration file my.cnf (1 means case-insensitive, 0 means case-sensitive), restart the MySQL service and perform the following test:

mysql> use mydb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from test;
+------+
|id|
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select * from TEST;
+------+
|id|
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> 

You can see that at this time, no matter test, TEST or Test, all are accessed as test. At this time, the "TEST" table cannot be accessed. The system variable lower_case_table_names is a read-only variable and cannot be modified in the current session. Under this setting, if the same table name exists, the following error will be encountered when using mysqldump to back up the database:

mysqldump: Got error: 1066: Not unique table/alias: 'test' when using LOCK TABLES

This situation is quite troublesome. You must set the variable lower_case_table_names=0 in the configuration file my.cnf and restart the MySQL service. Therefore, it is very important to plan ahead and use a unified naming rule to avoid such problems. In addition, the system variable lower_case_table_names has three values: 0, 1, and 2.

1. Set to 0: Table names are stored in the same case as the SQL you wrote, uppercase is uppercase and lowercase is lowercase, and comparison is case sensitive.

2. Set to 1: Table names are converted to lowercase and stored on disk, and comparisons are case-insensitive.

3. Set to 2: Table names are stored according to the case of the SQL you wrote, uppercase is uppercase and lowercase is lowercase, and they are all converted to lowercase when compared.

1: ERROR 1010 (HY000): Error dropping database (can't rmdir './xxxx', errno: 39)

1: ERROR 1010 (HY000): Error dropping database (can't rmdir './xxxx', errno: 39)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|MyDB|
| mydb |
|mysql |
| performance_schema |
|tmonitor|
| xiangrun |
+--------------------+
7 rows in set (0.01 sec)
mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 1 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql> drop database mydb;
ERROR 1010 (HY000): Error dropping database (can't rmdir './mydb', errno: 39)
mysql>

Solution: Set the variable lower_case_table_names=0 in the configuration file my.cnf, restart the MySQL service, and then you can drop the database.

2: ERROR 1049 (42000): Unknown database 'xxx'

mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 1 |
+------------------------+-------+
1 row in set (0.01 sec)
mysql> 
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|MyDB|
|mysql |
| performance_schema |
|tmonitor|
| xiangrun |
+--------------------+
6 rows in set (0.01 sec)
mysql> use MyDB;
ERROR 1049 (42000): Unknown database 'mydb'
mysql>

References:

https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html

Summarize

The above is the Identifier Case Sensitivity problem in MySQL that I introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!