如何知道何时使用“重复密钥更新"?插入或更新了一行? [英] How to know if when using "on duplicate key update" a row was inserted or updated?

问题描述

我们有一个数据库，每天下午12点通过cronjob进行更新，我们从外部XML获取新数据.

We have a database that gets updated everyday at midnight with a cronjob, we get new data from an external XML.

我们要做的是插入所有新内容，并在键重复的情况下更新该字段.

What we do is that we insert all the new content and in case there is a duplicated key we update that field.

INSERT INTO table (id, col1, col2, col3)
values (id_value, val1, val2, val3),
(id_value, val1, val2, val3),
(id_value, val1, val2, val3),
(id_value, val1, val2, val3),
ON DUPLICATE KEY UPDATE 
col1 = VALUES (col1), 
col2 = VALUES (col2), 
col3 = VALUES (col3);

我们想要知道的是实际上已经插入了哪些行，这意味着我们想要一个新项的列表.是否有可能返回新插入的查询?基本上，我们需要获取所有新ID，而不是新插入的数量.

What we want to know is which rows have actually been inserted, meaning we want to have a list of the new items. is there any query that might return the new inserts? Basically we will need to get all the new ID's and not the number of new insertions.

谢谢

推荐答案

添加update_count INT NOT NULL DEFAULT 1列并更改查询:

INSERT
INTO    table (id, col1, col2, col3)
VALUES
(id_value, val1, val2, val3),
(id_value, val1, val2, val3,),
(id_value, val1, val2, val3),
(id_value, val1, val2, val3),
ON DUPLICATE KEY
UPDATE 
        col1 = VALUES (col1), 
        col2 = VALUES (col2), 
        col3 = VALUES (col3),
        update_count = update_count + 1;

您还可以在BEFORE UPDATE触发器中增加它，这将使您保持查询不变.

You can also increment it in a BEFORE UPDATE trigger which will allow you to keep the query as is.

这篇关于如何知道何时使用“重复密钥更新"?插入或更新了一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！