MySQL中查询的有关英文字母大小写问题的分析

这篇文章主要介绍了MySQL中查询的有关英文字母大小写问题的分析,作者从多个角度进行了实验,需要的朋友可以参考下

mysql数据库在做查询时候，有时候是英文字母大小写敏感的，有时候又不是的，主要是由mysql的字符校验规则的设置决定的，通常默认是不支持的大小写字母敏感的。

1. 什么是字符集和校验规则？

字符集是一套符号和编码。校对规则是在字符集内用于比较字符的一套规则。任何一个给定的字符集至少有一个校对规则，它可能有几个校对规则。要想列出一个字符集的校对规则，使用SHOW COLLATION语句。

校对规则一般有这些特征：

两个不同的字符集不能有相同的校对规则。

每个字符集有一个默认校对规则。例如，utf8默认校对规则是utf8_general_ci。

存在校对规则命名约定：它们以其相关的字符集名开始，通常包括一个语言名，并且以_ci(大小写不敏感)、_cs(大小写敏感)或_bin(二元)结束。

2. 不同级别的字符集和校验规则可控制大小写敏感

MySQL5.1在同一台服务器、同一个数据库或甚至在同一个表中使用不同字符集或校对规则来混合定义字符串。字符集和校对规则有4个级别的默认设置：服务器级、数据库级、表级和连接级。

2.1服务器级

MySQL按照如下方法确定服务器字符集和服务器校对规则：

(1)修改配置文件/etc/my.cnf

在[mysqld]下添加：collation_server = utf8_bin

重启实例

更改服务器级的校验规则(collation_server )后，数据库校验规则(collation_collation)默认会继承服务器级的。

注意：

这个只适用于在重新启动之后, 新建的库,已存在的库不受影响.

同样的, 即使库的校验规则改了,已经存在的表不受修改影响;

同理与已经存在的列...

1. mysql> create database yutest0; 
2. Query OK, 1 row affected (0.00 sec) 
3. mysql> use yutest0; 
4. Database changed 
5. mysql> create table t1 (name varchar(10)); 
6. Query OK, 0 rows affected (0.01 sec) 
7.  
8. mysql> insert into t1 values('AAA'); 
9. Query OK, 1 row affected (0.00 sec) 
10. mysql> insert into t1 values('aaa'); 
11. Query OK, 1 row affected (0.01 sec) 
12.  
13. mysql> select * from t1; 
14. +------+ 
15. | name | 
16. +------+ 
17. | AAA | 
18. | aaa | 
19. +------+ 
20. 2 rows in set (0.00 sec) 
21.  
22. mysql> select * from t1 where name='aaa'; 
23. +------+ 
24. | name | 
25. +------+ 
26. | aaa | 
27. +------+ 
28. 1 row in set (0.00 sec) 

可以看出，在服务器级进行相应的校对规则设置，查询大小写敏感。

(2)当服务器启动时根据有效的选项设置

当启动mysqld时，根据使用的初始选项设置来确定服务器字符集和校对规则。

shell> mysqld --character-set-server=latin1 --collation-server=latin1_swedish_ci

2.2数据库级

MySQL这样选择数据库字符集和数据库校对规则：

如果指定了character set X和collate Y，那么采用字符集X和校对规则Y。

如果指定了character set X而没有指定collate Y，那么采用character set X和character set X的默认校对规则。

否则，采用服务器字符集和服务器校对规则。

(1)修改配置文件/etc/my.cnf

进行了两组测试：

1) 在[mysqld]下添加：

1. collation_server = utf8_bin 
2.  
3. collation_database = utf8_bin 

2) 在[mysqld]下添加：

1. collation_database = utf8_bin 

重启实例，两组都不能正常启动，错误信息如下：

可见，my.cnf配置文件中不支持设置collation_database 变量。

(2)创建数据库时设置数据库校验规则

1. mysql> create database yutest default character set utf8 collate utf8_bin; 
2. Query OK, 1 row affected (0.00 sec) 
3. mysql> show variables like 'collation_%'; 
4. +----------------------+-----------------+ 
5. | Variable_name | Value | 
6. +----------------------+-----------------+ 
7. | collation_connection | utf8_general_ci | 
8. | collation_database | utf8_bin | 
9. | collation_server | utf8_general_ci | 
10. +----------------------+-----------------+ 
11. 3 rows in set (0.00 sec) 
12. mysql> select * from t1; 
13. +------+ 
14. | name | 
15. +------+ 
16. | ABC | 
17. | abc | 
18. +------+ 
19. 2 rows in set (0.00 sec) 
20.  
21. mysql> select * from t1 where name='abc'; 
22. +------+ 
23. | name | 
24. +------+ 
25. | abc | 
26. +------+ 
27. 1 row in set (0.01 sec) 

可以看出，在数据库级进行相应的校对规则设置，查询大小写敏感。

2.3表级

MySQL按照下面的方式选择表字符集和校对规则：

如果指定了character set X和collate Y，那么采用character set X和collate Y。

如果指定了character set X而没有指定collate Y，那么采用character set X和character set X的默认校对规则。

否则，采用数据库字符集和服务器校对规则。

在创建表时设置表级校验规则：

1. mysql> create database yutest2; 
2. Query OK, 1 row affected (0.01 sec) 
3. mysql> use yutest2; 
4. Database changed 
5.  
6. mysql> create table t1(name varchar(10)) 
7. -> default character set utf8 collate utf8_bin; 
8. Query OK, 0 rows affected (0.01 sec) 
9.  
10. mysql> insert into t1 values('ABC'); 
11. Query OK, 1 row affected (0.00 sec) 
12. mysql> insert into t1 values('abc'); 
13. Query OK, 1 row affected (0.00 sec) 
14.  
15. mysql> show variables like 'collation_%'; 
16. +----------------------+-----------------+ 
17. | Variable_name | Value | 
18. +----------------------+-----------------+ 
19. | collation_connection | utf8_general_ci | 
20. | collation_database | utf8_general_ci | 
21. | collation_server | utf8_general_ci | 
22. +----------------------+-----------------+ 
23. 3 rows in set (0.00 sec) 
24.  
25. mysql> select * from t1; 
26. +------+ 
27. | name | 
28. +------+ 
29. | ABC | 
30. | abc | 
31. +------+ 
32. 2 rows in set (0.00 sec) 
33.  
34. mysql> select * from t1 where name='abc'; 
35. +------+ 
36. | name | 
37. +------+ 
38. | abc | 
39. +------+ 
40. 1 row in set (0.00 sec) 

可以看出，在表级进行相应的校对规则设置，查询大小写敏感。

2.4 连接级

考虑什么是一个“连接”：它是连接服务器时所作的事情。客户端发送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 | utf8 | 
8. | character_set_filesystem | binary | 
9. | character_set_results | utf8 | 
10. | character_set_server | utf8 | 
11. | character_set_system | utf8 | 
12. | character_sets_dir | /usr/share/mysql/charsets/ | 
13. +--------------------------+----------------------------+ 
14. 8 rows in set (0.00 sec) 

当查询离开客户端后，在查询中使用哪种字符集?

服务器使用character_set_client变量作为客户端发送的查询中使用的字符集。

服务器接收到查询后应该转换为哪种字符集?

转换时，服务器使用character_set_connection和collation_connection系统变量。它将客户端发送的查询从character_set_client系统变量转换到character_set_connection。

服务器发送结果集或返回错误信息到客户端之前应该转换为哪种字符集?

character_set_results变量指示服务器返回查询结果到客户端使用的字符集。包括结果数据，例如列值和结果元数据(如列名)。

3. 创建数据库表时大小写不敏感，仍然有方法在查询时区分大小写

3.1 在SQL语句中使用collate

使用collate子句，能够为一个比较覆盖任何默认校对规则。collate可以用于多种SQL语句中，比如where，having，group by，order by，as，聚合函数。

1. mysql> select * from t1 where name collate utf8_bin = 'ABC'; 
2. +------+ 
3. | name | 
4. +------+ 
5. | ABC | 
6. +------+ 
7. 1 row in set (0.00 sec) 
8.  
9. mysql> select * from t1 where name = 'ABC'; 
10. +------+ 
11. | name | 
12. +------+ 
13. | ABC | 
14. | Abc | 
15. | abc | 
16. +------+ 
17. 3 rows in set (0.00 sec) 
18.  
19. mysql> select * from t1; 
20. +------+ 
21. | name | 
22. +------+ 
23. | ABC | 
24. | Abc | 
25. | abc | 
26. +------+ 
27. 3 rows in set (0.00 sec) 

3.2 binary操作符

binary操作符是collate子句的一个速记符。binary 'x'等价与'x' collate y，这里y是字符集'x'二元校对规则的名字。每一个字符集有一个二元校对规则。例如，latin1字符集的二元校对规则是latin1_bin，因此，如果列a是字符集latin1，以下两个语句有相同效果：

1. select * from t1 order by binary a; 
2.  
3. select * from t1 order by a collate latin1_bin; 
4.  
5. mysql> select * from t1 where binary name = 'ABC'; 
6. +------+ 
7. | name | 
8. +------+ 
9. | ABC | 
10. +------+ 
11. 1 row in set (0.00 sec) 
12. mysql> 
13. mysql> select * from t1 where name = 'ABC'; 
14. +------+ 
15. | name | 
16. +------+ 
17. | ABC | 
18. | Abc | 
19. | abc | 
20. +------+ 
21. 3 rows in set (0.00 sec)