MySQL is case sensitive

MySQL is case insensitive on Windows, but case sensitive by default on Linux. If you do not pay attention to a little will appear in the local development of the program running everything normal, published to the server line table name can not find the problem, confused.

For this reason, it is required in the Alibaba.com Statute that:

[Mandatory] The table name and field name must contain lowercase letters or digits. Do not start with a digit or contain only digits between two underscores (_). Database field names can be expensive to change because they cannot be pre-published, so they need to be considered carefully.

Therefore, do not allow any uppercase letters in database names, table names, and field names to avoid unnecessary trouble.

MySQL case sensitivity is controlled by parameters

Mysql case sensitive configuration of two parameters, lower_case_file_system and lower_case_table_names.

To check the current mysql case-sensitive configuration, use the following statement

show global variables like '%lower_case%';

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 0     |
+------------------------+-------+
Copy the code

The parameters are described as follows:

  1. Lower_case_file_system: indicates whether the current system file is case sensitive. The parameter is read-only and cannot be changed. ON is case-insensitive, and OFF is case-sensitive.

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

  2. Lower_case_table_names: indicates whether the table name is case-sensitive and can be changed. The value can be 0, 1, or 2.

    • 0 is case-sensitive. (Unix, Linux default) The created library tables are saved as is on disk. Such as the create database TeSt; Create table AbCCC… The ABCCC. FRM file will be generated as is, and the SQL statement will be parsed as is.

    • 1 case insensitive. MySQL converts all library table names to lowercase and stores them on disk when creating library tables (default on Windows). The SQL statement also converts the library table name to lowercase. If you need to query the Testtable that has been created before (the testtable. FRM file is generated), even if you run the select * from Testtable command, the command will be converted to select * from Testtable. As a result, the error table does not exist.

    • 2 Case Insensitive (default in OS X) Library tables created are saved on disk as is, but the SQL statement converts the library table names to lowercase.

MySQL is case-sensitive

In Linux, modify the my. CNF file. In Windows, modify the my.ini file.

Lower_case_table_names = 0 or lower_case_table_names = 1Copy the code

Then restart the MySQL service to take effect.

Iv. Matters needing attention in development

  1. If you want to change lower_case_table_names from 0 (sensitive) to 1 (insensitive), you must first change the name of the old table to lowercase for all database tables and then set lower_case_table_names to 1. Otherwise, the table name cannot be found.
  2. Lower_case_table_names defaults to 1 (insensitive) on Windows and 2 (insensitive) on macOS. The value 2 is not supported on Linux, and the server enforces a value of 0 (sensitive).
  3. If the data directory resides on a case-insensitive file system (such as Windows or macOS), you should not set lower_case_table_names to 0.
  4. Otherwise, the MySQL service cannot be started.

Five, the summary

Due to different operating systems lead to different default Settings for case sensitivity, we must pay attention to the development, should develop a strict awareness of the USE of lowercase SQL statements, to avoid meaningless tram pits.