执行SQL'不存在时插入'的最有效方法 [英] Most efficient way to do a SQL 'INSERT IF NOT EXISTS'

问题描述

以下哪项效果更好?

(1) **INSERT IGNORE**
cursor.execute('INSERT IGNORE INTO table VALUES (%s,%s)')

(2) **SELECT or CREATE**
cursor.execute('SELECT 1 FROM table WHERE id=%s')
if not cursor.fetchone():
    cursor.execute('INSERT INTO table VALUES (%s,%s)')

我必须数百万次执行此操作，所以我希望找到此模式的最佳性能.最好是哪一个?为什么?

I have to do this patter millions of time so I'm looking to find the best performance for this pattern. Which one is preferably? Why?

推荐答案

出于多种原因，insert ignore是更好的方法.

The insert ignore is the better method, for several reasons.

就性能而言，仅编译和执行一个查询，而不是两个.这样可以节省将内容移入和移出数据库的开销.

In terms of performance, only one query is being compiled and executed, rather than two. This saves the overhead of moving stuff in and out of the database.

就维护而言，只有一个查询更可维护，因为逻辑全都放在一个地方.例如，如果添加了where子句，则您很可能会错过在两个单独的查询中添加它的机会.

In terms of maintenance, only having one query is more maintainable, because the logic is all in one place. If you added a where clause, for instance, you would be more likely to miss adding it in two separate queries.

就准确性而言，只有一个查询应该没有(或者至少少很多)竞赛条件的机会.如果在select和insert之间插入一行，那么您仍然会收到错误消息.

In terms of accuracy, only one query should have no (or at least many fewer) opportunities for race conditions. If a row is inserted between the select and insert, then you will still get an error.

但是，insert . . . on duplicate key update比insert ignore好.后者仅避免了重复问题的错误. insert ignore可能会忽略您实际上关心的错误.

However, better than insert ignore is insert . . . on duplicate key update. The latter only avoids the error for duplication problems. insert ignore might be ignoring errors that you actually care about.

顺便说一句，无论如何，您应该检查语句中的错误.

By the way, you should be checking for errors from the statement anyway.

这篇关于执行SQL'不存在时插入'的最有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！