英文:
how to prevent events from inserting data that already exists in sql
问题
以下是翻译好的部分：
所以，我想要创建一个事件，该事件根据交易中的值向Daily_Profit插入值。
所以我已经使用了这个语法来获得一个唯一的值，希望不会有重复
ALTER TABLE Daily_Profit ADD UNIQUE unique (date, profit);
但问题是，来自表B的空值总是被添加。所以就像这样
date profit
NULL 10
NULL 10
NULL 10
20 50
20 30
30 12
这里是我的事件语法
delimiter //
create event profit
on schedule every 1 second
do begin
insert ignore into Daily_Profit
(select date, sum(total_price) from transaction group by date);
end //
delimiter ;
那么，是否有解决方案，以防止空值重复？
英文:
so i want to create an event, which inserts values into Daily_Profit based on values from transaction.
so I have used this syntax to get a unique value and hope there will be no duplication
ALTER TABLE Daily_Profit ADD UNIQUE unique (date, profit);
but the problem is, null values from table B are always added. So it's like this
date profit
NULL 10
NULL 10
NULL 10
20 50
20 30
30 12
and here my event syntax
delimiter //
create event profit
on schedule every 1 second
do begin
insert ignore into Daily_Profit
(select date, sum(total_price) from transaction group by date);
end //
delimiter ;
So, is there a solution so that the null value will not be duplicated?
答案1
得分: 0
你可以在事件中添加空值检查来插入逻辑。
分隔符 //
创建事件 profit
按照计划每秒执行
执行开始
忽略插入到 daily_profit (date, profit)
选择 t.date, coalesce(sum(t.total_price), 0)
从交易 t
左连接 每日利润 dp 在 t.date = dp.date
其中 dp.date 是空
按 t.date 分组;
结束 //
分隔符 ;
英文:
You can add null check to insert logic in the event
delimiter //
create event profit
on schedule every 1 second
do begin
insert ignore into daily_profit (date, profit)
select t.date, coalesce(sum(t.total_price), 0)
from transaction t
left join daily_profit dp on t.date = dp.date
where dp.date is null
group by t.date;
end //
delimiter ;
答案2
得分: 0
如果你想要排除NULL值不被插入到Daily_Profit表中，你可以修改你的事件查询以过滤掉日期为NULL的行。以下是更新后的事件语法：
DELIMITER //
CREATE EVENT profit
ON SCHEDULE EVERY 1 SECOND
DO
BEGIN
INSERT IGNORE INTO Daily_Profit
SELECT date, SUM(total_price)
FROM transaction
WHERE date IS NOT NULL
GROUP BY date;
END //
DELIMITER ;
通过在查询中添加"WHERE date IS NOT NULL"条件，你确保只有具有非NULL日期值的行才会被考虑插入到Daily_Profit表中。这样，NULL值就不会在结果中重复出现。
请注意，你在Daily_Profit表上定义的唯一性约束（ALTER TABLE Daily_Profit ADD UNIQUE unique (date, profit);）不会阻止插入NULL值，因为NULL值被视为彼此不同。如果你希望排除NULL值不被插入到profit列中，你可能需要修改表结构并向profit列添加一个NOT NULL约束。
英文:
If you want to exclude NULL values from being inserted into the Daily_Profit table, you can modify your event's query to filter out rows where the date is NULL. Here's an updated version of your event syntax:
DELIMITER //
CREATE EVENT profit
ON SCHEDULE EVERY 1 SECOND
DO
BEGIN
INSERT IGNORE INTO Daily_Profit
SELECT date, SUM(total_price)
FROM transaction
WHERE date IS NOT NULL
GROUP BY date;
END //
DELIMITER ;
By adding the WHERE date IS NOT NULL condition to the query, you ensure that only rows with a non-NULL date value will be considered for insertion into the Daily_Profit table. This way, the NULL values won't be duplicated in the result.
Please note that the uniqueness constraint you defined on the Daily_Profit table (ALTER TABLE Daily_Profit ADD UNIQUE unique (date, profit);) won't prevent NULL values from being inserted since NULL values are considered distinct from each other. If you want to exclude NULL values from being inserted into the profit column as well, you may need to modify the table schema and add a NOT NULL constraint to the profit column.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论