mysql operation and maintenance-lower_case_table_names

1 Introduction
In MySQL, the database corresponds to the directory in the data directory. Each table in the database corresponds to at least one file in the database directory (maybe multiple, depending on the storage engine). Therefore, the case sensitivity of the operating system used determines the case sensitivity of database and table names.
In most Unix database names and table names are case-sensitive, but in Windows they are not case-sensitive. One notable exception is Mac OS X, which is based on Unix but uses the default file system type (HFS+), which is case-insensitive. However, Mac OS X also supports UFS volumes, which are case-sensitive, just like Unix.
The variable lower_case_file_system indicates whether the file system where the data directory is located is sensitive to the case of the file name. ON indicates that the file name is case-insensitive, OFF indicates that it is case-sensitive.

For example, view it under windows:

It means that the windows system is not case sensitive, and mysql is also set to be case insensitive by default.

2 Case sensitivity rules
Under Linux:
Database names and table names are strictly case-sensitive;
Table aliases Is strictly case-sensitive;
Column names and column aliases are case-insensitive in all cases;
Variable names are also strictly case-sensitive;
Under Windows:
Both Not case sensitive
Under Mac OS (non-UFS volume):
Not case sensitive

3 Parameter description (lower_case_table_names)
The default value of lower_case_table_names under unix is ​​0. The default value under Windows is 1. The default value under Mac OS X is 2.

##0Save the table on the hard disk using the uppercase and lowercase letters specified in the CREATE TABLE or CREATE DATABASE statement name and database name. Name comparisons are case-sensitive. We cannot set this parameter to 0 on case-insensitive operating systems such as Windows or Mac OS Accessing MyISAM table names in upper and lower case may cause index corruption. 1Table names are saved in lowercase on the hard disk, and name comparisons are not case-sensitive. MySQL converts all table names to lowercase on storage and lookup tables. This behavior also applies to database names and table aliases. This value is the Windows default. 2Table names and database names are saved on the hard disk using the uppercase and lowercase letters specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase during the lookup on the table. Name comparison is not case-sensitive, that is, it is saved according to upper and lower case and compared according to lower case. Note: Only applicable on case-insensitive file systems! InnoDB table names are saved in lowercase.

	Explanation

4 Convert from case-sensitive to insensitive method If the originally created libraries and tables are all If you want to convert it to case-insensitive, you need to perform the following three steps:
1. Export the database data through mysqldump.
2. Change lower_case_tables_name = 1 in my.cnf and restart the mysql database.
3. Import the exported data into the mysql database.

5 Notes In order to avoid problems caused by case, a recommended naming rule is: when defining database, table, column When using all lowercase letters and underlines, do not use any uppercase letters
You can use lower_case_tables_name=1 in any system. The disadvantage of using this option is that when using SHOW TABLES or SHOW DATABASES, it is not possible to see whether the name was originally in uppercase or lowercase.
Please note that in Unix, if lower_case_tables_name = 0 before setting lower_case_tables_name to 1, the old database name and table name must be converted to lowercase before restarting mysqld.

Related recommendations:

Add my.ini in Mysql, lower_case_table_names = 0, you can make the table's _MySQL

The above is the detailed content of mysql operation and maintenance-lower_case_table_names. For more information, please follow other related articles on the PHP Chinese website!