如何避免SQL事件插入重复数据？解决NULL值重复插入问题

问题根源 #

SQL里NULL不等于任何值，包括另一个NULL，所以你添加的UNIQUE(date, profit)约束对date为NULL的行起不到去重作用——数据库会把每个(NULL, 10)当成不同的记录，自然会重复插入。

解决方案 #

方法1：插入时将NULL替换为占位符

在插入的SELECT语句里，用IFNULL()或COALESCE()把NULL的date替换成一个固定值（比如选一个不会和正常业务日期冲突的特殊值，如'1970-01-01'），让唯一约束能正常生效：

delimiter //
create event profit
on schedule every 1 second
do begin
insert ignore into Daily_Profit
(select IFNULL(date, '1970-01-01'), sum(total_price) from transaction group by date);
end //
delimiter ;

注意：如果Daily_Profit的date是日期类型，要确保占位符是合法日期；如果是数值类型，就用特殊数字比如-1。

方法2：创建基于函数的唯一索引（MySQL 8.0+支持）

如果不想修改插入逻辑，可以给表加一个函数索引，把NULL的date统一转换成固定值后再做唯一约束：

ALTER TABLE Daily_Profit ADD UNIQUE KEY idx_unique_date_profit (IFNULL(date, '1970-01-01'), profit);

数据库会自动把date为NULL的记录转换成占位符后判断唯一性，不用改动事件的插入语句。

方法3：过滤NULL的date（业务允许的话）

如果业务里不需要统计date为NULL的记录，直接在SELECT里过滤掉：

insert ignore into Daily_Profit
(select date, sum(total_price) from transaction where date is not null group by date);

内容的提问来源于stack exchange，提问作者Chezar