Notes on MySQL case sensitivity

For this reason, Alibaba's regulations require:

[Mandatory] Table names and field names must use lowercase letters or numbers. They must not start with numbers or have only numbers between two underscores. It is very costly to modify the database field names, and since pre-release is not possible, the field names need to be carefully considered.

Therefore, no uppercase letters are allowed in database names, table names, and field names to avoid unnecessary trouble.

MySQL case sensitivity is controlled by parameters

There are two parameters related to MySQL case-sensitivity configuration, lower_case_file_system and lower_case_table_names.

To view the current case-sensitive configuration of MySQL, you can use the following statement

show global variables like '%lower_case%';

+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | ON |
| lower_case_table_names | 0 |
+------------------------+-------+

The parameters are described as follows:

1.lower_case_file_system, indicating whether the current system file is case sensitive, is a read-only parameter and cannot be modified. ON is case-insensitive, OFF is case-sensitive.

• This variable describes whether the file directory of the operating system where the data is located is case sensitive. OFF means file names are case sensitive, ON means they are case insensitive. This variable is read-only, and because it reflects a property of the file system, setting it has no effect on the file system.
  

2.lower_case_table_names, indicates whether the table name is case sensitive, which can be modified. There are three parameters: 0, 1, and 2.

• 0 Case sensitivity. (Unix, Linux default) The created library table will be saved as is on disk. For example, create database TeSt; will create a TeSt directory, create table AbCCC ... will generate the AbCCC.frm file as is, and the SQL statement will also be parsed as is.
• 1 Case insensitive. (Windows default) When creating a library table, MySQL converts all library table names to lowercase and stores them on disk. SQL statements will also convert the library and table names to lowercase. If you need to query the previously created Testtable (generate the Testtable.frm file), even if you execute select * from Testtable, it will be converted into select * from testtable, causing the error table to not exist.
• 2 Case insensitive (OS X default) The created library table will be saved as is on the disk, but the SQL statement will convert the library table name to lowercase.

How to set MySQL case sensitivity

Modify the my.cnf file in Linux system and the my.ini file in Windows system, and add or modify the following content.

lower_case_table_names = 0 or lower_case_table_names = 1

Then restart the MySQL service for the changes to take effect.

Development Notes

• If you want to change lower_case_table_names from 0 (sensitive) to 1 (insensitive), you must first process the table names of the old data tables, change the table names of all databases to lowercase, and then set lower_case_table_names to 1. Otherwise, the problem of not being able to find the table name will still occur.
• The default value of lower_case_table_names is 1 (insensitivity) on Windows and 2 (insensitivity) on macOS. On Linux, a value of 2 is not supported and the server forces the value to 0 (sensitive).
• And MySQL officials also remind that if the data directory resides on a case-insensitive file system (such as Windows or macOS), lower_case_table_names should not be set to 0.
• Otherwise, the MySQL service will fail to start.
  

Summarize

Due to the inconsistency of case-sensitivity default settings due to different operating systems, we must pay attention to it during development and develop a strict awareness that all SQL statements should use lowercase letters to avoid meaningless pitfalls.

The above are the details of the precautions for MySQL case sensitivity. For more information about MySQL case sensitivity, please pay attention to other related articles on 123WORDPRESS.COM!