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

时间:2021-08-05 04:14:00


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)