MySQL table name case-sensitive problem under Linux system

The problem is as follows:

For example, the person table in the query SQL statement if the table name is lowercase, it will be reported that the people table does not exist.

Because the table name of the MySQL database in the Linux environment is case-sensitive by default, you can view the MySQL configuration file/etc/my.cnf on Linux:

[[email Protected]_219_131_centos tomcat7]# Cat/etc/my.cnf[mysqld]DataDir=/var/Lib/Mysqlsocket=/var/Lib/Mysql/Mysql.sockUser=mysql# Disabling Symbolic-Links isRecommended toPrevent assorted security riskssymbolic-Links=0[Mysqld_safe]Log-Error=/var/Log/Mysqld.LogPID-file=/var/Run/Mysqld/Mysqld.pid[[email Protected]_219_131_centos tomcat7]#

If you want to use database table names that are not case-sensitive, you need to add a line of configuration under [Mysqld], which is Lower_case_table_names=1:

[[email Protected]_219_131_centos tomcat7]# VI/etc/my.cnf[mysqld]Lower_case_table_names=1DataDir=/var/Lib/Mysqlsocket=/var/Lib/Mysql/Mysql.sockUser=mysql# Disabling Symbolic-Links isRecommended toPrevent assorted security riskssymbolic-Links=0[Mysqld_safe]Log-Error=/var/Log/Mysqld.LogPID-file=/var/Run/Mysqld/Mysqld.pid

Description of the configuration on the Web:

The lower_case_table_names parameter is detailed:0: Case Sensitive,1: Case-insensitive MySQL under Linux database name, table name, column name, alias casing rules are like this:1, database name and table name are strictly case-sensitive;2, the alias of the table is strictly case-sensitive;3, the name of the column and the alias of the column are ignored in all cases;4, variable names are also strictly case-sensitive; MySQL is not case-sensitive under Windows. 3, if you want to distinguish the case of a field value at query time: The field value needs to be set to the binary property, and it can be set in several ways: A, set at creation time:CREATE TABLET (AVARCHAR(Ten)BINARY); B. Modify with Alter:ALTER TABLE' TableName ' MODIFYCOLUMN' Cloname 'VARCHAR( $)BINARY; C, the MySQL tableeditor directly tick binary items. 

After modifying the configuration, be sure to restart the database:

[email protected]_219_131_centos tomcat7] # service Mysqld restartstopping mysqld:   [  OK  ]starting mysqld:  [  OK  ]

Then use the Navicat tool to reconnect to the MySQL database, query the Perosn table again, and found that no matter whether the table name in the query SQL statement is uppercase or lowercase, you are prompted to find the person table.

This is due to the fact that modifying the configuration will cause the original uppercase table name person to be unrecognized.

The solution is:

(1) Before modifying the configuration, the table structure and table data of all tables must be exported for backup;

(2) Delete the original table;

(3) Modify the configuration;

(4) Import the table structure and table data.

After following the steps above, query the person table again, and the table name is not case-sensitive.

MySQL table name case-sensitive problem under Linux system