update和insert...on duplicate key

记录工作中根据sql返回值执行逻辑时遇到的问题。

• update的返回值,如果update语句执行成功，则 affected row是1。

hotspot_rural_mf_1> update rural_fans_0 set status=200 where rural_id=1 and uid=103
[2021-08-17 14:00:24] 1 row affected in 61 ms

• insert...on duplicate key update的返回值则有两种可能，如果表中没有记录，则会执行insert操作，返回值为1，如果表中有记录，则会执行update操作，返回值为2。

/* 记录不存在时 */
hotspot_rural_mf_1> insert into rural_fans_0 (`rural_id`,`uid`,`status`) values(1,104,200) on duplicate key update `status`= 200,`update_time`=now(3)
[2021-08-17 14:13:55] 1 row affected in 18 ms

/* 记录已存在时 */
hotspot_rural_mf_1> insert into rural_fans_0 (`rural_id`,`uid`,`status`) values(1,104,200) on duplicate key update `status`= 200,`update_time`=now(3)
[2021-08-17 14:14:07] 2 rows affected in 43 ms

注意

• ==但是也有记录存在，但在insert...on duplicate key操作时affected row是1的情况，比如快速的连续执行两次或者多次==。

/* 记录已存在，但快速连续执行多次时 */
hotspot_rural_mf_1> insert into rural_fans_0 (`rural_id`,`uid`,`status`) values(1,103,200) on duplicate key update `status`= 200,`update_time`=now(3)
[2021-08-17 14:18:07] 2 rows affected in 15 ms

hotspot_rural_mf_1> insert into rural_fans_0 (`rural_id`,`uid`,`status`) values(1,103,200) on duplicate key update `status`= 200,`update_time`=now(3)
[2021-08-17 14:18:08] 1 row affected in 26 ms

• 解决方法：将表中时间相关得字段设置为 DATETIME(3)...CURRENT_TIMESTAMP(3)

alter table rural_fans_0 modify `update_time` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间'


/* 修改字段定义后重新测试 */
/* 快速连续执行多次的返回值都是对的 */

hotspot_rural_mf_1> insert into rural_fans_0 (`rural_id`,`uid`,`status`) values(1,103,200) on duplicate key update `status`= 200,`update_time`=now(3)
[2021-08-17 14:25:12] 2 rows affected in 43 ms
hotspot_rural_mf_1> insert into rural_fans_0 (`rural_id`,`uid`,`status`) values(1,103,200) on duplicate key update `status`= 200,`update_time`=now(3)
[2021-08-17 14:25:13] 2 rows affected in 15 ms
hotspot_rural_mf_1> insert into rural_fans_0 (`rural_id`,`uid`,`status`) values(1,103,200) on duplicate key update `status`= 200,`update_time`=now(3)
[2021-08-17 14:25:13] 2 rows affected in 16 ms
hotspot_rural_mf_1> insert into rural_fans_0 (`rural_id`,`uid`,`status`) values(1,103,200) on duplicate key update `status`= 200,`update_time`=now(3)
[2021-08-17 14:25:14] 2 rows affected in 12 ms
hotspot_rural_mf_1> insert into rural_fans_0 (`rural_id`,`uid`,`status`) values(1,103,200) on duplicate key update `status`= 200,`update_time`=now(3)
[2021-08-17 14:25:14] 2 rows affected in 10 ms
hotspot_rural_mf_1> insert into rural_fans_0 (`rural_id`,`uid`,`status`) values(1,103,200) on duplicate key update `status`= 200,`update_time`=now(3)
[2021-08-17 14:25:15] 2 rows affected in 14 ms
hotspot_rural_mf_1> insert into rural_fans_0 (`rural_id`,`uid`,`status`) values(1,103,200) on duplicate key update `status`= 200,`update_time`=now(3)
[2021-08-17 14:25:15] 2 rows affected in 11 ms