mysql的collation区分大小写设置
mysql
数据库在做查询时候，有时候是英文字母大小写敏感的，有时候又不是的，主要是由于mysql的字符校验规则的设置。通常默认是不支持的大小写字母敏感的，在主动设置my
sql数据库的collation后，可以使得数据库满足大小写敏感，适合客户的一定要求。通过下面的试验进行理解学习.....
Sql代码
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.25 |
+-----------+
1 row in set (0.00 sec)
Sql代码
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\
Sql代码
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 大小写不敏感校验规则；
Sql代码
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是区分大小写的校验规则；
创建表做测试，看数据效果：
Sql代码
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)
比较查询结果：
Sql代码
mysql> insert into t_collation values('m','m'),('n','n');
Query OK, 2 rows affected (0.10 sec)
R