mysql - 在 INSERT INTO ... ON DUPLICATE KEY UPDATE 之前检查值是否存在

Question

背景

我有一个应用程序，用户可以在其中提交产品评论。用户还可以编辑之前提交的评论或为同一产品提交另一篇评论。

我正在实现“自动保存”功能，每 X 秒保存一次表单数据。如果页面不小心关闭了，用户可以恢复这个“草稿”。

这是我的表格的简化版本:

CREATE TABLE `review_autosave_data` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `review_id` int(11) unsigned DEFAULT NULL,
  `product_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  `review` blob,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_index` (`product_id`, `user_id`),
  KEY `fk_review_autosave_data_review_id (`review_id`),
  KEY `fk_review_autosave_data_product_id (`product_id`),
  KEY `fk_review_autosave_data_user_id (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

请记住，此表仅存储草稿 - 不存储实际评论。如果我们正在编辑评论，review_id 将指向该评论。如果我们正在创建新评论，则此字段将为 NULL。

---

什么在起作用

这是我插入新草稿的查询:

INSERT INTO review_autosave_data (review_id, product_id, user_id, review)
VALUES (25, 50, 1, "lorem ipsum")
ON DUPLICATE KEY
UPDATE review = "lorem ipsum";

这对于插入新的审阅草稿非常有效。索引可防止在 product_id 和 user_id 的组合已存在的情况下插入新行。

---

什么不起作用

我的问题是为现有评论插入草稿，其中review_id需要指向现有评论，因为理想情况下，此处的索引需要是product_id、user_id 和 review_id。不幸的是，就我而言，以下情况适用:

a UNIQUE index permits multiple NULL values for columns that can contain NULL

虽然关于上述引述存在问题和答案，但我不一定对实现将空值作为唯一索引的一部分感兴趣——而是想找到一种解决方法。

我想我可以先做一个选择查询来检查上面的组合是否存在，如果不存在则继续主查询。但如果可能的话，我想将所有这些都放在一个查询中。想法？

谢谢。

Answer 1

您可以创建两个标签，例如 review_insert_drafts 和 review_update_drafts(一个用于新评论，一个用于评论更新)，而不是 review_autosave_data。

CREATE TABLE `review_insert_drafts` (
  `product_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  `review` blob,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`product_id`, `user_id`),
  CONSTRAINT FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  CONSTRAINT FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
);

CREATE TABLE `review_update_drafts` (
  `review_id` int(11) unsigned NOT NULL,
  `review` blob,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`review_id`),
  CONSTRAINT FOREIGN KEY (`review_id`) REFERENCES `reviews` (`id`)
);

(不确定 name 列有什么用。)

在您的应用程序中，您必须检查用户是在撰写新评论还是在更新现有评论。

对于您运行的新评论:

INSERT INTO review_insert_drafts (product_id, user_id, review)
VALUES (50, 1, "lorem ipsum")
ON DUPLICATE KEY
UPDATE review = "lorem ipsum";

或

REPLACE INTO review_insert_drafts (product_id, user_id, review)
VALUES (50, 1, "lorem ipsum");

对于您运行的评论更新:

INSERT INTO review_update_drafts (review_id, review)
VALUES (25, "lorem ipsum")
ON DUPLICATE KEY
UPDATE review = "lorem ipsum";

或

REPLACE INTO review_update_drafts (review_id, review)
VALUES (25, "lorem ipsum");

优点:您的设计清晰，具有清晰的唯一键和外键。

缺点:您有两个包含相似数据的表。所以你有两个不同的插入语句。如果您想合并这两个表(例如显示用户的所有草稿)，您将需要一个 UNION 语句。