MySQL Case sensitivity: lower_case_table_names & amp; lower_case_file_system

MySQL Case sensitivity: lower_case_table_names & lower_case_file_system

MySQL Case sensitivity: lower_case_table_names & lower_case_file_system

〇 lower_case_file_system
This parameter is used to describe whether the operating system of the data directory is case sensitive. this parameter is of the bool type, but cannot be modified.
0 -- case sensitive
1 -- case insensitive

For example, all running on linux is OFF or 0.

〇 lower_case_table_names
This parameter is static and can be set to 0, 1, or 2.

0 -- case sensitive. (Unix, Linux default)
The created database and table are stored on the disk as they are. For example, create database TeSt; a directory for TeSt will be created. create table AbCCC... will generate AbCCC. frm as is.
The SQL statement is parsed as is.

1 -- case insensitive. (Windows Default)
When creating a database table, MySQL converts all database table names to lowercase and stores them on the disk.
The SQL statement also converts the database table name to lowercase.
If you need to query the previously created Test_table (generate the Test_table.frm file), even if you execute select * from Test_table, it will be converted to select * from test_table, causing the error table to not exist.

2 -- Case Insensitive (OS X default)
The created database and table are stored on the disk as they are.
However, the SQL statement converts the database table name to lowercase.

Common adverse effects caused by lower_case_table_names modification:
If you create a database table with uppercase letters when lower_case_table_names = 0 and change it to lower_case_table_names = 1, it cannot be found.

Note:
Set the default lower_case_tables_name to 0 to 1. convert the existing database and table names to lowercase:

1) if only the table name has uppercase letters:
① When lower_case_tables_name = 0, execute rename table to lowercase.
② Set lower_case_tables_name = 1, and the restart takes effect.

2) There are uppercase letters in the Database Name:
① When lower_case_tables_name = 0, use mysqldump to export and delete the old database.
② Set lower_case_tables_name = 1, and the restart takes effect.
③ Import data to the instance. The database name containing uppercase letters has been converted to lowercase.

The conversion operation needs to be tested by yourself. Different MySQL versions may vary depending on different operating systems.