哪种SQL模式更快，以避免插入重复的行？ [英] Which SQL pattern is faster to avoid inserting duplicate rows?

问题描述

我知道有两种方式可以插入而不重复。第一个是使用 WHERE NOT EXISTS 子句：

  INSERT INTO table_name （col1，col2，col3）
 SELECT％s，％s，％s 
 WHERE NOT EXISTS（
 SELECT * FROM table_name AS T 
 WHERE T.col1 =％s 
 AND T.col2 =％s）
  

另一个是做一个 LEFT JOIN ：

  INSERT INTO table_name（col1，col2，col3）
 SELECT ％s，％s，％s 
 FROM（SELECT％s，％s，％s）A 
 LEFT JOIN table_name B 
 ON B.COL1 =％s 
 AND B .COL2 =％s 
 WHERE B.id IS NULL 
 LIMIT 1 
  

有一个一般的规则，一个比另一个更快，还是取决于表？是否有不同的方式比两个都好？

解决方案

我建议定义一个UNIQUE约束需要是唯一的（在这种情况下是col1& col2），然后只是做INSERT。根据需要处理异常。

---

重新评论需要回滚的异常，PostgreSQL的解决方案是设置一个事务保存点，然后再尝试可能导致异常的插入。

请参阅：

• 如果您遇到例外情况，请回到保存点。 http://www.postgresql.org/docs/current/static/ sql-savepoint.html


• http://www.postgresql.org/docs/current/static/sql-rollback-to.html

I know of two ways to insert without duplication. The first is using a WHERE NOT EXISTS clause:

INSERT INTO table_name (col1, col2, col3)
SELECT %s, %s, %s
WHERE NOT EXISTS (
    SELECT * FROM table_name AS T
    WHERE T.col1 = %s
      AND T.col2 = %s)

the other is doing a LEFT JOIN:

INSERT INTO table_name (col1, col2, col3)
SELECT %s, %s, %s
FROM ( SELECT %s, %s, %s ) A
LEFT JOIN table_name B
ON  B.COL1 = %s
AND B.COL2 = %s
WHERE B.id IS NULL
LIMIT 1

Is there a general rule as to one being faster than the other, or does it depend on the tables? Is there a different way which is better than both?

解决方案

I would recommend defining a UNIQUE constraint on the column(s) you need to be unique (col1 & col2 in this case), and then just do the INSERT. Handle exceptions as needed.

---

Re your comment about the exception demanding a rollback, the solution for PostgreSQL is to set a transaction savepoint before you try the insert that may cause an exception. If you get the exception, rollback to the savepoint.

See:

• http://www.postgresql.org/docs/current/static/sql-savepoint.html
• http://www.postgresql.org/docs/current/static/sql-rollback-to.html

这篇关于哪种SQL模式更快，以避免插入重复的行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！