duplicate key update的真实影响行数的变通实现方法

nycle

先简单介绍一下：row_count函数，row_count函数表示，当执行DML语句时(insert|update|delete)，所影响的记录行数。
再简单介绍一下：duplicate key update的使用场景语法为：insert xxx values(xxx) on duplicate key update col1=xxx, col2=xxx;表示的含义是：如果主键或唯一索引不冲突时，则进行插入，如果冲突则进行修改。

有个哥们的需求是这样的：由于insert xxx values(xxx) on duplicate key update col1=xxx, col2=xxx语句的执行后返回的影响行数，不是真正变化的行数(insert时没有错，update时会翻倍)，然后他现在执行该语句后，想要返回真正受影响的行数。

我们现在通过实际例子进行演示：
1.创建表t1并初始化3条数据：
  create table t1(id int,name varchar(16),age int, addr varchar(64),primary key(id));
  insert into t1(id,name,age,addr) values(1,'name1',1,'addr1'),(2,'name2',2,'addr2'),(3,'name3',3,'addr3');

2.现在用一条id值已经存在的语句来执行duplicate key update:
mysql> insert into t1(id,name,age,addr) values(3,'name33',33,'addr33') on duplicate key update  name='name3...',addr='addr33...';
Query OK, 2 rows affected (0.09 sec)
我们发现：受影响2条记录(affected)，实际上这个语句，只会影响1条记录，而且是进行update操作(因为id=3在t1表中已经存在了)。那像这种在update时，返回的影响行数会翻倍的情况，那该如何返回真实的影响行数呢？

3.和那位哥们沟通一下了解到：
  3.1)该业务表(这里假设为t1表)是MyISAM存储引擎。
  3.2)是个统计分析系统。也就是通常不会发生并发操作的情况。

4.so nice，知道第3步中的两个情况后，我采用了如下的变通方法：
  4.1)select count(*) from xxx_table; 其值假设为cnt_before
  4.2)执行具体业务的on duplicate key update语句(该语句他已经返回了影响行数(不管是PHP、Java还是C语言，实际上就是调用MySQL的row_count函数)，其值假设为row_business);
  4.3)select count(*) from xxx_table; 其值假设为cnt_after
  4.4)on duplicate key update语句的真实受影响的行数为：
  (row_business - (cnt_after - cnt_before) ) / 2 + (cnt_after - cnt_before);

5.变通方法演示如下：
  tip:
  t1表的创建和初始数据在之前的步骤1已经完成了，我们这里再创建t2表，并初始化数据，把数据构造得稍微复杂一点，尽量做到既有insert，又有update的情况。
  create table t2 select * from t1 limit 2; --这两条会执行update
  insert into t2(id,name,age,addr) values(100,'name100',100,'addr100'); --这一条会执行insert  
select * from t2;--查看一下t2表
+-----+---------+------+---------+
| id  | name    | age  | addr    |
+-----+---------+------+---------+
|   1 | name1   |    1 | addr1   |
|   2 | name2   |    2 | addr2   |
| 100 | name100 |  100 | addr100 |
+-----+---------+------+---------+
3 rows in set (0.00 sec)

select * from t1; --查看一下t1表
+----+----------+------+-----------+
| id | name     | age  | addr      |
+----+----------+------+-----------+
|  1 | name1    |    1 | addr1     |
|  2 | name2    |    2 | addr2     |
|  3 | name3... |    3 | addr33... |
+----+----------+------+-----------+
3 rows in set (0.00 sec)

  接下来开始真正的业务处理变通：
  5.1)业务语句执行前的表的记录数：cnt_before
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql>   
  5.2)执行相应业务语句：
mysql> insert into t1(id,name,age,addr) select * from t2 on duplicate key update name='name99...',addr='addr99...';
Query OK, 5 rows affected (0.04 sec)
Records: 3  Duplicates: 2  Warnings: 0

mysql>   
  
  注：实际上，我们已经知道：执行该语句的真实受影响行数应该是3(insert：1条；update：2条)
  
  5.3)获取本次业务语句影响的行数(注：这个行数并不是真实的影响行数，因为update时受影响行数会翻倍)
mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|           5 |
+-------------+
1 row in set (0.00 sec)

mysql>   
  5.4)业务语句执行后的表的记录数：cnt_after
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

mysql>   
  5.5)计算该业务语句的真实影响行数(cnt_real)：
mysql> select concat('(row_business - (cnt_after - cnt_before) ) / 2 + (cnt_after - cnt_before) = ',round((5 - (4 - 3))/2 + (4 - 3))) as cnt_real;
+-------------------------------------------------------------------------------+
| cnt_real                                                                      |
+-------------------------------------------------------------------------------+
| (row_business - (cnt_after - cnt_before) ) / 2 + (cnt_after - cnt_before) = 3 |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
其结果刚好为3条，即符合我们的预期结果。

admin

{:soso_e179:}