Home > Article > Backend Development > Interpretation of mysql case-sensitive configuration issues
Two parameters related to mysql case-sensitive configuration, lower_case_file_system and lower_case_table_names.
View the current mysql case-sensitive configuration
show global variables like '%lower_case%';+------------------------+-------+| Variable_name | Value | +------------------------+-------+| lower_case_file_system | ON | | lower_case_table_names | 0 |+------------------------+-------+
Indicates whether the current system file is case-sensitive, a read-only parameter and cannot be modified.
ON Case insensitive
OFF Case sensitive
lower_case_table_names
Indicates whether the table name is case-sensitive and can be modified.
lower_case_table_names = 0, mysql will operate directly based on the table name and is case sensitive.
lower_case_table_names = 1, mysql will first convert the table name to lowercase before performing the operation.
Set the value of lower_case_table_names
Open the my.cnf file, add the following statements and restart.
lower_case_table_names = 0 或 lower_case_table_names = 1
Create table user
CREATE TABLE `user` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The table name is consistent with the case when it was created
select count(*) from user; +----------+| count(*) | +----------+| 0 | +----------+
The table name is inconsistent with the case when it was created
select count(*) from User;ERROR 1146 (42S02): Table 'user.User' doesn't exist
When lower_case_table_names=0, table names are case-sensitive.
select count(*) from user; +----------+| count(*) | +----------+| 0 | +----------+The table name is inconsistent with the case when it was created
select count(*) from user; +----------+| count(*) | +----------+| 0 | +----------+
When lower_case_table_names=1, the table name is not case sensitive.
lower_case_table_names=0, use mixed case to create a table name, and then set lower_case_table_names=1, the originally created table will be prompted not to exist when used.
lower_case_table_names=0
Create table User (mixed case)CREATE TABLE `User` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;show tables; +----------------+| Tables_in_user | +----------------+| User | +----------------+Then set
lower_case_table_names=1
When executing the query, no matter whether the table name is uppercase or lowercase, it will prompt that the table does not existselect * 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 when lower_case_table_names=1, the table name will be converted to lowercase before operation, and there is no lowercase table name file in the file, so an error occurs.
Summary: Different operating systems lead to inconsistent case sensitivity. When we develop, we should develop according to the principle of case sensitivity, 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 and testing environment, so as to strictly control the case sensitivity of the code during development and improve the compatibility and rigor of the code.
How to use php to merge arrays and retain key values
How to use phpcurl to implement multiple processes Download file class
#How to determine whether local and remote files exist through php
The above is the detailed content of Interpretation of mysql case-sensitive configuration issues. For more information, please follow other related articles on the PHP Chinese website!