MySQL is case sensitive

Be a positive person

Code, fix bugs, improve yourself

I have a paradise, facing programming, spring flowers!

00 MYSQL is case-sensitive

See the word such as face, see the title of the content. Have you ever experienced MYSQL being poked because it is case sensitive?

I have read alibaba Java development manual before and found it in MySql table building protocol:

[Mandatory] The table name and field name must contain lowercase letters or digits. Do not start with a digit or contain only digits between two underscores (_). Database field names can be expensive to change because they cannot be pre-published, so they need to be considered carefully.

Note: MySQL is case insensitive on Windows, but case sensitive by default on Linux. Therefore, do not allow any uppercase letters in database names, table names, and field names to avoid complications.

AliyunAdmin, rdc_config, level 3_ name Example: AliyunAdmin, rdcConfig, level 3 name

If there is no real encounter with similar problems, sometimes dry look at these regulations experience is not deep, understanding up vaguely understand, and just rote memorization.

01 a table letter size story

Recently, I have been working on a project. There has been no problem in the development and testing process of my own computer, but after deployment to the Linux server, I found an error, and the log information is about:

MySQLSyntaxErrorException: Table ‘kytu.tb_sutyHo’ doesn’t exist

There is a problem, a little depressed, local development is good, how to deploy the server is not good. A ghost… But don’t panic. Table tb_sutyHo does not exist.

Tb_sutyho (‘ h ‘); tb_sutyho (‘ h ‘);

Tb_sutyHo = tb_sutyHo = tb_sutyHo;

Problem found, the original is accidentally write SQL did not write the table name, change the table name is done, the function is also normal. That’s where the story would normally end, right? The problem was found, fixed, and all is well, ready to eat chicken later.

It’s important to find the problem and solve it, but it’s even more important to find the root cause of the problem so that you can avoid it the next time and not fall into the same trap twice as a programmer.

I am wondering why the local Window environment has not shown this error message. Wait until I deploy the server. What’s the problem? (If you are familiar with Mysql size sensitivity, you can skip….)

Mysql controls the case sensitivity of database and table names with the lower_case_table_names parameter.

View the following in the local Window environment:

mysql> show variables like '%case%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_file_system  | ON | | lower_case_table_names | 1 | +------------------------+-------+Copy the code

View the following information on the Linux server:

mysql> show variables like '%case%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_file_system  | OFF | | lower_case_table_names | 0 | +------------------------+-------+Copy the code

The difference can be seen from the above results, but I don’t have a feeling for these two parameters, so I don’t know what they mean.

When introducing lower_case_table_names, I’ll also mention lower_case_file_system.

lower_case_file_system

This variable describes the case sensitivity of the file name on the file system where the data directory resides. OFF means file names are case sensitive and ON means they are case insensitive. This variable is read-only because it reflects the file system properties and setting it has no effect on the file system.

lower_case_table_names

This parameter is static and can be set to 0, 1, or 2.

0 — case sensitive. (Unix, Linux default) The created library tables are saved as is on disk. Such as the create database TeSt; Create table AbCCC… Abccc.frm will be generated as is. SQL statements are also parsed as-is.

1 — case insensitive. MySQL converts all library table names to lowercase and stores them on disk when creating library tables (default on Windows). The SQL statement also converts the library table name to lowercase. If you need to query the previously created Test_table (generate test_table. FRM file), even if you execute the select * from Test_table, it will be converted to select * from Test_table, so that the error table does not exist.

2 — Case insensitive (OS X default) Library tables created are saved as is on disk. But the SQL statement converts the library table name to lowercase.

On Windows the default value is 1. On macOS, the default value is 2. On Linux, a value of 2 is not supported; the server forces the value to 0 instead.

On Windows, the default value is 1. On macOS, the default is 2. The value 2 is not supported on Linux; The server enforces a value of 0.

If you are running MySQL on a system where the data directory resides on a case-insensitive file system (such as Windows or macOS), you should not set lower_case_table_names to 0.

When I tried to set lower_case_table_names to 0 in my Window10 environment, the MySQL service failed to start. Windows is case-insensitive, as shown below:

Note: If you want to change the lower_case_table_names value, you need to change the my.ini configuration file in Windows and the my. CNF configuration file in Linux. You need to restart the service.

02 Precautions

Common hidden trouble caused by changing lower_case_table_names: If a library table with uppercase letters is created when lower_case_table_names=0 and changed to lower_case_table_names=1, the database cannot be queried.

Start by setting lower_case_table_names=0

CREATE TABLE `Student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
  PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8;

show tables;
+----------------+
| Tables_in_aflyun |
+----------------+
| Student           |
+----------------+
Copy the code

If lower_case_table_names=1 and the query is executed, the table does not exist, regardless of whether the table name is uppercase or lowercase.

mysql> select * from Student;
1146 - Table 'aflyun.Student' doesn't exist

mysql> select * from student;
1146 - Table 'aflyun.student' doesn't exist
Copy the code

Solution: If you want to change the default value of lower_case_tables_name to 1, convert the existing database table name to lowercase.

If only the table names contain uppercase letters: 1. If lower_case_tables_name is 0, rename the table to lowercase. 2. Set lower_case_tables_name to 1.

If the database name contains uppercase letters: 1. If lower_case_tables_name=0, run mysqldump to export the database and delete the old database. 2. Set lower_case_tables_name to 1. 3, import data into the instance, the library name containing uppercase letters has been converted to lowercase.

03 summary

With the experience of stepping on the pit, to the beginning said ali Mysql protocol understanding more in-depth. Different operating systems cause different case sensitivity. When we are developing, we should develop according to the principle of case sensitivity, so that the program can be compatible with different operating systems. Therefore, you are advised to set the value of lower_case_table_names to 0 in the development test environment to strictly control the case sensitivity of code during development and improve the compatibility and rigor of code.

04 References

MySQL is case-sensitive lower_case_table_names & lower_case_file_system

---

Thank you for reading, if you think this blog is helpful to you, please like or like, let more people see! I wish you happy every day!

---

No matter what you do, as long as you stick to it, you will see the difference! On the road, neither humble nor pushy!

I wish you and I can become the best of themselves on the way of life, to become an independent person

© Alfayun who is getting better every day