MYSQL插入和更新的记录数 [英] MYSQL number of records inserted and updated

问题描述

我有以下SQL语句

INSERT INTO my_counts (type,code,count) SELECT type,id ,1 from my_data WHERE user_id=10
ON DUPLICATE KEY UPDATE count = count + 1; 

类型，代码是复合主键.

请让我知道如何找出插入的记录数以及分别更新的记录数吗?

Could you please let me know how can I find out the number of records inserted and also the number of records updated separately ?

我知道 SELECT ROW_COUNT()给出了受影响的行(2个用于更新，1个用于插入). 但是如何单独找出

I know SELECT ROW_COUNT() gives the effected rows ( 2 for update and 1 for insert) . But how to find out separately

1) the number of rows updated 
2) the number of rows inserted

感谢您的帮助

致谢

基兰

推荐答案

我不知道从诊断信息中获取此信息的好方法.

I don't know of a good way to get this from the diagnostics information.

在任何情况下，假设您已对其进行控制，以使表中仅 just INSERT ed行(或仅被查询)，则可以在事实:

In any case, supposing that you've controlled it so that only the rows just INSERT ed are in the table (or will only be queried), this can be simply retrieved after the fact:

SELECT COUNT(*) as Inserted, SUM(count - 1) as Updated
FROM my_counts;

(请注意，正如我的SQLFiddle 示例中所述，这些数字实际上可以从基础数据中预测出来.

(please note that, as included in my SQLFiddle example, these numbers can actually be predicted from the base data).

这篇关于MYSQL插入和更新的记录数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！