Interpretation of mysql case-sensitive configuration issues-PHP Tutorial-php.cn

1.mysql case-sensitive configuration

Two parameters related to mysql case-sensitive configuration,lower_case_file_systemandlower_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 |+------------------------+-------+

Copy after login

lower_case_file_system

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

ONCase insensitive
OFFCase 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

Copy after login

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;

Copy after login

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

Copy after login

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

Copy after login

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

Copy after login

The table name is inconsistent with the case when it was created

select count(*) from User; +----------+| count(*) | +----------+| 0 | +----------+

Copy after login

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

Copy after login

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

Copy after login

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!