MYSQL|如果同一个表的同一行中不存在值，则插入一个值

Question

我尝试做类似的事情

+----+------------+----------------+
| id | customerID |productID       |
+----+------------+----------------+
|  1 | 123        | 1              | 
+----+------------+----------------+
|  2 | 123        | 2              | 
+----+------------+----------------+
|  3 | 456        | 2              | 
+----+------------+----------------+

客户可以拥有一些独特的产品。另一位客户可以拥有相同的产品，但对他来说是独一无二的

我尝试这样做:

INSERT INTO FavoriteProducts (customerID, productID)
SELECT * FROM (SELECT '123', '1') AS tmp
WHERE NOT EXISTS (
    SELECT productID FROM FavoriteProducts WHERE customerID = '123'
) LIMIT 1;

SELECT * FROM FavoriteProducts;

+----+------------+----------------+
| id | customerID |productID       |
+----+------------+----------------+
|  1 | 123        | 1              | 
+----+------------+----------------+

我用另一个 prudoctID 重试:

INSERT INTO FavoriteProducts (customerID, productID)
SELECT * FROM (SELECT '123', '2') AS tmp
WHERE NOT EXISTS (
    SELECT productID FROM FavoriteProducts WHERE customerID = '123'
) LIMIT 1;

SELECT * FROM FavoriteProducts;

+----+------------+----------------+
| id | customerID |productID       |
+----+------------+----------------+
|  1 | 123        | 1              | 
+----+------------+----------------+

但是不起作用:(

Answer 1

您必须将 NOT EXISTS 上的查询与外部查询链接起来(也使用产品 ID):

INSERT INTO FavoriteProducts (customerID, productID)
SELECT * 
FROM (SELECT '123', '2' AS pID) AS tmp
WHERE NOT EXISTS (
    SELECT productID 
    FROM FavoriteProducts 
    WHERE customerID = '123' AND productID = tmp.pID
) LIMIT 1;

您还可以使用 UNIQUE 约束来解决这个问题:

ALTER TABLE FavoriteProducts
ADD CONSTRAINT unique_customer_product UNIQUE (customerID, productID);

...通过使用UNIQUE constraint您可以使用简单的INSERT:

INSERT INTO FavoriteProducts (customerID, productID) VALUES
  ('123', '4');

demo on dbfiddle.uk