有条件地插入MySQL

本文介绍了有条件地插入MySQL-不存在的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

当客户没有购买产品的记录时，我正在尝试执行INSERT INTO查询.

I am trying to do an INSERT INTO query when the customer does not already have a record of purchasing a product.

我尝试了以下SQL，但似乎无法正常工作.

I have tried the below SQL, but it doesn't seem to work.

$queryAddPurchase = "INSERT INTO purchase (UserID, Product, Price) VALUES ('$userID', '$product', '$price') WHERE NOT EXISTS(SELECT Product FROM purchase WHERE Product = '$product' AND UserID = '$userID')";

我收到的错误如下:

您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册，以获取正确的语法，以在第3行的不存在(选择购买的产品"中使用)附近

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NOT EXISTS(SELECT Product FROM purchase ' at line 3

任何建议将不胜感激！

推荐答案

如果可能，请向表中添加UNIQUE约束.像...

If possible, add a UNIQUE constraint to the table. Something like ...

alter table purchase add unique user_product (UserID, ProductID);

您的条件插入变得无足轻重了:

And your conditional insert becomes trivial:

INSERT IGNORE INTO purchase (UserID, Product, Price) VALUES ('$userID', '$product', '$price')

如果这不是适合您的解决方案，请在此处使用选择的答案: MySQL条件插入

If this is not a suitable solution for you, use the selected answer here: MySQL Conditional Insert

答案很简单:

INSERT INTO purchase (UserID, Product, Price) SELECT '$userID', '$product', '$price' FROM dual WHERE NOT EXISTS ( SELECT * FROM purchase WHERE UserID='$userID' AND ProductID='$product' )