Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of Identifier Case Sensitivity problems in MySQL, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

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, the database corresponds to the directory in the data directory. Each table in the database corresponds to at least one file in the database directory (possibly more, depending on the storage engine). 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 these names are not case-sensitive in Windows, but are case-sensitive in most types of Unix. One 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 are case-sensitive like any Unix. See Section 1.8.1, "MySQL Extensions to Standard SQL". The lower_case_table_names system variable also affects how the server handles the case sensitivity of identifiers, as described later in this section.

Linux system:

Database names and table names are strictly case-sensitive

Aliases for tables are strictly case sensitive

Column names and column aliases are case ignored in all cases

Variable names are also strictly case-sensitive

Windows system:

Are not case-sensitive.

Under Mac OS (non-UFS volume):

Are not case-sensitive.

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

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

The following is Red Hat Enterprise Linux Server release 5.7 and MySQL 5.6.20 in the test environment:

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 changedmysql > 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 test as follows:

Mysql > use mydb;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > 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 whether it is test, TEST or Test, the test is accessed, and the "TEST" table cannot be accessed at this time. 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, you will encounter the following error when backing up the database with mysqldump:

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

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

1. Set to 0: table names are stored in the SQL case you write, uppercase is lowercase, lowercase is lowercase, comparison is case sensitive.

two。 Set to 1: the table name is converted to lowercase and stored to the hard disk, and the comparison is case-insensitive.

3. Set to 2: the table name is stored according to the SQL case you wrote, uppercase and lowercase, lowercase and lowercase comparison.

With regard to database name case sensitivity, you will encounter the following problems:

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 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.01sec) mysql > use MyDB ERROR 1049 (42000): Unknown database 'mydb'mysql > Thank you for reading this article carefully. I hope the article "sample Analysis of Identifier Case Sensitivity problems in MySQL" shared by the editor will be helpful to you. At the same time, I hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.