一般的資料庫表格都是水平欄位,而 Key/Value 表格將欄位設計成垂直欄位讓欄位更有彈性,如此一來你可以依照不同種類來給他們不同的欄位,而不用保持一堆欄位處於 NULL
的狀態。
而採用這種設計的 Key/Value 表格通常只有三個欄位:「編號」、「設定名稱」、「設定值」。
但是這種做法也不是沒有缺點,首先你的資料會新增更多筆(可能以倍數新增),而當你遇到要 SELECT ... WHERE .. AND
的時候就會發生問題,而這就是今天要探討的地方。
在一般表格怎麼處理?
下面是一般表格所設計的結構,假設我們有 commentId
, type
和target
。
+-----------+-------+--------+
| commentId | type | target |
+-----------+-------+--------+
| 1 | post | 2 |
| 2 | post | 8 |
| 3 | video | 6 |
+-----------+-------+--------+
如何 SELECT
今天要找出 type = x
和 target = x
的 commentID
只要透過此指令即可:
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 = x
的 commentId
只要透過這樣子的寫法就可以了:
SELECT `commentId` FROM `comment_datas` WHERE name = 'type' AND value = 'post'
但如何 SELECT .. WHERE .. AND
?
這才是真正的問題,你要如何一次去設定兩個條件?先前的方法如此簡單是因為我們只要求一個條件,但在 Key/Value 表格中要求兩個條件就等同於是要符合兩列,而三個條件就是三列(以此類推)。
起初的想法
我原本想說要不要就乾脆執行兩次 Query,然後透過 PHP 去相抵兩個 Query 的資料,最終就可以取得到我要的那一列,但看起來這樣太累了,所以我決定轉戰 Stackoverflow。
在 Stackoverflow 的解答
我最終跑去了 Stackoverflow 詢問解答,你可以在這裡看到那篇問題,而這是最終答案。
翻譯成中文是這樣:
sum
是一個加總的表達式,而它支援在多個欄位中進行加總, 所以這個想法就是取得「加總name = type
和value = 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