MySQL case sensitive issues and naming conventions

Note: The main content of this article is referenced from Internet related articles

1.MySQL Case Sensitive Rules

In MySQL, a library will correspond to a folder, the library of the table will be stored in the folder in the file, so the sensitivity of the operating system to the case of the database and the table case sensitive decision-making (MySQL has a read-only system variable Lower_case_file_system, Its value reflects exactly whether the current file system is case-sensitive, and therefore: MySQL database and table names under Windows are case-insensitive, and are case-sensitive in most types of UNIX systems.

The following is a detailed case-sensitive rule for MySQL:

Under Linux:

1, database name and table name are strictly case-sensitive;
2, the table alias is strictly case-sensitive;
3, the column name and the alias of the column in all cases are ignored case;
4, variable names are also strictly case-sensitive;

Under Windows:

All are case insensitive

Add:

1.MySQL has a system variable: Lower_case_table_names, which is specifically configured to differentiate between the case of a library name and a table name. If your system is windows and you want MySQL to be case sensitive, then you can add the following in the My.ini file, at the end of the [mysqld] section:

[plain] view plain copy  

2. #If set to 0, table names is stored as specified and comparisons is case sensitive.
4. #If set to 1, table names is stored in lowercase on disk and comparisons is not case sensitive.
6. #If set to 2, table names is stored as given but compared in lowercase. This option also applies to database names and table aliases.
8. Lower_case_table_names=0

2.MySQL is case insensitive when querying strings. If a field value needs to be case-sensitive, you can specify the binary property when the field is defined.

2. A MySQL naming convention

In order to avoid case-based problems, a recommended naming convention is to define the database, tables, and columns with all lowercase letters and underscores, without using any uppercase letters.

MySQL case sensitive issues and naming conventions