Início / dba / Perguntas / 267597
Accepted
Googlebot
Asked: 2020-05-22 01:37:29 +0800 CST

Como adicionar todas as combinações de dois pares de valores-chave a uma tabela?

  • 772

Eu tenho um banco de dados de itens, pois cada item tem um conjunto de pares chave-valor como

place => london
field => engineering

já que frequentemente preciso encontrar itens com os mesmos dois conjuntos de valores-chave. Criei tabelas de suporte armazenando ids designados para cada keye 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

(Eu removi índices e FKs por simplicidade)

Por exemplo, uma linha da tabela Pairsé

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

Eu preciso preencher as duas últimas tabelas.

  1. Como posso INSERTtodas as combinações da Key1,Value1,Key2,Value2tabela ItemKeyValues (KeyID,ValueID)?
  2. Como posso UPDATEmesa ItemPairsda mesa ItemPairs?

O desempenho é de grande importância, pois a tabela ItemKeyValuestem mais de 100 milhões de linhas (embora esta seja uma operação única).

mysql mariadb

1 respostas

  1. Best Answer
    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;

    violino

    PS. Simplifiquei suas estruturas removendo índices e opções.

    PPS. Seu pacote de scripts DDL contém pelo menos 1 erro (erro de cópia e colagem?).

    • 1

relate perguntas