Mysql collation case sensitivity settings

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.