INSERT .. ON DUPLICATE KEY IGNORE
适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版
MySQL 数据库也支持 INSERT IGNORE INTO 子句。jOOQ 使用更方便的 SQL 语法变体 ON DUPLICATE KEY IGNORE 支持此功能
// Add a new author called "Koontz" with ID 3.
// If that ID is already present, ignore the INSERT statement
create.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.LAST_NAME)
.values(3, "Koontz")
.onDuplicateKeyIgnore()
.execute();
如果底层数据库没有任何“忽略”失败的 INSERT 语句的方法（例如，MySQL 通过 INSERT IGNORE），jOOQ 可以使用 MERGE 语句 或使用 INSERT .. SELECT WHERE NOT EXISTS 来模拟该语句。
MySQL 的 INSERT IGNORE 语句忽略的约束冲突不仅仅是重复键，因此模拟不完全等效，请参阅 #5211
方言支持
此示例使用 jOOQ
insertInto(AUTHOR, AUTHOR.ID, AUTHOR.LAST_NAME).values(3, "X").onDuplicateKeyIgnore()
翻译成以下特定方言的表达式
访问
INSERT INTO AUTHOR (ID, LAST_NAME)
SELECT t.v0, t.v1
FROM (
SELECT
3 v0,
'X' v1
FROM (
SELECT count(*) dual
FROM MSysResources
) AS dual
WHERE NOT EXISTS (
SELECT 1
FROM AUTHOR
WHERE AUTHOR.ID = 3
)
) t
ASE, ClickHouse, SQLDataWarehouse, Trino, Vertica
INSERT INTO AUTHOR (ID, LAST_NAME)
SELECT t.v0, t.v1
FROM (
SELECT
3 v0,
'X' v1
WHERE NOT EXISTS (
SELECT 1
FROM AUTHOR
WHERE AUTHOR.ID = 3
)
) t
Aurora MySQL, MariaDB, MySQL
INSERT IGNORE INTO AUTHOR (ID, LAST_NAME) VALUES ( 3, 'X' )
Aurora Postgres, CockroachDB, DuckDB, Postgres, SQLite, YugabyteDB
INSERT INTO AUTHOR (ID, LAST_NAME) VALUES ( 3, 'X' ) ON CONFLICT DO NOTHING
BigQuery
INSERT INTO AUTHOR (ID, LAST_NAME)
SELECT t.v0, t.v1
FROM (
SELECT
3 v0,
'X' v1
FROM UNNEST([STRUCT(1 AS dual)]) AS dual
WHERE NOT EXISTS (
SELECT 1
FROM AUTHOR
WHERE AUTHOR.ID = 3
)
) t
Databricks, Redshift, Snowflake
MERGE INTO AUTHOR USING ( SELECT 3, 'X' ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
DB2
MERGE INTO AUTHOR USING ( SELECT 3, 'X' FROM SYSIBM.DUAL ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
Derby
MERGE INTO AUTHOR USING SYSIBM.SYSDUMMY1 ON AUTHOR.ID = 3 WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( 3, 'X' )
Exasol, MemSQL
INSERT INTO AUTHOR (ID, LAST_NAME)
SELECT t.v0, t.v1
FROM (
SELECT
3 v0,
'X' v1
FROM DUAL
WHERE NOT EXISTS (
SELECT 1
FROM AUTHOR
WHERE AUTHOR.ID = 3
)
) t
Firebird
MERGE INTO AUTHOR USING ( SELECT 3, 'X' FROM RDB$DATABASE ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
H2
MERGE INTO AUTHOR
USING (
SELECT
3 ID,
'X' LAST_NAME
) t
ON AUTHOR.ID = t.ID
WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME)
VALUES (
t.ID,
t.LAST_NAME
)
Hana
MERGE INTO AUTHOR
USING (
(
SELECT
3 ID,
'X' LAST_NAME
FROM SYS.DUMMY
)
) t
ON AUTHOR.ID = t.ID
WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME)
VALUES (
t.ID,
t.LAST_NAME
)
HSQLDB
MERGE INTO AUTHOR USING ( SELECT 3, 'X' FROM (VALUES (1)) AS dual (dual) ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
Informix
INSERT INTO AUTHOR (ID, LAST_NAME)
SELECT t.v0, t.v1
FROM (
SELECT
3 v0,
'X' v1
FROM (
SELECT 1 AS dual
FROM systables
WHERE (tabid = 1)
) AS dual
WHERE NOT EXISTS (
SELECT 1
FROM AUTHOR
WHERE AUTHOR.ID = 3
)
) t
Oracle
MERGE INTO AUTHOR
USING (
(
SELECT
3 ID,
'X' LAST_NAME
)
) t
ON (AUTHOR.ID = t.ID)
WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME)
VALUES (
t.ID,
t.LAST_NAME
)
SQLServer
MERGE INTO AUTHOR USING ( SELECT 3, 'X' ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME );
Sybase
MERGE INTO AUTHOR USING ( SELECT 3, 'X' FROM SYS.DUMMY ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
Teradata
MERGE INTO AUTHOR
USING (
SELECT 3, 'X'
FROM (
SELECT 1 AS "dual"
) AS "dual"
) t (ID, LAST_NAME)
ON AUTHOR.ID = t.ID
WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME)
VALUES (
t.ID,
t.LAST_NAME
)
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？ 我们很乐意听取您的意见！