Key/Value 表格在 MySQL 中做出 WHERE ... AND ...

一般的資料庫表格都是水平欄位,而 Key/Value 表格將欄位設計成垂直欄位讓欄位更有彈性,如此一來你可以依照不同種類來給他們不同的欄位,而不用保持一堆欄位處於 NULL 的狀態

而採用這種設計的 Key/Value 表格通常只有三個欄位:「編號」、「設定名稱」、「設定值」。

但是這種做法也不是沒有缺點,首先你的資料會新增更多筆(可能以倍數新增),而當你遇到要 SELECT ... WHERE .. AND 的時候就會發生問題,而這就是今天要探討的地方。

在一般表格怎麼處理?

下面是一般表格所設計的結構,假設我們有 commentId, typetarget

+-----------+-------+--------+
| commentId | type  | target |
+-----------+-------+--------+
|         1 | post  |      2 |
|         2 | post  |      8 |
|         3 | video |      6 |
+-----------+-------+--------+

如何 SELECT

今天要找出 type = xtarget = xcommentID 只要透過此指令即可:

SELECT `commentId` FROM `comment_datas` WHERE type = 'post' AND target = '2'  

如何在 Key/Value 表格執行這樣的方法?

而這是今天的問題,下面這個表格才是真正的 Key/Value 表格內容和第一個表格是一樣的,但是被規劃成了 Key/Value 表格。

+-----------+--------+-------+
| commentId |  name  | value |
+-----------+--------+-------+
|         1 | type   | post  |
|         1 | target | 2     |
|         2 | type   | post  |
|         2 | target | 8     |
|         3 | type   | post  |
|         3 | target | 6     |
+-----------+--------+-------+

遇到了什麼問題?

在這種情況下你要如何去 SELECT如果僅是要 SELECT .. WHERE 實際上是很簡單假設我們今天只要取得 type = xcommentId 只要透過這樣子的寫法就可以了:

SELECT `commentId` FROM `comment_datas` WHERE name = 'type' AND value = 'post'  

但如何 SELECT .. WHERE .. AND

這才是真正的問題,你要如何一次去設定兩個條件?先前的方法如此簡單是因為我們只要求一個條件,但在 Key/Value 表格中要求兩個條件就等同於是要符合兩列,而三個條件就是三列(以此類推)。

起初的想法

我原本想說要不要就乾脆執行兩次 Query,然後透過 PHP 去相抵兩個 Query 的資料,最終就可以取得到我要的那一列,但看起來這樣太累了,所以我決定轉戰 Stackoverflow

在 Stackoverflow 的解答

我最終跑去了 Stackoverflow 詢問解答,你可以在這裡看到那篇問題,而這是最終答案。

翻譯成中文是這樣:

sum 是一個加總的表達式,而它支援在多個欄位中進行加總, 所以這個想法就是取得「加總 name = typevalue = post 後結果大於 0」並且由 commentId 所分組的那列。

而實際的解決方法如下:

SELECT commentId  
FROM comment_datas  
GROUP BY commentId  
HAVING sum(name = 'type' AND value = 'post') > 0  
AND sum(name = 'target' AND value = '2') > 0  

圖示來源:Noun project Database By Creative Stall