Interpreting MySQL case sensitive configuration issues

1.mysql Case Sensitive Configuration

MySQL case sensitive configuration related to two parameters,Lower_case_file_system and lower_case_table_names.

View the current case-sensitive configuration of MySQL

Show global variables like '%lower_case% '; +------------------------+-------+| Variable_name          | Value |+------------------------+-------+| Lower_case_file_system | On    | | lower_case_table_names | 0     |+------------------------+-------+

Lower_case_file_system

Indicates whether the current system file is case-sensitive, read-only, and cannot be modified.

on case insensitive
OFF Case Sensitive
lower_case_table_names

Indicates if the table name is case-sensitive and can be modified.

Lower_case_table_names = 0 o'clock, MySQL operates directly on the table name and is case-sensitive.
Lower_case_table_names = 1 o'clock, MySQL will first convert the table name to lowercase, and then perform the operation.

Set the value of the Lower_case_table_names

Open the My.cnf file, and then restart it by adding the following statement.

Lower_case_table_names = 0 or lower_case_table_names = 1

2. Test lower_case_table_names for different conditions of 0 and 1 o'clock

CREATE TABLE User

CREATE TABLE ' user ' (  ' id ' int (one) unsigned not null auto_increment,  ' name ' varchar (a) NOT NULL,  PRIMARY KEY (' ID ')) Engine=innodb DEFAULT Charset=utf8;

1. Set lower_case_table_names = 0

Table names are consistent with case creation

Select COUNT (*) from user;+----------+| COUNT (*) |+----------+|        0 |+----------+

Table name does not match case when created

Select COUNT (*) from User; ERROR 1146 (42S02): Table ' user. User ' doesn ' t exist

when lower_case_table_names=0, the table name is case sensitive.

2. Set Lower_case_table_names = 1

Table names are consistent with case creation

Select COUNT (*) from user;+----------+| COUNT (*) |+----------+|        0 |+----------+

Table name does not match case when created

Select COUNT (*) from user;+----------+| COUNT (*) |+----------+|        0 |+----------+

when Lower_case_table_names=1, the table name is not case sensitive.

3. When setting Lower_case_table_names=1, the table hint that was originally created at lower_case_table_names=0 does not exist workaround

When lower_case_table_names=0 is used to create a table name using case blending, and after setting Lower_case_table_names=1 , the original created table will not be prompted for use.

Demonstrate

Set lower_case_table_names=0 First

CREATE TABLE User (mixed case)

CREATE TABLE ' User ' (  ' id ' int (one) unsigned not null auto_increment,  ' name ' varchar (a) NOT NULL,  PRIMARY KEY (' ID ')) Engine=innodb DEFAULT charset=utf8;show tables;+----------------+| Tables_in_user |+----------------+| User           |+----------------+

Set Lower_case_table_names=1 again

Executes a query that indicates that the table does not exist, regardless of whether the table name is uppercase or lowercase

SELECT * from User; ERROR 1146 (42S02): Table ' user.user ' doesn ' t existselect * from user; ERROR 1146 (42S02): Table ' user.user ' doesn ' t existselect * from user; ERROR 1146 (42S02): Table ' user.user ' doesn ' t exist

because Lower_case_table_names=1, the table name is converted to lowercase before operation, and the file does not have a lowercase table name file, so there is an error.

Workaround:

If you want to change lower_case_table_names from 0 to 1 o'clock, you should first handle the table name of the old data table, change the table name of all databases to lowercase first, and then set Lower_case_table_names to 1, otherwise the above problem will occur.

Summary:
Different operating systems cause case sensitivity inconsistencies. When we develop it, we should develop it according to the principle of case-sensitive, so that the developed program can be compatible with different operating systems. Therefore, it is recommended to set the value of Lower_case_table_names to 0 in the development test environment, which is easy to control the code case sensitivity and improve the compatibility and preciseness of code.

This article explains the MySQL case sensitive configuration problem, more relevant content please pay attention to the PHP Chinese web.