MySQL Case Summary

Summary:

1. The database name is case-sensitive and cannot be configured with Parameters

2. The table name is case sensitive (lower_case_table_names can be configured with parameters), and the table alias is case sensitive.

3. The column name and column alias are not case sensitive.

4. Variable names are case sensitive and cannot be configured.

5. The execution directory is case sensitive and can be configured with parameters (lower_case_file_system)

6. By default, the names of the following table and the execution directory in windows are case-insensitive, while those in Unix and linux are case-sensitive.

7. The naming case sensitivity is not the same as the query case sensitivity in the field. The query case sensitivity in the field is specified by the character set or binary keyword. By default, the name is case insensitive.

The lower_case_table_names parameter affects the case sensitivity of table names. 0 indicates case sensitivity, and 1 indicates case insensitive.

The lower_case_file_system parameter affects the case sensitivity of various mysql directories. 0 indicates case insensitive and 1 indicates case insensitive.

Field case sensitivity

Affected by the character set validation character set, such as utf8_bin, utf8_general_ci, utf8_general_cs, and _ bin indicate that data is stored in binary format and case sensitive. ci indicates that data is not case sensitive, cs indicates case-sensitive (it seems that this verification character has been eliminated). The verification character set can be divided into four types: server layer, database layer, surface layer, and field layer (from high to low ), the real factor is the validation Character Set of the field layer. If it is not specified, it is inherited from the previous layer by default. For example, the database layer of the table inherited fields, the field layer inherited from the surface layer, and the database layer inherited from the server layer, the server layer needs to be set. If this parameter is not set, the default value is latin1_general_ci.

If the verification character of a field is utf8_bin, the fields A and a are different!

Example:

Create table t_bin (vr VARCHAR (20) not null primary key) COLLATE = utf8_bin; create table t_ci (vr VARCHAR (20) not null primary key) COLLATE = utf8_general_ci; insert into t_bin (vr) VALUES ('A'); insert into t_ci (vr) VALUES ('B '); insert into t_ci (vr) VALUES ('B'); 4 queries executed, 3 success, 1 errors, 0 warnings query: insert into t_bin (vr) values ('A ') 1 row affected execution time: 0 sec Transfer Time: 0 sec total time consumed: 0 sec ---------------------------------------------------- query: insert into t_bin (vr) VALUES ('A ') 1 row affected execution time: 0 sec Transfer Time: 0 sec total time consumed: 0 sec ---------------------------------------------------- query: insert into t_ci (vr) VALUES ('B ') 1 line affected execution time: 0 sec Transfer Time: 0 sec total time consumed: 0 sec ---------------------------------------------------- query: insert into t_ci (vr) VALUES ('B') error code: 1062 Duplicate entry 'B' for key 'primary' execution time: 0 sec Transfer Time: 0 sec total time consumed: 0 sec

We can see that t_bin (encoded as _ bin) is inserted, and Zone a are separated.

When t_ci (encoded as _ ci) is inserted, B and B cannot be distinguished. If the _ bin check encoding is not used for a field by default, you can use the binary keyword to specify the case sensitivity.

For example:

CREATE TABLE t_no(vr VARCHAR(20) NOT NULL )COLLATE=utf8_general_ci;INSERT INTO t_no (vr) VALUES ('a');INSERT INTO t_no (vr) VALUES ('A');INSERT INTO t_no (vr) VALUES ('b');INSERT INTO t_no (vr) VALUES ('B'); mysql> SELECT * FROM t_no WHERE vr='a';+----+| vr |+----+| a  || A  |+----+2 rows in set (0.00 sec)mysql> SELECT * FROM t_no WHERE binary vr='a';+----+| vr |+----+| a  |+----+1 row in set (0.00 sec)

Suggestion: Generally, it is recommended that the table name be case-insensitive. Set lower_case_table_names to 1.

This article from the "original" blog, please be sure to keep this source http://qdjalone.blog.51cto.com/1222376/1304539