返回提示词目录第 1386 页

编写数据库插入或更新语句

按数据库版本与表结构生成冲突更新语句,说明唯一键、并发行为及边界测试。

完整提示词

<context>
A developer needs an insert-or-update on {{database}}. Upserts look simple and go wrong in predictable ways. They only work against a unique constraint or unique index that actually exists; without one, `ON CONFLICT` errors and a "select then insert" pattern creates duplicates under concurrency. The syntax and semantics differ by engine: Postgres and SQLite use `INSERT ... ON CONFLICT (cols) DO UPDATE SET col = EXCLUDED.col`; MySQL uses `INSERT ... ON DUPLICATE KEY UPDATE`, which fires on any unique key on the table (not just the one you meant), and newer versions prefer a row alias over the deprecated `VALUES()` function; SQL Server's `MERGE` is not race-safe by default and needs `HOLDLOCK` (serializable) or an update-then-insert pattern with the right locks. Other traps: updating columns that should never change (created_at, owner), auto-increment gaps in MySQL, a Postgres batch that touches the same key twice failing with "cannot affect row a second time", nullable columns in the unique key (NULLs are distinct unless the engine supports NULLS NOT DISTINCT), and needing to know whether a row was inserted or updated.
</context>

<task>
Goal: {{goal}}

<table_ddl>
{{table_ddl}}
</table_ddl>
{{#batch}}Volume: {{batch}}{{/batch}}

1. Name the conflict target: the unique constraint or index the upsert relies on. If the DDL has none that matches the goal, say so and give the DDL to add it (and how to clean existing duplicates first, read-only query only).
2. Write the upsert in the exact syntax for {{database}}: which columns are inserted, which are updated on conflict, which are left untouched, and any condition on the update (for example only when the incoming version or timestamp is newer, so old messages do not overwrite new data).
3. Return what the caller needs: the row id, and inserted-versus-updated if useful (for example `RETURNING` with `xmax = 0` in Postgres, affected-rows counts in MySQL, `OUTPUT $action` in SQL Server), with the caveats of each.
4. For batches, give the multi-row form, deduplicate keys within the batch first, sort rows by the conflict key so concurrent batches lock in the same order (fewer deadlocks), and give a sensible batch size.
5. Explain concurrency: what happens when two sessions upsert the same key at once on this engine, whether it can deadlock or raise a unique violation, and what to retry if so.
6. Give a short test: insert new, upsert existing, upsert with an older version (if conditional), and two concurrent sessions on the same key.
</task>

<constraints>
- Use only syntax that exists in the stated engine and version; if a feature depends on the version (row aliases in MySQL 8.0.19+, NULLS NOT DISTINCT in Postgres 15+, MERGE in Postgres 15+), say so.
- Never suggest select-then-insert in application code as the upsert.
- Do not update primary keys, creation timestamps or ownership columns on conflict unless the goal asks for it.
- Parameterise values; no string-built SQL.
{{> output/uncertainty}}
</constraints>

<output_format>
## Conflict key
The constraint used, or the DDL to add it.

## Query
SQL in one code block, with a line on each non-obvious clause.

## Behaviour under concurrency
Three to six lines.

## Edge cases
Bullets: NULLs in the key, duplicate keys in a batch, conditional updates, side effects (auto-increment gaps, triggers).

## Test
SQL or steps for the four checks.
</output_format>

可替换内容

  • {{database}}
  • {{goal}}
  • {{table_ddl}}
  • {{#batch}}
  • {{batch}}
  • {{/batch}}
  • {{> output/uncertainty}}

分类与标签

编程开发、数据库写入、冲突更新、唯一约束、批量处理、并发控制

前往 AI 图片创作