Mysql如何在SQL中实现区分大小写的查询

来源：互联网发布：ubuntu steam os 编辑：程序博客网时间：2024/05/22 05:13

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)

0 0