mysql默认情况下是对大小写不敏感的,有的时候我们可能需要让他对大小写敏感,肿么办?
比如有如下表结构和内容:
mysql> select * from test;
+----+----------+-------+-------+
| id | name | class | point |
+----+----------+-------+-------+
| 1 | fff | 1 | 100 |
| 2 | duanhong | 2 | 59 |
| 3 | laoda | 3 | 60 |
| 4 | laoer | 4 | 59 |
| 5 | laogao | 1 | 59 |
| 6 | laosi | 2 | 100 |
| 7 | laowu | 3 | 20 |
| 8 | laoliu | 4 | 10 |
| 9 | Lni | 2 | 90 |
| 10 | Lii | 3 | 67 |
+----+----------+-------+-------+
10 rows in set (0.00 sec)
我们需要查询name以'l'开头的,执行:
mysql> select * from test where name like 'l%';
+----+--------+-------+-------+
| id | name | class | point |
+----+--------+-------+-------+
| 3 | laoda | 3 | 60 |
| 4 | laoer | 4 | 59 |
| 5 | laogao | 1 | 59 |
| 6 | laosi | 2 | 100 |
| 7 | laowu | 3 | 20 |
| 8 | laoliu | 4 | 10 |
| 9 | Lni | 2 | 90 |
| 10 | Lii | 3 | 67 |
+----+--------+-------+-------+
8 rows in set (0.00 sec)
结果却连大写'L'开头的都查询出来了。
要区分大小写,可以在创建数据表的时候指定编码为binary类型,但是我们的表在创建的时候是utf8编码的,我们也可以在查询的时候将字段编码设置成2进制,这样就能区分大小写了:
mysql> select * from test where name collate utf8_bin like 'l%';
+----+--------+-------+-------+
| id | name | class | point |
+----+--------+-------+-------+
| 3 | laoda | 3 | 60 |
| 4 | laoer | 4 | 59 |
| 5 | laogao | 1 | 59 |
| 6 | laosi | 2 | 100 |
| 7 | laowu | 3 | 20 |
| 8 | laoliu | 4 | 10 |
+----+--------+-------+-------+
6 rows in set (0.00 sec)