MySQL「ci」字集做字串比對會無視大小寫

在我踩到坑、並找到解法之後,才發現這個很基礎
但踩到了就踩到了,還是紀錄一下🙈

根據 khiav223577 大大的「MySQL 編碼挑選與差異比較」分享,
一般常使用的「ci」字集,其意思是 case-insensitive,也就是大小寫不敏感。
這種設定相當適合用在文章網站、新聞網站的搜尋功能,因為無論使用者輸入大寫的「APPLE」或小寫的「apple」,都能找到所有帶有「Apple」關鍵字的資料。
但當你需要做如 ID 比對、token 比對、短網址代碼比對,可能都需要改成 case-sensitive 字集,
或者在 WHERE 條件中使用 binary 達成目的。

舉例

比如我要做一個短網址服務,我會為每個連結產生 8 個字元的 slug,使用 0-9 a-z A-Z,總共 62 種字,總共是 628(218,340,105,584,896) 種組合

不過下列幾筆資料使用了類似、僅有大小寫不一樣的 slug:

  1. UurxM6ey
  2. UurxM6Ey
  3. UurxM6eY

當使用 ci 字集做查詢第三筆的時候(SELECT * FROM `short_codes` WHERE `code` = 'UurxM6eY' ORDER BY id ASC LIMIT 1;),會因為 case-insensitive,所以找到第一筆資料,抓出跟預期不一樣的資料。

解法

在不修改 ci 字集的條件下要解決這問題,可以把查詢改成:

SELECT * FROM `short_codes` WHERE BINARY `code` = 'UurxM6eY' ORDER BY id ASC LIMIT 

影響

若是不解決這問題,這短網址服務的 slug 組合會剩下 (26/2 + 26/2 + 10)8 = 2,821,109,907,456 種組合
並且會給部分使用同字、不同大小寫的 slug 的使用者帶來困擾

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *