Mysql大小写敏感 - 数据库编程

-----------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------+ | case2 | CREATE TABLE `case2` ( `a` int(11) DEFAULT NULL, `B` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

case4是指定字符集和collate的表

mysql> create table case4(a int(11),B varchar(10)) default character set utf8 collate utf8_bin;;
Query OK, 0 rows affected (0.12 sec)

mysql> show create table case4;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| case4 | CREATE TABLE `case4` (
`a` int(11) DEFAULT NULL,
`B` varchar(10) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

我们可以在创建数据库或表时指定是否大小写敏感，如果没有指定的话，通过语句级的collate和binary也可以实现

mysql> select * from case1;
+------+------+
| a | B |
+------+------+
| 1 | AAA |
| 2 | bbb |
| 4 | AAA |
| 3 | BBB |
+------+------+
4 rows in set (0.00 sec)

mysql> select * from case1 where B like '%b%';
+------+------+
| a | B |
+------+------+
| 2 | bbb |
| 3 | BBB |
+------+------+
2 rows in set (0.00 sec)

指定Collation
mysql> select * from case1 where B like '%b%' collate utf8_bin;
?

+------+------+
| a | B |
+------+------+
| 2 | bbb |
+------+------+
1 row in set (0.01 sec)

通过binary把字符串转化为二进制比较，由于大小写字符的二进制肯定不同，因此也是区分大小的一种方式

mysql> select * from case1 where binary B like '%b%';
+------+------+
| a | B |
+------+------+
| 2 | bbb |
+------+------+
1 row in set (0.00 sec)

mysql>

最后要说明一点的是Collation与索引存储的关系。因为Collation是用于字符串之间比较，而索引是基于比较有序排列的，因此Collation会影响记录的索引顺序

Mysql大小写敏感(二)