Interpretation of mysql case-sensitive configuration issues

1.mysql case-sensitive configuration

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     |+------------------------+-------+

lower_case_file_system

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

2. Test different situations when lower_case_table_names is 0 and 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;

1.Set lower_case_table_names = 0

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.

2. Set lower_case_table_names = 1

##The table name is in the same case as 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;
+----------+| count(*) |
+----------+|        0 |
+----------+

When lower_case_table_names=1, the table name is not case sensitive.

3. When setting lower_case_table_names=1, the original table created when lower_case_table_names=0 prompts that the table does not exist.

When

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.

Demonstration

First set

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 exist

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 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.

Solution:

If you want to modify lower_case_table_names from 0 to 1, you should first modify the old data table Process the table names, first change the table names of all databases to lowercase, and finally set lower_case_table_names to 1, otherwise the above problems will occur.

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.

This article explains the case-sensitive configuration issue of MySQL. For more related content, please pay attention to the PHP Chinese website.

Related recommendations:

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!