SQL 如何在不存在的地方插入？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4707063/
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
How can I do an insert where not exists?
提问by froadie
I'd like to combine an insert query with a "where not exists" so as not to violate PK constraints. However, syntax such as the following gives me an Incorrect syntax near the keyword 'WHERE'error -
我想将插入查询与“不存在的地方”结合起来，以免违反 PK 约束。但是，如下所示的语法给了我一个Incorrect syntax near the keyword 'WHERE'错误 -
INSERT INTO myTable(columns...)
VALUES(values...)
WHERE NOT EXISTS
(SELECT *
FROM myTable
WHERE pk_part1 = value1,
AND pk_part2 = value2)
How can I accomplish this?
我怎样才能做到这一点？
(In general, can you combine an insert with a where clause?)
（通常，您可以将插入与 where 子句结合使用吗？）
回答by bernd_k
INSERT INTO myTable(columns...)
Select values...
WHERE NOT EXISTS
(SELECT *
FROM myTable
WHERE pk_part1 = value1,
AND pk_part2 = value2)
Edit:After reading martins link, If admit, that the best solution is:
编辑：阅读马丁斯链接后，如果承认，最好的解决方案是：
BEGIN TRY
INSERT INTO myTable(columns...)
values( values...)
END TRY
BEGIN CATCH
IF ERROR_NUMBER() <> 2627
RAISERROR etc
END CATCH;
回答by OMG Ponies
The simplest way to keep a unique list of values is to either a) set the column(s) as the primary key or b) create a unique constraint on the column(s). Either of these would result in an error when attempting to insert/update values to something that already exists in the table, when a NOT EXISTS/etc would fail silently -- no error, query will execute properly.
保持唯一值列表的最简单方法是 a) 将列设置为主键或 b) 在列上创建唯一约束。当尝试向表中已存在的内容插入/更新值时，其中任何一个都会导致错误，当 NOT EXISTS/etc 将静默失败时——没有错误，查询将正确执行。
That said, use an INSERT/SELECT (don't include the VALUES portion):
也就是说，使用 INSERT/SELECT（不包括 VALUES 部分）：
INSERT INTO myTable(columns...)
SELECT [statically defined values...]
FROM ANY_TABLE
WHERE NOT EXISTS (SELECT NULL
FROM myTable
WHERE pk_part1 = value1
AND pk_part2 = value2)
回答by Tom Stock
None of the examples worked for me... so I suggest this example:
没有一个例子对我有用......所以我建议这个例子：
INSERT INTO database_name.table_name(column_name)
SELECT column_name
FROM database_name.table_name
WHERE NOT EXISTS (SELECT NULL
FROM database_name.table_name
WHERE column_name = 'Column Value')
回答by Dominik Goltermann
mysql has the insert ignore query:
mysql 有插入忽略查询：
If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued. Data conversions that would trigger errors abort the statement if IGNORE is not specified. With IGNORE, invalid values are adjusted to the closest values and inserted; warnings are produced but the statement does not abort. You can determine with the mysql_info() C API function how many rows were actually inserted into the table.
如果使用 IGNORE 关键字，则在执行 INSERT 语句时发生的错误将被视为警告。例如，如果没有 IGNORE，复制表中现有 UNIQUE 索引或 PRIMARY KEY 值的行会导致重复键错误并且语句被中止。使用 IGNORE，仍然不会插入该行，但不会发出错误。如果未指定 IGNORE，则会触发错误的数据转换会中止语句。使用 IGNORE，将无效值调整为最接近的值并插入；产生警告但语句不会中止。您可以使用 mysql_info() C API 函数确定实际插入到表中的行数。
http://dev.mysql.com/doc/refman/5.0/en/insert.html
http://dev.mysql.com/doc/refman/5.0/en/insert.html
ON DUPLICATE KEY UPDATE is also available
ON DUPLICATE KEY UPDATE 也可用
