Shulou(Shulou.com)06/01 Report--

In MySQL, a database corresponds to a directory in a data directory. Each table in the database corresponds to at least one file in the database directory (or more, depending on the storage engine). Therefore, the case sensitivity of the operating system used determines the case sensitivity of database names and table names.

Database names and table names are case-sensitive in most Unix, but case-insensitive in Windows. A notable exception is Mac OS X, which is Unix-based 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 on which the data directory resides is case-sensitive for file names. ON means case insensitive, OFF means case sensitive.

Mysql official documentation:

Value

Meaning

0

Table and database names are stored on disk using the lettercase specified in the CREATE TABLE orCREATE DATABASE statement. Name comparisons are case sensitive. You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or Mac OS X). If you force this variable to 0 with --lower-case-table-names=0 on a case-insensitive file system and access MyISAM tablenames using different lettercases, index corruption may result.

1

Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.

2

Table and database names are stored on disk using the lettercase specified in the CREATE TABLE orCREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1.

Default is 0, case sensitive.

Set 1, case insensitive. The tables and databases created are stored on disk in lowercase form, and sql statements are converted to lowercase to search tables and DB.

Set 2, the tables and DB created are stored according to the format on the statement, and all searches are converted to lowercase.

If you plan to set the lower_case_table_names system variable to 1 on Unix, you must first convert your old database and table names to lowercase before stopping mysqld and restarting it with the new variable setting. To do this for an individual table, use RENAME TABLE:

When you want to set lower_case_table_names = 1, you need to convert the original database and tables to lowercase before restarting the database instance.

Interpretation of official documents. To turn on lower_case_table_names = 1. First, change the upper-case database to lowercase. Otherwise the database name will not be found.

So if you want to ignore case, you need to change the upper case database name to lower case in advance, otherwise you will report an error. Database name cannot be renamed. You can create a new database name in lowercase.

Then rename the table to the new database to complete the migration of the table.

Before setting database parameters, you need to master the impact of this parameter, and you must not set it blindly.

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.