MariaDB/MySQL - 如何在查詢資料時區分大小寫


最近在使用資料庫的時候,發現一個之前一直被我忽略的問題:「查詢欄位值時,如何 區分大小寫 的資料?」。在以往,印象中都只是直接查詢資料庫而已,並沒有特別在意這一點,現在想想,MySQL預設的字串搜尋結果似乎都是不分大小寫的,一般這種情況不會有什麼問題,但遇到網頁呈現有要求要區分大小時,那麼預設的搜尋結果就不符合需求了。

MySQL資料庫查詢區分大小寫

Select 區分大小寫:方法一

例如表格中目前的資料有二筆,但是「location」欄位的值分別為「Taiwan」和「taiwan」,第一個字母是有大小寫區別的。此時若是進行一般性的查詢,可以看到下方二筆資料都被列出:

MariaDB [demo]> select * from geo where location = "taiwan";
+----------+----------+---------------------+----------+-----------+----------+
| event_id | location | date                | latitude | longitude | distance |
+----------+----------+---------------------+----------+-----------+----------+
|        1 | Taiwan   | 2018-07-28 20:56:49 |    100.1 |     200.2 |      300 |
|        2 | taiwan   | 2018-07-28 21:04:27 |    300.1 |     400.2 |      500 |
+----------+----------+---------------------+----------+-----------+----------+
2 rows in set (0.00 sec)

但如果想要查詢的location欄位值是「Taiwan」而不是「taiwan」呢?我個人覺得最簡單的方式是直接在搜尋語法上加上「binary」是最快的,以實際的例子來說,語法會變成:
select * from 表格名稱 where binary location = "Taiwan";

或者
select * from 表格名稱 where location = binary "Taiwan";

實際查詢的結果如下,筆數由原本的二筆變成一筆,而且是我指定的查詢字串「Taiwan」:

MariaDB [demo]> select location from geo where binary location = "Taiwan";
+----------+
| location |
+----------+
| Taiwan   |
+----------+
1 row in set (0.00 sec)

Select 區分大小寫:方法二

第二種方式是直接在建立表格時,就指定binary屬性,這樣未來再查詢時就會自動區分大小寫了,例如:
create table geo_demo (
event_id MEDIUMINT NOT NULL AUTO_INCREMENT,
location varchar(30) binary,
...(略)
);

此時,若資料庫中的「location」有二筆資料,分別為「Taiwan」、「tAiwan」, 那麼再進行查詢的時候不需要加「binary」即可自動區分大小寫了,例如:

MariaDB [demo]> select * from geo_demo where location = "tAiwan";
+----------+----------+---------------------+----------+-----------+----------+
| event_id | location | date                | latitude | longitude | distance |
+----------+----------+---------------------+----------+-----------+----------+
|        2 | tAiwan   | 2018-07-28 23:27:57 |    200.1 |     300.2 |      500 |
+----------+----------+---------------------+----------+-----------+----------+
1 row in set (0.00 sec)

PS:亦可以透過變更MySQL的字符集編碼來解決這個問題,這部分就不另外說明了

SQLAlchemy

如果大家有在用SQLAlchemy來存取資料庫的話,那麼也可以利用func.binary來達到一樣的效果,例如:
表格名稱.欄位.like(func.binary('%Taiwan%'))

延伸閱讀:
[教學] MariaDB/MySQL備份 – 如何匯出、匯入資料庫或表格
MariaDB/MySQL 常用指令操作與語法範例