Oracle 相当于 MySQL INSERT IGNORE？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9332360/
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
Oracle Equivalent to MySQL INSERT IGNORE?
提问by Bad Programmer
I need to update a query so that it checks that a duplicate entry does not exist before insertion. In MySQL I can just use INSERT IGNORE so that if a duplicate record is found it just skips the insert, but I can't seem to find an equivalent option for Oracle. Any suggestions?
我需要更新一个查询，以便它在插入之前检查重复条目不存在。在 MySQL 中，我可以只使用 INSERT IGNORE，这样如果发现重复记录，它就会跳过插入，但我似乎找不到 Oracle 的等效选项。有什么建议？
回答by a_horse_with_no_name
Check out the MERGE statement. This should do what you want - it's the WHEN NOT MATCHEDclause that will do this.
查看 MERGE 语句。这应该做你想做的 - 这是WHEN NOT MATCHED执行此操作的条款。
Do to Oracle's lack of support for a true VALUES() clause the syntax for a single record with fixed values is pretty clumsy though:
对于 Oracle 缺乏对真正的 VALUES() 子句的支持，具有固定值的单个记录的语法非常笨拙：
MERGE INTO your_table yt
USING (
SELECT 42 as the_pk_value,
'some_value' as some_column
FROM dual
) t on (yt.pk = t.the_pke_value)
WHEN NOT MATCHED THEN
INSERT (pk, the_column)
VALUES (t.the_pk_value, t.some_column);
A different approach (if you are e.g. doing bulk loading from a different table) is to use the "Error logging" facility of Oracle. The statement would look like this:
一种不同的方法（例如，如果您要从不同的表进行批量加载）是使用 Oracle 的“错误日志记录”工具。该语句将如下所示：
INSERT INTO your_table (col1, col2, col3)
SELECT c1, c2, c3
FROM staging_table
LOG ERRORS INTO errlog ('some comment') REJECT LIMIT UNLIMITED;
Afterwards all rows that would have thrown an error are available in the table errlog. You need to create that errlogtable (or whatever name you choose) manually before running the insert using DBMS_ERRLOG.CREATE_ERROR_LOG.
之后所有会抛出错误的行都在表中可用errlog。您需要errlog在使用DBMS_ERRLOG.CREATE_ERROR_LOG.
See the manual for details
详情请参阅手册
回答by Jon Heller
If you're on 11g you can use the hint IGNORE_ROW_ON_DUPKEY_INDEX:
如果您使用的是 11g，则可以使用提示IGNORE_ROW_ON_DUPKEY_INDEX：
SQL> create table my_table(a number, constraint my_table_pk primary key (a));
Table created.
SQL> insert /*+ ignore_row_on_dupkey_index(my_table, my_table_pk) */
2 into my_table
3 select 1 from dual
4 union all
5 select 1 from dual;
1 row created.
回答by Ben
I don't think there is but to save time you can attempt the insert and ignore the inevitable error:
我认为除了节省时间之外，您可以尝试插入并忽略不可避免的错误：
begin
insert into table_a( col1, col2, col3 )
values ( 1, 2, 3 );
exception when dup_val_on_index then
null;
end;
/
This will only ignore exceptions raised specifically by duplicate primary key or unique key constraints; everything else will be raised as normal.
这只会忽略由重复主键或唯一键约束引起的异常；其他一切都会照常提出。
If you don't want to do this then you have to select from the table first, which isn't really that efficient.
如果您不想这样做，那么您必须先从表中进行选择，这并不是那么有效。
回答by rt.jar
Another variant
另一个变种
Insert into my_table (student_id, group_id)
select distinct p.studentid, g.groupid
from person p, group g
where NOT EXISTS (select 1
from my_table a
where a.student_id = p.studentid
and a.group_id = g.groupid)
or you could do
或者你可以做
Insert into my_table (student_id, group_id)
select distinct p.studentid, g.groupid
from person p, group g
MINUS
select student_id, group_id
from my_table
回答by vinnyman
This one isn't mine, but came in really handy when using sqlloader:
这不是我的，但在使用 sqlloader 时非常方便：
create a view that points to your table:
CREATE OR REPLACE VIEW test_view AS SELECT * FROM test_tabcreate the trigger:
CREATE OR REPLACE TRIGGER test_trig INSTEAD OF INSERT ON test_view FOR EACH ROW BEGIN INSERT INTO test_tab VALUES (:NEW.id, :NEW.name); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL; END test_trig;and in the ctl file, insert into the view instead:
OPTIONS(ERRORS=0) LOAD DATA INFILE 'file_with_duplicates.csv' INTO TABLE test_view FIELDS TERMINATED BY ',' (id, field1)
创建一个指向您的表的视图：
CREATE OR REPLACE VIEW test_view AS SELECT * FROM test_tab创建触发器：
CREATE OR REPLACE TRIGGER test_trig INSTEAD OF INSERT ON test_view FOR EACH ROW BEGIN INSERT INTO test_tab VALUES (:NEW.id, :NEW.name); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL; END test_trig;并在 ctl 文件中，插入到视图中：
OPTIONS(ERRORS=0) LOAD DATA INFILE 'file_with_duplicates.csv' INTO TABLE test_view FIELDS TERMINATED BY ',' (id, field1)
回答by knagaev
A simple solution
一个简单的解决方案
insert into t1
select from t2
where not exists
(select 1 from t1 where t1.id= t2.id)
回答by dev-null
yet another "where not exists"-variant using dual...
另一个“不存在的地方” - 使用双重变体......
insert into t1(id, unique_name)
select t1_seq.nextval, 'Franz-Xaver' from dual
where not exists (select 1 from t1 where unique_name = 'Franz-Xaver');
回答by spootdev
How about simply adding an index with whatever fields you need to check for dupes on and say it must be unique? Saves a read check.
简单地添加一个包含任何字段的索引来检查是否存在欺骗，并说它必须是唯一的怎么样？保存读取检查。
