表中明明沒有這條資料,竟然還能查出來?

苏三说技术發表於2024-11-11

大家好,我是蘇三,又跟大家見面了。

前言

當你看到這個標題時,第一反應可能是想:標題搞錯了吧?

答:沒搞錯,各位看官,聽我慢慢道來。

我之前寫過一篇文章《明明加了唯一索引,為什麼還是產生重複資料?》,發表之後,被很多博主轉載過,引起了全網很多讀者的共鳴。

我最近發現,這類出人意料的線上問題,加上知識點總結,加上乾貨分享,更容易吸引讀者。

所以,這篇文章將會延續上篇文章的風格,從一個線上問題開始。

1.還原問題現場

有一天下午,有使用者反饋說,它自定義的品牌:yoyo,一直都新增不成功。

我查了一下伺服器的日誌,並沒有異常。

在我們的建立商品頁面,使用者可以選擇已有品牌,也可以自己自定義新的品牌。

前端做了一個品牌的下來列表,為了方便使用者查詢,支援搜尋。

使用者可以輸入關鍵字搜尋品牌。

如果下拉框中出現了,則可以選擇使用。

如果下拉框中沒有資料,則在輸入框中標識這個品牌是使用者自定義的品牌。

然後透過建立商品介面,將該品牌新增到資料庫當中。

現在的問題是yoyo這個品牌,使用者自定義了,但不能儲存到資料庫當中。

這就非常奇怪了。

2 分析問題

為了查明這個問題,我先查詢了資料庫中的品牌表:

select * from brand where `name`='yoyo';

確實沒有查出yoyo這個品牌。

但意外查出YOYO這個品牌。

圖片

它是yoyo英文字母的大寫。

奇怪,我們查小寫的yoyo字串,為什麼會把大寫的YOYO查出來了?

於是,我查了brand表的表結構。

CREATE TABLE `brand` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(30) NOT NULL COMMENT '品牌名稱',
`create_user_id` bigint NOT NULL COMMENT '建立人ID',
`create_user_name` varchar(30) NOT NULL COMMENT '建立人名稱',
`create_time` datetime(3) DEFAULT NULL COMMENT '建立日期',
`update_user_id` bigint DEFAULT NULL COMMENT '修改人ID',
`update_user_name` varchar(30) DEFAULT NULL COMMENT '修改人名稱',
`update_time` datetime(3) DEFAULT NULL COMMENT '修改時間',
`is_del` tinyint(1) DEFAULT '0' COMMENT '是否刪除 1:已刪除 0:未刪除',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='品牌表';

品牌表使用的儲存引擎ENGINE是InnoDB,為了保證表的事務性。

字符集CHARSET用的utf8mb4,可以儲存一些表情符號等特殊字元。

校對規則COLLATE用的utf8_unicode_ci。

字符集是一組符號和編碼的集合,而校對規則是用於比較字符集中字元的規則。

例如,utf8mb4字符集支援儲存Unicode字元,而utf8mb4_0900_ai_ci校對規則定義瞭如何比較這些字元。

在MySQL中使用show collation指令,可以檢視到所有COLLATE。

以utf8mb4為例,該編碼所支援的所有COLLATE如下圖所示。圖片

主要包含了三種:

  1. 以_ci結尾的。
  2. 以_bin結尾的。
  3. 以_cs結尾的。

ci是case insensitive的縮寫,意思是大小寫不敏感,即忽略大小寫。

cs是case sensitive的縮寫,意思是大小寫敏感,即區分大小寫。

還有一種是bin,它是將字串中的每一個字元用二進位制資料儲存,區分大小寫。

使用最多的是 utf8mb4_general_ci(預設的)和 utf8mb4_bin。

我們的brand表,使用的COLLATE是utf8mb4_general_ci,它不區分大小寫。

難怪下面的這條sql:

select * from brand where `name`='yoyo';

資料庫中明明沒有小寫的yoyo這條資料,但卻能把大寫的YOYO資料查出來。

3.如何解決問題?

知道原因了,就好辦了。

第一個想到的是把brand表的COLLATE改成utf8mb4_bin不就搞定了?

這樣確實可以非常快速解決問題。

但我仔細想了一下。

品牌這種基礎資料,yoyo和YOYO正常情況下應該是同一個品牌,應該只有一個id,不區分大小寫才是正確的做法。

如果brand表的COLLATE改成了utf8mb4_bin,區分大小寫,不就會出現兩個不同的id,這樣品牌表不就會產生重複的資料,後面會導致商品也可能會重複。

如果後面商品也重複了,就會帶來非常多的問題。

因此,我們要在brand表做好控制,不應該區分大小寫,保證品牌不會重複。

既然修改brand表的COLLATE這個方案不行,那麼,只能修改業務邏輯了。

目前有兩種解決方案:

  1. 前端搜尋品牌時,不區分大小寫。
  2. 前端品牌下拉控制元件,改成分頁的,搜尋品牌的功能,改成呼叫後端介面實現。

方案1適合品牌資料量少的情況。

方案2適合品牌資料量多的情況。

我們的品牌資料,其實在不斷增加,因此,決定使用方案2。

後端提供一個分頁查詢品牌的介面,並且支援不區分大小寫的模糊搜尋功能。

但這樣還不能100%保證,品牌資料在brand表中不會重複。

還需要給name欄位增加唯一索引。

這樣改造之後,後面使用者輸入yoyo,但資料庫中有YOYO,在品牌下拉選單中會顯示YOYO,使用者可以直接選擇使用。

這樣對使用者的互動更友好一些。

這是一類問題,可以衍生一下。

有些屬性值表也有類似的問題。

比如使用者自定義屬性值之後,如果業務邏輯中有透過屬性id查詢屬性值集合,再拿這個屬性值集合跟自定義屬性值做判斷的時候,就需要忽略大小寫做判斷了。

其實,在我們的實際工作中,這樣的場景很多,趕緊排查一下程式碼,看看你有沒有這個問題?

最後說一句(求關注,別白嫖我)

如果這篇文章對您有所幫助,或者有所啟發的話,幫忙掃描下發二維碼關注一下,您的支援是我堅持寫作最大的動力。
求一鍵三連:點贊、轉發、在看。
關注公眾號:【蘇三說技術】,在公眾號中回覆:進大廠,可以免費獲取我最近整理的10萬字的面試寶典,好多小夥伴靠這個寶典拿到了多家大廠的offer。

相關文章