仅当记录不存在时才将SQL插入表中

问题描述：

我要运行一组查询，以便将某些数据插入SQL表中，但前提是要满足满足某些条件的记录.该表有4个字段:id(主)，fund_id，date和price

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, date and price

我在查询中有3个字段:fund_id，date和price.

I have 3 fields in the query: fund_id, date and 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'
    );

因此，我只想在与fund_id和date匹配的记录不存在的情况下插入数据.如果上述方法正确无误，那么每次实现都必须运行一条附加的select语句使我感到非常低效.

So I only want to insert the data if a record matching the fund_id and date does 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.

是否有更好的方法来实现上述目标?

Is there a better way of achieving the above?

为澄清起见，fund_id和date都不是唯一字段；共享相同的fund_id或日期的记录将存在，但没有记录应具有相同的fund_id和日期.

For clarification neither fund_id nor date are 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组成的表上创建一个复合唯一索引.

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_id and 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

这将为子查询提供更好的性能，并且表的结构更好.需要注意的是，您的唯一键列中不能包含NULL值，因为MySQL仍将它们视为值.

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.

仅当记录不存在时才将SQL插入表中

相关推荐