MySQL的where查詢的重新認(rèn)識(shí)

相關(guān)學(xué)習(xí)推薦：mysql教程

不能說(shuō)不行

今天加班，業(yè)務(wù)的妹子過(guò)來(lái)找我們查數(shù)據(jù)，說(shuō)數(shù)據(jù)查出來(lái)量不對(duì)。一看妹子的SQL是這樣寫的：

select distinct * from prvt_pub_stmt_vnwhere issue_time >= '2020-08-01'and issue_time <= '2020-08-01'and prs_dmtd_cde in ('p','n');復(fù)制代碼

我分析來(lái)分析去，感覺(jué)沒(méi)有問(wèn)題呀，于是查了一下prs_dmtd_cde 字段的碼值，發(fā)現(xiàn)不僅有大寫的P還有小寫的p，而妹子只查了小寫的p，數(shù)據(jù)量卻多了很多。

于是我就把妹子的SQL改了一下：

select distinct * from prvt_pub_stmt_vnwhere issue_time >= '2020-08-01'and issue_time <= '2020-08-01'and prs_dmtd_cde in ('p','n','P','N');復(fù)制代碼

查出來(lái)的結(jié)果竟然是一樣的。這就。。。

在妹子面前當(dāng)然不能說(shuō)不行啊，于是讓妹子先回去再看看。

我這邊飛快的上網(wǎng)查了查，發(fā)現(xiàn)竟然是MySQL 的編碼格式和排序規(guī)則的問(wèn)題。

知其所以然

我們MySQL數(shù)據(jù)庫(kù)基本上用的都是 utf8 的編碼格式，而 utf8 編碼格式還存在各種排序規(guī)則。常用的如下：

utf8_bin：將字符串中的每一個(gè)字符以十六進(jìn)制方式存儲(chǔ)數(shù)據(jù)，區(qū)分大小寫。

utf8_general_ci：不區(qū)分大小寫，ci為case insensitive的縮寫，即大小寫不敏感。

再查一下默認(rèn)的字符集設(shè)置：

剛好 utf8 編碼格式的默認(rèn)排序規(guī)則就是：utf8_general_ci——即不區(qū)分大小寫。

問(wèn)題原因找到了，那就對(duì)癥下藥好了。

解決方法自然就是直接修改字段的 collate 屬性為 utf8_bin。

ALTER TABLE prvt_pub_stmt_vn CHANGE prs_dmtd_cde prs_dmtd_cde VARCHAR(255)  CHARACTER SET utf8 COLLATE utf8_bin;復(fù)制代碼

另外還有一種解決方法，就是不改變?cè)斜斫Y(jié)構(gòu)，而是改SQL。在查詢字段前加上 binary 關(guān)鍵字。

select distinct * from prvt_pub_stmt_vnwhere issue_time >= '2020-08-01'and issue_time <= '2020-08-01'and binary prs_dmtd_cde in ('p','n');復(fù)制代碼

Mysql 默認(rèn)查詢是不分大小寫的，可以在 SQL 語(yǔ)句中加入 binary 來(lái)區(qū)分大小寫。

binary 不是函數(shù)，是類型轉(zhuǎn)換運(yùn)算符，它用來(lái)強(qiáng)制它后面的字符串為一個(gè)二進(jìn)制字符串，可以理解為在字符串比較的時(shí)候區(qū)分大小寫。

問(wèn)題解決了，當(dāng)然是去告訴妹子這個(gè)問(wèn)題多么多么深?yuàn)W，我又是如何剖析原理最終解決的了。

看著妹子投來(lái)的崇拜目光，當(dāng)然是很開(kāi)心了。

最最重要的還是要記住這個(gè)問(wèn)題，以后在遇到字段大小寫敏感的業(yè)務(wù)，建表的時(shí)候要注意字符集和排序規(guī)則的選擇，以避免今天這種事情的發(fā)生。

想了解