如何將兩對鍵值的所有組合添加到表中？

我有一個項目數據庫，因為每個項目都有一組鍵值對，例如

place => london
field => engineering

因為我經常需要找到具有相同兩個鍵值集的項目。id我通過為每個key和儲存指定的 s 來創建支持表value。

CREATE TABLE ItemKeyValues
(
ItemID int(11) unsigned,
KeyID mediumint(7) unsigned,
ValueID mediumint(7) unsigned,
PRIMARY KEY (ItemID,KeyID,ValueID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

CREATE TABLE Pairs
(
PairID int(11) unsigned NOT NULL AUTO_INCREMENT,
Key1 mediumint(7) unsigned,
Value1 mediumint(7) unsigned,
Key2 mediumint(7) unsigned,
Value2 mediumint(7) unsigned,
UNIQUE INDEX(Key1,Value1,Key2,Value2),
PRIMARY KEY (ItemID,KeyID,ValueID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

CREATE TABLE ItemPairs
(
PairID int(11) unsigned NOT NULL,
ItemID int(11) unsigned,
PRIMARY KEY (PairID,ItemID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

（為簡單起見，我刪除了索引和 FK）

例如，表格的一行Pairs是

PairID    Key1             Value1            Key2              Value2
1         3 (for place)    88 (for London)   10 (for field)    9 (for engineering)

我需要填寫最後兩張表。

1. 我怎樣才能從表中的INSERT所有組合？Key1,Value1,Key2,Value2``ItemKeyValues (KeyID,ValueID)
2. 我怎樣才能UPDATE從桌子上ItemPairs桌子ItemPairs？

性能非常重要，因為表ItemKeyValues超過 1 億行（儘管這是一次性操作）。

INSERT INTO Pairs
SELECT DISTINCT NULL, t1.KeyID, t1.ValueID, t2.KeyID, t2.ValueID
FROM ItemKeyValues t1
JOIN ItemKeyValues t2 USING (ItemID)
WHERE t1.KeyID < t2.KeyID;

INSERT INTO ItemPairs
SELECT t1.PairID, t2.ItemID
FROM Pairs t1
JOIN ItemKeyValues t2 ON t1.Key1 = t2.KeyID 
                    AND t1.Value1 = t2.ValueID
JOIN ItemKeyValues t3 ON t1.Key2 = t3.KeyID 
                    AND t1.Value2 = t3.ValueID 
                    AND t2.ItemID = t3.ItemID;

小提琴

PS。我通過刪除索引和選項簡化了您的結構。

聚苯乙烯。您的 DDL 腳本包至少包含 1 個錯誤（複製粘貼錯誤？）。

引用自：https://dba.stackexchange.com/questions/267597