mariadb-insert
/mariadb-insert
Opisuje składnię i zachowanie INSERT w MariaDB: RETURNING, ON DUPLICATE KEY UPDATE, INSERT IGNORE, INSERT SELECT, wstawianie do partycji oraz semantykę LAST_INSERT_ID() po wstawieniu wielu wierszy.
- Repozytorium
- ai-plugins ↗
- Licencja
- LGPL
- Ostatnia zmiana repozytorium
- 24.10.2026
Ocena i bezpieczeństwo
- Pliki w paczce
- 1
- Skrypty
- 0
- Komentarze HTML
- 0
- Adresy URL w SKILL.md
- 7
Brak sygnałów. Repozytorium zmienione 24.10.2026, bez odwołań do wycofanych modeli i API.
LGPL, otwarta.
Co robi i kiedy
Opisuje składnię i zachowanie INSERT w MariaDB: RETURNING, ON DUPLICATE KEY UPDATE, INSERT IGNORE, INSERT SELECT, wstawianie do partycji oraz semantykę LAST_INSERT_ID() po wstawieniu wielu wierszy.
Oryginalny opis (EN)
MariaDB-specific syntax and behavior for INSERT — RETURNING to get inserted rows in one round trip, INSERT … ON DUPLICATE KEY UPDATE with VALUE()/VALUES(), INSERT IGNORE warning and error-coercion behavior, INSERT … SELECT, the SET form, PARTITION targeting, multi-row VALUES, priority/DELAYED modifiers and their engine limits, and LAST_INSERT_ID() semantics after multi-row inserts. Use when writing, generating, or reviewing INSERT statements that target MariaDB, or when an upsert is needed.
Jak działa
- What LLMs Often Miss
- RETURNING
- ON DUPLICATE KEY UPDATE (the real upsert)
- INSERT IGNORE
- Forms and modifiers
- See Also
Podgląd SKILL.md
---
name: mariadb-insert
description: "MariaDB-specific syntax and behavior for INSERT — RETURNING to get inserted rows in one round trip, INSERT … ON DUPLICATE KEY UPDATE with VALUE()/VALUES(), INSERT IGNORE warning and error-coercion behavior, INSERT … SELECT, the SET form, PARTITION targeting, multi-row VALUES, priority/DELAYED modifiers and their engine limits, and LAST_INSERT_ID() semantics after multi-row inserts. Use when writing, generating, or reviewing INSERT statements that target MariaDB, or when an upsert is needed."
---
# INSERT in MariaDB
*Last updated: 2026-06-24*
This skill covers the **delta between standard SQL `INSERT` and MariaDB's**. It assumes the agent already knows the standard forms (`INSERT INTO t (cols) VALUES (…)` and `INSERT INTO t SELECT …`). Everything below documents MariaDB-specific syntax, duplicate-key handling, the `RETURNING` extension, and the most common LLM traps.
> **Default context:** Assume MariaDB **11.8 LTS** (GA May 2025) unless the user states another version. Anything available in current LTS branches (10.6, 10.11, 11.4, 11.8) is shown without a "since" annotation; only newer-than-10.6 features carry one.
## What LLMs Often Miss
| If the agent writes / assumes… | …prefer the MariaDB form |
|---|---|
| `INSERT …` then a separate `SELECT` (or `LAST_INSERT_ID()` gymnastics) to see what was inserted | `INSERT INTO t (…) VALUES (…) RETURNING id, created_at, …` returns the inserted rows in one statement. Supports expressions, stored functions, and single-row subqueries (no aggregates) |
| To do an "upsert," reach for `REPLACE` | Use `INSERT … ON DUPLICATE KEY UPDATE` — it updates the existing row in place, preserving unmentioned columns and not churning `AUTO_INCREMENT` or firing `DELETE` triggers. `REPLACE` deletes and re-inserts (see `mariadb-replace`) |
| Inside `ON DUPLICATE KEY UPDATE`, only knows `VALUES(col)` to reference the would-be-inserted value | Both work; `VALUE(col)` is the current canonical name, `VALUES(col)` the older alias. Note `VALUES(col)`/`VALUE(col)` is **only** valid inside `ON DUPLICATE KEY UPDATE` — a syntax error elsewhere |
| Expects `INSERT IGNORE` to skip duplicates **silently** | Each skipped row raises a **warning** (e.g. code 1062) — check `SHOW WARNINGS`. The fully-silent legacy behavior is gated behind `OLD_MODE=NO_DUP_KEY_WARNINGS_WITH_IGNORE`, which is deprecated since 11.3 |
| Thinks `INSERT IGNORE` only swallows duplicate-key errors | It downgrades **all** errors to warnings — a missing `NOT NULL` default (1364), out-of-range/invalid values (coerced to the closest valid value), etc. Easy to mask real data problems with it |
| Combines `INSERT IGNORE … ON DUPLICATE KEY UPDATE` (or `DELAYED … ON DUPLICATE KEY UPDATE`) expecting both to apply | `IGNORE` and `DELAYED` are **silently ignored** when `ON DUPLICATE KEY UPDATE` is present |
| Reads "2 rows affected" from `ON DUPLICATE KEY UPDATE` as 2 rows inserted | The count is **1 per row inserted, 2 per existing row updated** (0 if an update changed nothing). So a batch reporting "2" inserted one of nothing — it updated one |
| Expects `LAST_INSERT_ID()` after a multi-row INSERT to give the **last** generated id | It returns the `AUTO_INCREMENT` value of the **first** successfully inserted row. Derive the others by offset, or use `RETURNING id` |
| Uses `INSERT DELAYED` as fire-and-forget on any table | `DELAYED` is honored only on table-lock engines (MyISAM, Aria, MEMORY, ARCHIVE, BLACKHOLE) and errors (1616) elsewhere; it's also a no-op on replicas, in stored programs, and on partitioned tables. Treat it as legacy — prefer a normal INSERT (and `LAST_INSERT_ID()` isn't available after a `DELAYED` insert) |
| `INSERT … SELECT` reading from the same table it inserts into | Not allowed — the `SELECT` source can't be the `INTO` target. Stage through a temporary table |
| Adds `LOW_PRIORITY` / `HIGH_PRIORITY` to speed up an InnoDB insert | These only affect table-lock engines; no effect on InnoDB. `HIGH_PRIORITY` isn't even valid in the `INSERT … SELECT` form |
| Assumes partition targeting isn't available | `INSERT INTO t PARTITION (p0, p1) VALUES (…)` restricts the insert to named partitions (errors if a row doesn't belong) |
## `RETURNING`
Return data from the rows just inserted, in a single statement:
```sql
INSERT INTO orders (cust_id, total) VALUES (42, 99.50), (43, 12.00)
RETURNING id, cust_id, total * 1.2 AS gross;
```
Allowed: column references, expressions, stored functions, single-row/single-column subqueries, and prepared statements. **Not** allowed: aggregate functions. For a plain count, use `ROW_COUNT()`. `RETURNING` is a MariaDB extension — not in standard SQL.
## `ON DUPLICATE KEY UPDATE` (the real upsert)
```sql
INSERT INTO inventory (sku, qty) VALUES ('A1', 5), ('B2', 3)
ON DUPLICATE KEY UPDATE qty = qty + VALUE(qty);
```
- Fires when an inserted row would collide with a `PRIMARY KEY` or `UNIQUE` index. The `UPDATE` clause runs against the existing row; unmentioned columns are left untouched.
- `VALUE(col)` (alias `VALUES(col)`) inside the `UPDATE` clause = the value that *would have been* inserted for that column. Bare column names refer to the existing row.
- If a row matches more than one unique index, only the **first** matched index's row is updated — ambiguous on multi-unique tables; design around it.
This is almost always what's wanted over `REPLACE`. See `mariadb-replace` for the contrast.
## `INSERT IGNORE`
```sql
INSERT IGNORE INTO t (id, name) VALUES (1, 'a'), (1, 'dup'), (2, 'b');
SHOW WARNINGS; -- the skipped duplicate surfaces here
```
Downgrades errors to warnings rather than aborting the statement. Useful for best-effort bulk loads, but it masks duplicate-key collisions, missing defaults, and value coercion alike — don't use it to paper over a schema mismatch.
## Forms and modifiers
```sql
-- SET form (one row, named assignments):
INSERT INTO person SET first_name = 'John', last_name = 'Doe';
-- Multi-row VALUES (VALUES and VALUE are interchangeable):
INSERT INTO t VALUES (1, 'a'), (2, 'b'), (3, 'c');
-- INSERT ... SELECT (source table ≠ target table):
INSERT INTO archive (id, payload) SELECT id, payload FROM live WHERE ts < '2025-01-01';
-- Partition targeting:
INSERT INTO t PARTITION (p0, p1) VALUES (…);
```
Priority/queue modifiers: `[LOW_PRIORITY | DELAYED | HIGH_PRIORITY]` on the `VALUES`/`SET` forms; the `SELECT` form takes only `[LOW_PRIORITY | HIGH_PRIORITY]` (no `DELAYED`). All are table-lock-engine concerns — typically irrelevant on InnoDB.
## See Also
- **`mariadb-replace`** — `REPLACE` (delete-then-insert) and exactly when `ON DUPLICATE KEY UPDATE` is the better upsert
- **`mariadb-select`** — the `SELECT` source for `INSERT … SELECT`, plus `PARTITION` pruning and priority semantics
- **`mariadb-create-table`** / **`mariadb-alter-table`** — `AUTO_INCREMENT`, `DEFAULT` expressions, and `UNIQUE`/`PRIMARY KEY` definitions that drive `ON DUPLICATE KEY UPDATE` and `IGNORE` behavior
- Canonical references on `mariadb.com/docs` (consult only for edge cases not covered here):
- <https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert>
- <https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert-on-duplicate-key-update>
- <https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insertreturning>
- <https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert-ignore>
- <https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert-delayed>
- <https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert-select>
- <https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert-default-duplicate-values>
<sub>_This page is: Copyright © 2026 MariaDB. All rights reserved._</sub>
Wersje
W bazie nie ma innych skilli o nazwie mariadb-insert.
Identyczna treść występuje jeszcze w 8 innych folderach na GitHubie. Tu pokazujemy jedną, najlepiej ocenioną kopię.
Migawka bazy z 13.11.2026 · wersja paczki dc96a7278bb7 (skrót SHA-256 całej paczki)
Pobrania w ostatnich 30 dniach: 0 · ranking
Zgłoś problem z tym skillem: szkodliwa treść, błędny opis albo prośba autora o usunięcie.
Podobne skille
- 100mariadb-replace
Opisuje działanie REPLACE w MariaDB jako usunięcie z następującym wstawieniem, ryzyko utraty danych, alternatywę INSERT ON DUPLICATE KEY UPDATE oraz formy RETURNING i SET. - 100mariadb-update
Opisuje składnię i zachowanie UPDATE w MariaDB: formy jedno- i wielotabelowe, ORDER BY z LIMIT, RETURNING z OLD_VALUE(), kolejność przypisań, aktualizację tabel wersjonowanych oraz CTE przed UPDATE. - 100t-sql-upserts-merge
Pisze bezpieczny upsert dla Azure SQL Database, odporny na równoczesne sesje, i odrzuca warianty MERGE gubiące wiersze. - 100psycopg2-batch-insert-optimization
Przyspiesza wolne wstawianie danych do PostgreSQL w Pythonie przez execute_values z psycopg2 zamiast pojedynczych INSERT. - 100frappe-syntax-query-builder
Pomaga budować zapytania do bazy przez frappe.qb opartego na PyPika: SELECT, INSERT, UPDATE, DELETE, joiny, agregacje, podzapytania i zgodność z MariaDB i PostgreSQL. - 100yida-business-rule
Konfiguruje w formularzach Yida reguły powiązań biznesowych i funkcje INSERT, UPDATE, DELETE i UPSERT do zmian między tabelami.