MySQL 仅当记录不存在时 SQL 插入到表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16460397/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Insert into table only if record doesn't exist
提问by harryg
I want to run a set of queries to insert some data into an SQL table but only if the record satisfying certain criteria are met. The table has 4 fields: id(primary), fund_id, dateand price
我想运行一组查询以将一些数据插入 SQL 表，但前提是满足特定条件的记录。该表有 4 个字段：id(primary), fund_id,date和price
I have 3 fields in the query: fund_id, dateand price.
我在查询中有 3 个字段：fund_id,date和price.
So my query would go something like this:
所以我的查询会是这样的：
INSERT INTO funds (fund_id, date, price)
VALUES (23, '2013-02-12', 22.43)
WHERE NOT EXISTS (
SELECT *
FROM funds
WHERE fund_id = 23
AND date = '2013-02-12'
);
So I only want to insert the data if a record matching the fund_idand datedoes not already exist. If the above is correct it strikes me as quite an inefficient way of achieving this as an additional select statement must be run each time.
所以我只想在匹配fund_id和的记录date不存在时插入数据。如果以上是正确的，我觉得这是一种非常低效的实现方式，因为每次都必须运行额外的 select 语句。
Is there a better way of achieving the above?
有没有更好的方法来实现上述目标？
Edit: For clarification neither fund_idnor dateare unique fields; records sharing the same fund_id or date will exist but no record should have both the same fund_id and date as another.
编辑：为了澄清，fund_id也不date是唯一字段；共享相同 fund_id 或 date 的记录将存在，但没有记录应该与另一个记录具有相同的 fund_id 和 date。
采纳答案by harryg
Although the answer I originally marked as chosen is correct and achieves what I asked there is a better way of doing this (which others acknowledged but didn't go into). A composite unique index should be created on the table consisting of fund_idand date.
尽管我最初标记为已选择的答案是正确的并且达到了我所要求的，但有一种更好的方法可以做到这一点（其他人承认但没有进入）。应在由fund_id和组成的表上创建复合唯一索引date。
ALTER TABLE funds ADD UNIQUE KEY `fund_date` (`fund_id`, `date`);
Then when inserting a record add the condition when a conflict is encountered:
然后在插入记录时添加遇到冲突时的条件：
INSERT INTO funds (`fund_id`, `date`, `price`)
VALUES (23, DATE('2013-02-12'), 22.5)
ON DUPLICATE KEY UPDATE `price` = `price`; --this keeps the price what it was (no change to the table) or:
INSERT INTO funds (`fund_id`, `date`, `price`)
VALUES (23, DATE('2013-02-12'), 22.5)
ON DUPLICATE KEY UPDATE `price` = 22.5; --this updates the price to the new value
This will provide much better performance to a sub-query and the structure of the table is superior. It comes with the caveat that you can't have NULL values in your unique key columns as they are still treated as values by MySQL.
这将为子查询提供更好的性能，并且表的结构更优越。需要注意的是，您的唯一键列中不能有 NULL 值，因为它们仍被 MySQL 视为值。
回答by Trinimon
This might be a simple solution to achieve this:
这可能是实现此目的的简单解决方案：
INSERT INTO funds (ID, date, price)
SELECT 23, DATE('2013-02-12'), 22.5
FROM dual
WHERE NOT EXISTS (SELECT 1
FROM funds
WHERE ID = 23
AND date = DATE('2013-02-12'));
p.s.alternatively (if IDa primary key):
ps或者（如果ID是主键）：
INSERT INTO funds (ID, date, price)
VALUES (23, DATE('2013-02-12'), 22.5)
ON DUPLICATE KEY UPDATE ID = 23; -- or whatever you need
see this Fiddle.
看到这个小提琴。
回答by gillyspy
Assuming you cannot modify DDL (to create a unique constraint) or are limited to only being able to write DML then check for a null on filtered result of your values against the whole table
假设您无法修改 DDL（以创建唯一约束）或仅限于只能编写 DML，然后针对整个表检查您的值的过滤结果是否为空
FIDDLE
小提琴
insert into funds (ID, date, price)
select
T.*
from
(select 23 ID, '2013-02-12' date, 22.43 price) T
left join
funds on funds.ID = T.ID and funds.date = T.date
where
funds.ID is null
