Mysql collation case sensitivity settings

Mysql's collation case-sensitive setting mysql databases are sometimes case-sensitive and sometimes not true when performing queries, mainly because of the setting of mysql character verification rules. Usually, it is not supported by default. After actively setting collation for the mysql database, the database can meet the case sensitivity requirements and is suitable for customers. Learn through the following experiment ..... SQL code mysql> select version (); + ----------- + | version () | + ----------- + | 5.5.25 | + ----------- + 1 row in set (0.00 sec) SQL code mysql> show variables like '% character %'; + ------------------------ + bytes | Variable_name | Value + -------------------------- + bytes | character_set_clie Nt | utf8 | character_set_connection | utf8 | character_set_database | latin1 | bytes | binary | bytes | utf8 | character_set_server | latin1 | character_set_system | utf8 | character_sets_dir | D: \ database \ mysql \ mysql-5.5.25-winx64 \ share \ charsets \ SQL code mysql> show variables like '% collation %'; + ---------------------- + ------------------- + | Variable_name | Value | + Rows + | collation_connection | utf8_general_ci | collation_database | latin1_swedish_ci | collation_server | latin1_swedish_ci | + rows + 3 rows in set (0.00 sec) default: collation_connection = utf8_general_ci case-insensitive validation rules; SQL code mysql> show collation like '% utf8 %'; + ------------------------ + --------- + ----- + --------- + -- -------- + --------- + | Collation | Charset | Id | Default | Compiled | Sortlen | + bytes + --------- + ----- + --------- + ---------- + --------- + | utf8_general_ci | utf8 | 33 | Yes | 1 | utf8_bin | utf8 | 83 | Yes | 1 | utf8_unicode_ci | utf8 | 192 | Yes | 8 | utf8_icelandic_ci | utf8 | 193 | Yes | 8 | Client character Set: utf8, verification rule: utf8_general_ci. The default value is yes, that is, it is not a case-sensitive match; and utf8_bin is a zone Case-sensitive validation rules; create a table for testing and check data results: SQL code mysql> create table T_collation (first varchar (30) character set utf8-> collate utf8_bin, second varchar (30) character set utf8 collate-> utf8_general_ci); Query OK, 0 rows affected (0.32 sec) mysql> show create table t_collation \ G; * *************************** 1. row *************************** Table: t_collation Create Table: create table 't_ collation '('first' var Char (30) character set utf8 COLLATE utf8_bin default null, 'second' varchar (30) character set utf8 default null) ENGINE = InnoDB default charset = latin1 1 row in set (0.00 sec) ERROR: No query specified mysql> insert into t_collation values ('M', 'M'), ('n', 'n'), ('A ', 'A'), ('B', 'B'); Query OK, 4 rows affected (0.13 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from t_collation; + ------- + ---- ---- + | First | second | + ------- + -------- + | M | N | a | B | + ------- + -------- + 4 rows in set (0.00 sec) compare Query results: SQL code mysql> insert into t_collation values ('M', 'M'), ('n', 'n'); Query OK, 2 rows affected (0.10 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t_collation; + ------- + -------- + | first | second | + ------- + -------- + | M | N | a | B | B | m | n | + ------- + -------- + 6 rows in set (0.00 sec) mysql> select * from t_collation where first = 'M '; + ------- + -------- + | first | second | + ------- + -------- + | m | + ------- + -------- + 1 row in set (0.02 sec) mysql> select * from t_collation where second = 'M '; + ------- + -------- + | first | second | + ------- + -------- + | M | m | + ------- + -------- + 2 rows in set (0.00 sec) Mysql> select * from t_collation where second = 'M '; + ------- + -------- + | first | second | + ------- + -------- + | M | m | + ------- + -------- + 2 rows in set (0.00 sec) mysql> select * from t_collation where first = 'M '; + ------- + -------- + | first | second | + ------- + -------- + | M | + ------- + -------- + 1 row in set (0.00 sec) compare their verification rules, utf8_bin is case-sensitive, while utf8_general_ci is case-insensitive. The default value is. You can also compare the results from the sort statement to see the test results ..... SQL code mysql> select * from t_collation; + ------- + -------- + | first | second | + ------- + -------- + | M | N | a | B | m | | n | + ------- + -------- + 6 rows in set (0.00 sec) mysql> select * from t_collation order by first; + ------- + -------- + | first | second | + ------- + -------- + | M | N | a | B | m | | n | + ----- -- + -------- + 6 rows in set (0.00 sec) mysql> select * from t_collation order by second; + ------- + -------- + | first | second | + ------- + -------- + | a | B | M | m | N | | n | + ------- + -------- + 6 rows in set (0.00 sec) checks that comply with the verification rules. Conclusion: In the MYSQL database, you can adjust the collation validation rules of character sets as appropriate based on actual business needs to modify the default case sensitivity problem to meet actual needs, this is a kind of database settings, familiar with standards and rules, suitable for use by the project, you can set specific databases, tables, or table columns.