mysql3108_mysql不存在插入，存在更新(insert duplicate测试)

总结

1、在update中，如果这样写”字段=字段“，即使数据有变化也不会更新，可以改为这样”字段=value(字段)“

2、可以将一组字段(多个字段)设为unique(这些字段默认值千万别是null，可以设为‘’)，可以指定这组一个或多个来更新数据，没指定的unique字段其值是默认值

基于mysql 5.7.32测试，如下

create table dsp_report(

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`publisher_id` varchar(64) COLLATE utf8mb4_bin DEFAULT '',

`advertiser_id` varchar(64) COLLATE utf8mb4_bin DEFAULT '',

`opera_impr_count` bigint(11) DEFAULT '-1',

PRIMARY KEY (`id`),

UNIQUE KEY `idx_unique_r_day_billing_report` (`publisher_id`,`advertiser_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

insert into dsp_report(publisher_id,advertiser_id,opera_impr_count) values('pub236088034304','adv1075923487232',111111);

insert into dsp_report(publisher_id,opera_impr_count) values('pub236088034304',222222);

select * from dsp_report;

-- idpublisher_id advertiser_id opera_impr_count

-- 10pub236088034304adv1075923487232111111

-- 11pub236088034304 222222

insert into dsp_report(publisher_id,opera_impr_count) values('pub236088034304',333333) on duplicate key update opera_impr_count=opera_impr_count;

select * from dsp_report;

-- idpublisher_id advertiser_id opera_impr_count

-- 10pub236088034304adv1075923487232111111

-- 11pub236088034304 222222

insert into dsp_report(publisher_id,opera_impr_count) values('pub236088034304',4444444) on duplicate key update opera_impr_count=values(opera_impr_count);

select * from dsp_report;

-- idpublisher_id advertiser_id opera_impr_count

-- 10pub236088034304adv1075923487232111111

-- 11pub236088034304 4444444

注意：本文归作者所有，未经作者允许，不得转载