最近在使用資料庫的時候,發現一個之前一直被我忽略的問題:「查詢欄位值時,如何 區分大小寫 的資料?」。在以往,印象中都只是直接查詢資料庫而已,並沒有特別在意這一點,現在想想,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」是最快的,以實際的例子來說,語法會變成:
或者
實際查詢的結果如下,筆數由原本的二筆變成一筆,而且是我指定的查詢字串「Taiwan」:
MariaDB [demo]> select location from geo where binary location = "Taiwan";
+----------+
| location |
+----------+
| Taiwan |
+----------+
1 row in set (0.00 sec)
Select 區分大小寫:方法二
第二種方式是直接在建立表格時,就指定binary屬性,這樣未來再查詢時就會自動區分大小寫了,例如:
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來存取資料庫的話,那麼也可以利用
延伸閱讀:
.[教學] MariaDB/MySQL備份 – 如何匯出、匯入資料庫或表格
.MariaDB/MySQL 常用指令操作與語法範例