我以前工作一直都是使用 Oracle database, 而寫 SQL 查詢時(例如做 string comparison)是區分英文大小寫的。
舉個例子,有一個 column 為 product_type
, 而當中有兩個可能 values, Bond
和 BOND
, 只有大小寫之別。那麼寫 WHERE prod_type = 'Bond'
是不會找到 prod_type = 'BOND'
的結果的。
後來有一個朋友工作上需要用到 SQL, 但只會很基礎的。於是乎我便根據自己經驗,為她設計了一些暗藏陷阱的 SQL 題目供她練習。
其中有個深刻回憶,便是中途發現了 MySQL 查詢英文大小寫問題。因為她寫的答案有類似上面例子的情況,答了 WHERE xxx = 'all_lower_case'
, 全小寫。而我印象中那 column 是有分大小寫的。她表示在 MySQL 執行過沒發現問題,大寫的都能找到。然後我便想,咦,難道 MySQL 查詢沒有區分大小寫?
原因
探索了一下,發現是 MySQL collation 的原因。
創建 database 時可以設定 Collation, 如果後綴是 cs
便是區分大小寫,若是 ci
則不區分。
Suffix | Meaning |
---|---|
_ai | Accent-insensitive |
_as | Accent-sensitive |
_ci | Case-insensitive |
_cs | Case-sensitive |
_ks | Kana-sensitive |
_bin | Binary |
雖然可以設定為 Case-insensitive, 不過我個人還是偏向寫 SQL 時默認 Case-sensitive 來寫比較好,當然也得提防撈 data 時撈多了的情況。
References
https://dev.mysql.com/doc/refman/8.0/en/charset-collation-names.html