Shulou(Shulou.com)05/31 Report--

This article mainly introduces the meaning of lower_case_table_names in mysql operation and maintenance, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article. Let's take a look at it.

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 (or multiple, depending on the storage engine). Therefore, the case sensitivity of the operating system used determines the case sensitivity of database and table names.

Database and table names are case-sensitive in most Unix, but not case-sensitive in Windows. One notable exception is Mac OS X, which is based on Unix but uses the default file system type (HFS+) and 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 resides is case-sensitive. ON indicates that it is not sensitive to the case of file names, while OFF indicates that it is sensitive.

For example, view under windows:

This indicates that the windows system is case-insensitive, and mysql is set to be case-insensitive by default.

2 case-sensitive rules

Under linux:

Database names and table names are strictly case-sensitive

Aliases for tables are strictly case sensitive

Column names and column aliases are case ignored in all cases

Variable names are also strictly case-sensitive

Under windows:

Are not case-sensitive.

Under Mac OS (non-UFS volume):

Are 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 of Windows is 1. The default value of Mac OS X is 2. 0.

Parameter value

Explain 0 saves the table name and database name on the hard disk using the uppercase and lowercase letters specified by the CREATE TABLE or CREATE DATABASE statement. Names are case sensitive. We cannot set this parameter to 0 on case-insensitive operating systems such as windows or Mac OS x. If you force-- lowercase-table-names to 0 on a case-insensitive file system, and access the MyISAM table name with different case, it may cause index corruption. The table name is saved in lowercase on the hard disk, and the name is relatively insensitive to case. 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 default value for Windows. 2 Table and database names are saved on the hard disk using the uppercase and lowercase letters specified by the CREATE TABLE or CREATE DATABASE statements, but MySQL converts them to lowercase on the lookup table. Names are not case-sensitive, that is, they are saved by case and compared by lowercase. Note: applies only to case-insensitive file systems! Innodb table names are saved in lowercase.

4 conversion from case-sensitive to insensitive methods

If the originally created libraries and tables are case-sensitive, if you want to convert to case-insensitivity, you need to take the following three steps:

1. Export database data through mysqldump.

two。 Change lower_case_tables_name = 1 in my.cnf and restart the mysql database.

3. Import the exported data into the mysql database.

5 points for attention

To avoid uppercase and lowercase problems, a recommended naming rule is to define databases, tables, and columns with lowercase letters and underscores, without any uppercase letters.

Lower_case_tables_name=1 can be used on any system. The disadvantage of using this option is that when using SHOW TABLES or SHOW DATABASES, you can't tell whether the name was originally in uppercase or lowercase.

Note that in Unix, before setting lower_case_tables_name to 1 before lower_case_tables_name = 0, the old database and table names must be converted to lowercase before restarting mysqld.

Thank you for reading this article carefully. I hope the article "what is the meaning of lower_case_table_names in mysql operation and maintenance" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.