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

Editor to share with you how to solve the problem caused by MySQL case sensitivity, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

MYSQL is case sensitive

I have read Alibaba's Java development manual before, and I saw it in the MySql table-building specification:

[mandatory] Table names and field names must use lowercase letters or numbers, the beginning of numbers is prohibited, and only numbers are prohibited between the two underscores. The modification of the database field name is expensive because it cannot be pre-published, so the field name needs to be carefully considered.

Note: MySQL is not case-sensitive under Windows, but it is case-sensitive by default under Linux. Therefore, database names, table names, and field names are not allowed to appear in uppercase letters.

Positive example: aliyun _ admin, rdc _ config, level 3 _ name counterexample: AliyunAdmin, rdcConfig, level 3 name

If you have not really encountered similar problems, sometimes dry look at these protocols do not understand, it seems to understand, and just memorize it.

01 an alphabet size story

Recently, I have been working on a project, and there has been no problem in the process of development and testing on my own machine, but after being deployed to the Linux server, I found that there was an error. 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. There's a ghost... But don't panic. Looking at the error message, it is obvious that the tb_sutyHo table does not exist!

① so I unhurriedly opened nv (navicat) to see if the table was there, and I saw the tb_sutyho shown in the database, but h is lowercase.

② looked at the code and found that the table name was actually written as tb_sutyHo, and an h was written in uppercase H.

The problem was found. It turned out that the name of the table was not written correctly when I accidentally wrote SQL. It was done by changing the name of the table, and everything was normal. Normally, this is the end of the story? The problem has been found and fixed, everything is all right, and we can eat chicken later.

For me, who can't play chicken eating, it's not over here. It's really important to find and solve the problem, but it's more important to find the root cause of the problem, so that you can avoid such problems next time. As a programmer, don't fall into the same hole twice.

I wonder why the local Window environment doesn't have this error message all the time. I have to wait for me to deploy the server before it shows up. What's the problem? If you are sensitive to Mysql size, you can skip the following. .)

So using the search engine, it is found that the case sensitivity of database and table names in Mysql is controlled by the parameter lower_case_table_names.

View it in the native Window environment as follows:

Mysql > show variables like'% case%' +-+-+ | Variable_name | Value | +-+-+ | lower_case_file_system | ON | | lower_case_table_names | 1 | +-+-+

View it on the Linux server as follows:

Mysql > show variables like'% case%' +-+-+ | Variable_name | Value | +-+-+ | lower_case_file_system | OFF | | lower_case_table_names | 0 | +-+-+

We can see the difference from the above results, but I have no feeling about these two parameters, and I don't know exactly what they mean.

Lowercasefile_system

This variable describes the case sensitivity of file names on the file system where the data directory resides. OFF indicates that filenames are case-sensitive, and ON indicates that they are not case-sensitive. This variable is read-only because it reflects the file system property and sets it to have no effect on the file system.

Lowercasetable_names

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

0-case sensitive. (Unix,Linux default) Library tables created will be saved as is on disk. For example, create database TeSt; will create a TeSt directory, create table AbCCC. AbCCC.frm will be generated as is. The SQL statement is also parsed as is.

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

2-Library tables created by case-insensitive (OS X default) are saved as is on disk. However, 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 value is 2. A value of 2 is not supported on Linux; the server enforces it to be 0.

And the official website also notes that if you run MySQL on a system where the data directory resides on a case-insensitive file system, such as Windows or macOS, you should not set lowercasetable_names to 0.

When I tried to set lower_case_table_names to 0 in my window10 environment, the MySQL service failed to start, and the startup service reported an error. The windows system is case-insensitive, as shown below:

Note: if you want to modify the value of lower_case_table_names, modify my.ini under windows and my.cnf configuration file under Linux, you need to restart the service. You can find information on the Internet for specific operations.

02 points for attention

Common hidden dangers caused by modifying lowercasetable_names: if you create a library table with uppercase letters during lower_case_table_names=0 and change it to lower_case_table_names=1, it will not be found.

First set up 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 | +-+

Then set the lower_case_table_names=1 and execute the query, regardless of whether the table name is uppercase or lowercase, indicating that the table does not exist.

Mysql > select * from Student;1146-Table 'aflyun.Student' doesn't existmysql > select * from student;1146-Table' aflyun.student' doesn't exist

Solution: if you want to set the default lower_case_tables_name to 0 to 1, you need to convert the existing library table name to lowercase first.

For cases where only uppercase letters exist in the table name:

When ① or lower_case_tables_name=0, the rename table is executed in lowercase.

②, set lower_case_tables_name=1, and restart takes effect.

For cases where there are uppercase letters in the library name:

When ①, lower_case_tables_name=0, use mysqldump to export and delete the old database.

②, set lower_case_tables_name=1, and restart takes effect.

③, import data to the instance, and the library name containing uppercase letters has been converted to lowercase.

The above is all the contents of this article entitled "how to solve the problem caused by MySQL case sensitivity". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.