mysql在3个表之间插​​入和选择

Question

我正在寻找更好的方法来处理以下问题。

custData
--------
ID | Code
1  |  A
2  |  B

prodData
--------
ID | ProdID
1  | prodA
2  | prodB
3  | prodC

我想在此表中保留记录:

custProdPrice
--------
ID | Cust | Prod
 1 |  A   | prodA
 2 |  A   | prodB
 3 |  A   | prodC
 4 |  B   | prodA
 5 |  B   | prodB
 6 |  B   | prodC

我通过使用以下查询来完成它

create view  CashPOSDB.viewertable as select Code, ProdID as pid, BelongTo
from CashPOSDB.custData cross join CashPOSDB.prodData

AND

insert into CashPOSDB.custProdPrice(Cust, Prod, BelongTo)(select Code, Pid, BelongTo from
(select Code, pid, belongTo from CashPOSDB.viewertable UNION ALL
select Cust, Prod, BelongTo from CashPOSDB.custProdPrice) t group by Code, Pid); 

它完成了这项工作，但似乎有时会丢失一些结果或插入重复的行。

此外，我希望它进行检查，以便当我插入新的 Prod prodD 时，它会检查 custProdPrice 并查看 cust A 和 B 是否已经有行

A | prodD
b | prodD  

如果不存在则将其插入。

那么优化这个的最佳方法是什么？

Answer 1

为custProdPrice 表指定(Cust, Prod) 上的唯一索引以防止重复:

ALTER TABLE custProdPrice ADD UNIQUE INDEX (Cust, Prod);

然后使用INSERT IGNORE 跳过重复项。

在插入 custProdPrice 时，似乎没有任何理由将 UNION 与 custProdPrice 结合起来。只需插入叉积即可。

INSERT IGNORE INTO CashPOSDB.custProdPrice (Cust, Prod, BelongTo)
SELECT Cust, Prod, BelongTo
FROM CashPOSDB.viewertable

要自动向 custProdPrice 添加新行，请使用触发器:

CREATE TRIGGER addCustProdPrice
AFTER INSERT ON prodTable
FOR EACH ROW
    INSERT IGNORE INTO custProdPrice (Cust, Prod, BelongTo)
    SELECT c.Cust, NEW.Prod, NEW.BelongTo
    FROM custData;