mysql一列多值多屬性設計-亂穿馬路

亂穿馬路

需求：每個產品都可能有多個聯絡人

簡單方案：將單值儲存改為逗號分隔儲存多值

常用逗號分隔的列表來避免在多對多關係中建立交叉表，這是一種反模式，稱為亂穿馬路(Jaywalking),因為可以避免十字路口

目標：儲存多值屬性

如何在一列中儲存一系列相關資料的集合

一個賬號對應多個產品

每個產品對應一個聯絡人

但每個產品可能對應多個聯絡人

反模式：格式化的逗號分隔列表

查詢指定賬號的產品

查詢異常困難，不能再用等號

不得不借助模式匹配

困難出錯且無法帶來效能的優勢

查詢指定產品的賬號

多表連線也不合適

執行聚合查詢

很糟糕

缺陷：

1. 困難出錯且無法帶來效能的優勢
2. 多表連線也不合適
3. 聚合查詢很糟糕
4. 更新過於麻煩
5. 驗證麻煩
6. 選擇合適的分隔符，儲存的是字串使用逗號就不合適，甚至使用任何可能出現的新字元都不合適
7. 列表長度限制

識別

1. 列表最多支援存放多少資料
2. 如何分詞查詢
3. 哪個字元不會出現在任何一個列表中

合理使用

可能會需要，也可能沒必要使用這樣的單獨項。如果資料來源是這樣的值，並且只做儲存不做修改就每必要分開。

謹慎使用，儘可能用規範化的，更加靈活可變

解決方案：建立一張交叉表

將account_id儲存在單獨的一張表中而不是products表，每個獨立的account_id都可佔據一行

展現了products和accounts的多對多

問題解決：

1. 通過賬號查詢產品和反查，使用連線查詢，由於能使用索引更加高效，且更加簡單

   select p.* from products as p join constracts as c on(p.product_id = c.product_id) where c.account = 34;

2. 聚合查詢

   select product_id,count(*) from constracts group by product_id

3. 更新指定產品的相關聯絡人

   [外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片儲存下來直接上傳(img-y5aAQhmV-1608896591993)(亂穿馬路.assets/image-20201221214154487.png)]

4. 驗證產品id

   約束資料型別

5. 不用分隔符

6. 沒有長度限制

7. 用到索引，效率更改，新增額外屬性，比如記錄一個聯絡人被加入產品的具體日期，或產品的第一、第二聯絡人

區域被新增到活動的日期，活動的主要區域、次級區域等

每個值都應該儲存在各自的行與列中。

建立交叉表