mysql的collation区分大小写设置

mysql的collation区分大小写设置

 

      mysql数据库在做查询时候，有时候是英文字母大小写敏感的，有时候又不是的，主要是由于mysql的字符校验规则的设置。通常默认是不支持的大小写字母敏感的，在主动设置mysql数据库的collation后，可以使得数据库满足大小写敏感，适合客户的一定要求。通过下面的试验进行理解学习.....

 

Sql代码  

1. mysql> select version();  
2. +-----------+  
3. | version() |  
4. +-----------+  
5. | 5.5.25    |  
6. +-----------+  
7. 1 row in set (0.00 sec)  

  

    

Sql代码  

1. mysql> show variables like '%character%';  
2. +--------------------------+-------------------------------------------------------  
3. | Variable_name            | Value  
4. +--------------------------+-------------------------------------------------------  
5. | character_set_client     | utf8  
6. | character_set_connection | utf8  
7. | character_set_database   | latin1  
8. | character_set_filesystem | binary  
9. | character_set_results    | utf8  
10. | character_set_server     | latin1  
11. | character_set_system     | utf8  
12. | character_sets_dir       | D:\database\mysql\mysql-5.5.25-winx64\share\charsets\  

 

   

Sql代码  

1. mysql> show variables like '%collation%';  
2. +----------------------+-------------------+  
3. | Variable_name        | Value             |  
4. +----------------------+-------------------+  
5. | collation_connection | utf8_general_ci   |  
6. | collation_database   | latin1_swedish_ci |  
7. | collation_server     | latin1_swedish_ci |  
8. +----------------------+-------------------+  
9. 3 rows in set (0.00 sec)  

    默认即为：collation_connection = utf8_general_ci   大小写不敏感校验规则；

  

Sql代码  

1. mysql> show collation like '%utf8%';  
2. +--------------------------+---------+-----+---------+----------+---------+  
3. | Collation                | Charset | Id  | Default | Compiled | Sortlen |  
4. +--------------------------+---------+-----+---------+----------+---------+  
5. | utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 |  
6. | utf8_bin                 | utf8    |  83 |         | Yes      |       1 |  
7. | utf8_unicode_ci          | utf8    | 192 |         | Yes      |       8 |  
8. | utf8_icelandic_ci        | utf8    | 193 |         | Yes      |       8 |  

    客户端字符集：utf8, 校验规则: utf8_general_ci, 默认为yes，即不是大小写敏感的匹配；

    而utf8_bin是区分大小写的校验规则；

    创建表做测试，看数据效果：

 

   

Sql代码  

1. mysql> create table T_collation(first varchar(30) character set utf8  
2.     -> collate utf8_bin,second varchar(30) character set utf8 collate  
3.     -> utf8_general_ci);  
4. Query OK, 0 rows affected (0.32 sec)  
5.   
6. mysql> show create table t_collation\G;  
7. *************************** 1. row ***************************  
8.        Table: t_collation  
9. Create Table: CREATE TABLE `t_collation` (  
10.   `first` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,  
11.   `second` varchar(30) CHARACTER SET utf8 DEFAULT NULL  
12. ) ENGINE=InnoDB DEFAULT CHARSET=latin1  
13. 1 row in set (0.00 sec)  
14.   
15. ERROR:  
16. No query specified  
17.   
18. mysql> insert into t_collation values('M','M'),('N','N'),('a','a'),('b','b');  
19. Query OK, 4 rows affected (0.13 sec)  
20. Records: 4  Duplicates: 0  Warnings: 0  
21.   
22. mysql> select * from t_collation;  
23. +-------+--------+  
24. | first | second |  
25. +-------+--------+  
26. | M     | M      |  
27. | N     | N      |  
28. | a     | a      |  
29. | b     | b      |  
30. +-------+--------+  
31. 4 rows in set (0.00 sec)  

    比较查询结果：

   

Sql代码  

1. mysql> insert into t_collation values('m','m'),('n','n');  
2. Query OK, 2 rows affected (0.10 sec)  
3. Records: 2  Duplicates: 0  Warnings: 0  
4.   
5. mysql> select * from t_collation;  
6. +-------+--------+  
7. | first | second |  
8. +-------+--------+  
9. | M     | M      |  
10. | N     | N      |  
11. | a     | a      |  
12. | b     | b      |  
13. | m     | m      |  
14. | n     | n      |  
15. +-------+--------+  
16. 6 rows in set (0.00 sec)  
17.   
18. mysql> select * from t_collation where first='m';  
19. +-------+--------+  
20. | first | second |  
21. +-------+--------+  
22. | m     | m      |  
23. +-------+--------+  
24. 1 row in set (0.02 sec)  
25.   
26. mysql> select * from t_collation where second='m';  
27. +-------+--------+  
28. | first | second |  
29. +-------+--------+  
30. | M     | M      |  
31. | m     | m      |  
32. +-------+--------+  
33. 2 rows in set (0.00 sec)  
34. mysql> select * from t_collation where second='M';  
35. +-------+--------+  
36. | first | second |  
37. +-------+--------+  
38. | M     | M      |  
39. | m     | m      |  
40. +-------+--------+  
41. 2 rows in set (0.00 sec)  
42.   
43. mysql> select * from t_collation where first='M';  
44. +-------+--------+  
45. | first | second |  
46. +-------+--------+  
47. | M     | M      |  
48. +-------+--------+  
49. 1 row in set (0.00 sec)  

    比较各自的校验规则，utf8_bin是区分大小写的，而utf8_general_ci是不区分的，默认的。

    还可以从排序语句中进行比较，看看测试效果的.....

 

   

Sql代码  

1. mysql> select * from t_collation;  
2. +-------+--------+  
3. | first | second |  
4. +-------+--------+  
5. | M     | M      |  
6. | N     | N      |  
7. | a     | a      |  
8. | b     | b      |  
9. | m     | m      |  
10. | n     | n      |  
11. +-------+--------+  
12. 6 rows in set (0.00 sec)  
13.   
14. mysql> select * from t_collation order by first;  
15. +-------+--------+  
16. | first | second |  
17. +-------+--------+  
18. | M     | M      |  
19. | N     | N      |  
20. | a     | a      |  
21. | b     | b      |  
22. | m     | m      |  
23. | n     | n      |  
24. +-------+--------+  
25. 6 rows in set (0.00 sec)  
26.   
27. mysql> select * from t_collation order by second;  
28. +-------+--------+  
29. | first | second |  
30. +-------+--------+  
31. | a     | a      |  
32. | b     | b      |  
33. | M     | M      |  
34. | m     | m      |  
35. | N     | N      |  
36. | n     | n      |  
37. +-------+--------+  
38. 6 rows in set (0.00 sec)  

   同样符合校验规则的检查。

  结论： 在MYSQL数据库中，根据实际业务需要，适当可以调整字符集的(collation)校验规则，修改默认的大小写敏感问题，满足实际需要，这本身就是数据库的一种设置，熟悉标准、规则，适当利用为项目所用，可以针对具体的数据库或者表或者表的列进行设置。

from: http://xiaobo.iteye.com/blog/1830942