Question

Mysql怎样在SQL中实现区分大小写的查询

Answer 1

1.问题描述
mysql版本：mariadb 10.1.16,采用utf8-ci，不区分大小写的排序规则。
开发人员反应业务要求要区分大小写，就是说当输入小写条件时，只能返回小写条件对应的数据，不能返回大写。

dba@192.168.19.215:3306mdls01:58:57>SELECT
-> cabinet_no
-> FROM
-> go_task_ycon_refrence gtyr
-> WHERE
-> 1 = 1
-> AND cabinet_no = 'abcd4499503'
-> ORDER BY
-> 1 ASC;
+-------------+
| cabinet_no |
+-------------+
| ABCD4499503 |
+-------------+
1 row in set (0.00 sec)
2.问题解决
在条件字段后面加COLLATE utf8_bin ，或在表级或字段级指定COLLATE utf8_bin
dba@192.168.19.215:3306mdls02:08:08>SELECT
-> cabinet_no
-> FROM
-> gtyr
-> WHERE
-> 1 = 1
-> AND cabinet_no COLLATE utf8_bin = 'ABCDU4499503'
-> ORDER BY
-> 1 ASC;
+-------------+
| cabinet_no |
+-------------+
| ABCD4499503 |
+-------------+
1 row in set (0.00 sec)
dba@192.168.19.215:3306mdls02:08:43>SELECT
-> cabinet_no
-> FROM
-> gtyr
-> WHERE
-> 1 = 1
-> AND cabinet_no COLLATE utf8_bin = 'abcd4499503'
-> ORDER BY
-> 1 ASC;
Empty set (0.00 sec)