MySQL数据库查询大小写是否敏感问题分析
MySQL数据库查询大小写是否敏感问题分析
mysql数据库在做查询时候,有时候是英文字母大小写敏感的,有时候又不是的,主要是由mysql的字符校验规则的设置决定的,通常默认是不支持的大小写字母敏感的。
1. 什么是字符集和校验规则?
字符集是一套符号和编码。校对规则是在字符集内用于比较字符的一套规则。任何一个给定的字符集至少有一个校对规则,它可能有几个校对规则。要想列出一个字符集的校对规则,使用SHOW COLLATION语句。
校对规则一般有这些特征:
- 两个不同的字符集不能有相同的校对规则。
- 每个字符集有一个默认校对规则。例如,utf8默认校对规则是utf8_general_ci。
- 存在校对规则命名约定:它们以其相关的字符集名开始,通常包括一个语言名,并且以_ci(大小写不敏感)、_cs(大小写敏感)或_bin(二元)结束。
2. 不同级别的字符集和校验规则可控制大小写敏感
MySQL5.1在同一台服务器、同一个数据库或甚至在同一个表中使用不同字符集或校对规则来混合定义字符串。字符集和校对规则有4个级别的默认设置:服务器级、数据库级、表级和连接级。

这个只适用于在重新启动之后, 新建的库,已存在的库不受影响.
同样的, 即使库的校验规则改了,已经存在的表不受修改影响;
同理与已经存在的列…
mysql> create database yutest0;Query OK, 1 row affected (0.00 sec)mysql> use yutest0;Database changedmysql> create table t1 (name varchar(10));Query OK, 0 rows affected (0.01 sec)mysql> insert into t1 values(‘AAA’);Query OK, 1 row affected (0.00 sec)mysql> insert into t1 values(‘aaa’);Query OK, 1 row affected (0.01 sec)mysql> select * from t1;+——+| name |+——+| AAA || aaa |+——+2 rows in set (0.00 sec)mysql> select * from t1 where name=’aaa’;+——+| name |+——+| aaa |+——+1 row in set (0.00 sec)
可以看出,在服务器级进行相应的校对规则设置,查询大小写敏感。
shell> mysqld –character-set-server=latin1 –collation-server=latin1_swedish_ci
- 如果指定了character set X和collate Y,那么采用字符集X和校对规则Y。
- 如果指定了character set X而没有指定collate Y,那么采用character set X和character set X的默认校对规则。
- 否则,采用服务器字符集和服务器校对规则。
(1)修改配置文件/etc/my.cnf
进行了两组测试:
1) 在[mysqld]下添加:
collation_server = utf8_bin
collation_database = utf8_bin
2) 在[mysqld]下添加:
collation_database = utf8_bin
重启实例,两组都不能正常启动,错误信息如下:
可见,my.cnf配置文件中不支持设置collation_database 变量。
(2)创建数据库时设置数据库校验规则
mysql> create database yutest0;Query OK, 1 row affected (0.00 sec)mysql> use yutest0;Database changedmysql> create table t1 (name varchar(10));Query OK, 0 rows affected (0.01 sec)mysql> insert into t1 values(‘AAA’);Query OK, 1 row affected (0.00 sec)mysql> insert into t1 values(‘aaa’);Query OK, 1 row affected (0.01 sec)mysql> select * from t1;+——+| name |+——+| AAA || aaa |+——+2 rows in set (0.00 sec)mysql> select * from t1 where name=’aaa’;+——+| name |+——+| aaa |+——+1 row in set (0.00 sec)
可以看出,在数据库级进行相应的校对规则设置,查询大小写敏感。
2.3表级
- 如果指定了character set X和collate Y,那么采用character set X和collate Y。
- 如果指定了character set X而没有指定collate Y,那么采用character set X和character set X的默认校对规则。
- 否则,采用数据库字符集和服务器校对规则。
在创建表时设置表级校验规则:
mysql> create database yutest2;Query OK, 1 row affected (0.01 sec)mysql> use yutest2;Database changedmysql> create table t1(name varchar(10))-> default character set utf8 collate utf8_bin;Query OK, 0 rows affected (0.01 sec)mysql> insert into t1 values(‘ABC’);Query OK, 1 row affected (0.00 sec)mysql> insert into t1 values(‘abc’);Query OK, 1 row affected (0.00 sec)mysql> show variables like ‘collation_%’;+———————-+—————–+| Variable_name | Value |+———————-+—————–+| collation_connection | utf8_general_ci || collation_database | utf8_general_ci || collation_server | utf8_general_ci |+———————-+—————–+3 rows in set (0.00 sec)mysql> select * from t1;+——+| name |+——+| ABC || abc |+——+2 rows in set (0.00 sec)mysql> select * from t1 where name=’abc’;+——+| name |+——+| abc |+——+1 row in set (0.00 sec)
可以看出,在表级进行相应的校对规则设置,查询大小写敏感。
2.4 连接级
考虑什么是一个“连接”:它是连接服务器时所作的事情。客户端发送SQL语句,例如查询,通过连接发送到服务器。服务器通过连接发送响应给客户端,例如结果集。对于客户端连接,这样会导致一些关于连接的字符集和校对规则的问题,这些问题均能够通过系统变量来解决:
mysql> show variables like ‘character%’;+————————–+—————————-+| Variable_name | Value |+————————–+—————————-+| character_set_client | utf8 || character_set_connection | utf8 || character_set_database | utf8 || character_set_filesystem | binary || character_set_results | utf8 || character_set_server | utf8 || character_set_system | utf8 || character_sets_dir | /usr/share/mysql/charsets/ |+————————–+—————————-+8 rows in set (0.00 sec)
- 当查询离开客户端后,在查询中使用哪种字符集?
- 服务器接收到查询后应该转换为哪种字符集?
- 服务器发送结果集或返回错误信息到客户端之前应该转换为哪种字符集?
3.1 在SQL语句中使用collate
使用collate子句,能够为一个比较覆盖任何默认校对规则。collate可以用于多种SQL语句中,比如where,having,group by,order by,as,聚合函数。
mysql> select * from t1 where name collate utf8_bin = ‘ABC’;+——+| name |+——+| ABC |+——+1 row in set (0.00 sec)mysql> select * from t1 where name = ‘ABC’;+——+| name |+——+| ABC || Abc || abc |+——+3 rows in set (0.00 sec)mysql> select * from t1;+——+| name |+——+| A
BC || Abc || abc |+——+3 rows in set (0.00 sec)
3.2 binary操作符
select * from t1 order by binary a;select * from t1 order by a collate latin1_bin;
mysql> select * from t1 where binary name = ‘ABC’;+——+| name |+——+| ABC |+——+1 row in set (0.00 sec)mysql>mysql> select * from t1 where name = ‘ABC’;+——+| name |+——+| ABC || Abc || abc |+——+3 rows in set (0.00 sec)