在INSERT INTO ... ON DUPLICATE KEY UPDATE之前检查值是否存在 [英] Check if values exist before INSERT INTO ... ON DUPLICATE KEY UPDATE

问题描述

我有一个应用程序，用户可以在其中提交产品评论.用户还可以编辑以前提交的评论，或针对同一产品提交另一条评论.

I have an an application where users can submit reviews for a product. Users can also edit previously submitted reviews or submit another one for the same product.

我正在实施自动保存"每X秒保存一次表单数据的功能.如果页面意外关闭，则用户可以恢复此草稿".

I am implementing an "auto-save" feature that saves the form data once every X seconds. If the page is accidentally closed, the user can restore this "draft".

这是我的桌子的简化版本:

This is a simplified version of my table:

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.

Bear in mind that this table only stores the drafts - not the actual reviews. If we're editing a review review_id will point to that review. If we're in the process of creating a new review, this field will be NULL.

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

This is my query for inserting a new draft:

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组合的地方插入新行.

This works fine for inserting new review-drafts. The indexes prevent inserting a new row where the combination of product_id and user_id already exist.

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

My problem is for inserting drafts for existing reviews, where review_id needs to point to an existing review because, ideally, the index here needs to be the combination of product_id, user_id and review_id. Unfortunately in my case, the following applies:

UNIQUE索引允许可以包含NULL的列使用多个NULL值

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

尽管上面的报价有疑问和答案，但我不一定要使null值成为唯一索引的一部分，而是要找到一种解决方法.

While there are questions and answers about the above quote, I'm not necessarily interested in achieving getting a null value be part of a unique index - but rather to find a workaround.

我想我可以先进行选择查询，以检查上述组合是否存在，如果不存在，则进行主查询.但是，如果可能的话，我想将所有这些信息整合到一个查询中.想法?

I guess I could first make a select query to check if the above combination exists, and if not proceed with the main query. But I'd like to get all that into one query if possible. Ideas?

谢谢.

推荐答案

您可以代替review_autosave_data创建两个表，例如review_insert_drafts和review_update_drafts(一个用于新评论，一个用于评论更新).

Instead of review_autosave_data you can create two tabels like review_insert_drafts and review_update_drafts (one for new reviews and one for review updates).

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列的用处.)

在您的应用程序中，您必须检查用户是否正在编写新评论或正在更新现有评论.

In your application you have to check if the user is writing a new review or is updating an existing one.

对于新评论，您运行:

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");

要运行评论更新，请执行以下操作:

For review updates you run:

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");

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

Advantages: You have a clear design with clear unique keys and foreign keys.

缺点:您有两个包含相似数据的表.因此，您有两个不同的插入语句.如果要合并两个表(例如，为用户显示所有草稿)，则需要UNION语句.

Disadvantages: You have two tables containing similar data. So you have two different insert statements. And you will need a UNION statement if you want to combine the two tables (e.g. show all drafts for a user).

这篇关于在INSERT INTO ... ON DUPLICATE KEY UPDATE之前检查值是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！