mysql的collation区分大小写设置
mysql数据库在做查询时候,有时候是英文字母大小写敏感的,有时候又不是的,主要是由于mysql的字符校验规则的设置。通常默认是不支持的大小写字母敏感的,在主动设置mysql数据库的collation后,可以使得数据库满足大小写敏感,适合客户的一定要求。通过下面的试验进行理解学习.....
- mysql> select version();
- +-----------+
- | version() |
- +-----------+
- | 5.5.25 |
- +-----------+
- 1 row in set (0.00 sec)
- mysql> show variables like '%character%';
- +--------------------------+-------------------------------------------------------
- | Variable_name | Value
- +--------------------------+-------------------------------------------------------
- | character_set_client | utf8
- | character_set_connection | utf8
- | character_set_database | latin1
- | character_set_filesystem | binary
- | character_set_results | utf8
- | character_set_server | latin1
- | character_set_system | utf8
- | character_sets_dir | D:\database\mysql\mysql-5.5.25-winx64\share\charsets\
- mysql> show variables like '%collation%';
- +----------------------+-------------------+
- | Variable_name | Value |
- +----------------------+-------------------+
- | collation_connection | utf8_general_ci |
- | collation_database | latin1_swedish_ci |
- | collation_server | latin1_swedish_ci |
- +----------------------+-------------------+
- 3 rows in set (0.00 sec)
默认即为:collation_connection = utf8_general_ci 大小写不敏感校验规则;
- mysql> show collation like '%utf8%';
- +--------------------------+---------+-----+---------+----------+---------+
- | Collation | Charset | Id | Default | Compiled | Sortlen |
- +--------------------------+---------+-----+---------+----------+---------+
- | utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
- | utf8_bin | utf8 | 83 | | Yes | 1 |
- | utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
- | utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |
客户端字符集:utf8, 校验规则: utf8_general_ci, 默认为yes,即不是大小写敏感的匹配;
而utf8_bin是区分大小写的校验规则;
创建表做测试,看数据效果:
- 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` varchar(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 | M |
- | N | N |
- | a | a |
- | b | b |
- +-------+--------+
- 4 rows in set (0.00 sec)
比较查询结果:
- 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 | M |
- | N | N |
- | a | a |
- | b | b |
- | m | m |
- | n | n |
- +-------+--------+
- 6 rows in set (0.00 sec)
- mysql> select * from t_collation where first='m';
- +-------+--------+
- | first | second |
- +-------+--------+
- | m | m |
- +-------+--------+
- 1 row in set (0.02 sec)
- mysql> select * from t_collation where second='m';
- +-------+--------+
- | first | second |
- +-------+--------+
- | M | M |
- | m | m |
- +-------+--------+
- 2 rows in set (0.00 sec)
- mysql> select * from t_collation where second='M';
- +-------+--------+
- | first | second |
- +-------+--------+
- | M | M |
- | m | m |
- +-------+--------+
- 2 rows in set (0.00 sec)
- mysql> select * from t_collation where first='M';
- +-------+--------+
- | first | second |
- +-------+--------+
- | M | M |
- +-------+--------+
- 1 row in set (0.00 sec)
比较各自的校验规则,utf8_bin是区分大小写的,而utf8_general_ci是不区分的,默认的。
还可以从排序语句中进行比较,看看测试效果的.....
- mysql> select * from t_collation;
- +-------+--------+
- | first | second |
- +-------+--------+
- | M | M |
- | N | N |
- | a | a |
- | b | b |
- | m | m |
- | n | n |
- +-------+--------+
- 6 rows in set (0.00 sec)
- mysql> select * from t_collation order by first;
- +-------+--------+
- | first | second |
- +-------+--------+
- | M | M |
- | N | N |
- | a | a |
- | b | b |
- | m | m |
- | n | n |
- +-------+--------+
- 6 rows in set (0.00 sec)
- mysql> select * from t_collation order by second;
- +-------+--------+
- | first | second |
- +-------+--------+
- | a | a |
- | b | b |
- | M | M |
- | m | m |
- | N | N |
- | n | n |
- +-------+--------+
- 6 rows in set (0.00 sec)
同样符合校验规则的检查。
结论: 在MYSQL数据库中,根据实际业务需要,适当可以调整字符集的(collation)校验规则,修改默认的大小写敏感问题,满足实际需要,这本身就是数据库的一种设置,熟悉标准、规则,适当利用为项目所用,可以针对具体的数据库或者表或者表的列进行设置。
from: http://xiaobo.iteye.com/blog/1830942