Przejdź do treści
Biblioteka skilli
Programowanie · Nauka i wyjaśnianie

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

100/ 100Klasa: dobry
Format25 / 25
Frontmatter z nazwą i opisem, poprawna nazwa, opis do 1024 znaków, nazwa zgodna z folderem.
Odkrywalność25 / 25
Opis mówi, kiedy użyć skilla, ma sensowną długość i jest w 3. osobie.
Treść35 / 35
Co najmniej 150 słów i do 500 linii, nagłówki, listy kroków, przykłady albo kod.
Higiena15 / 15
Bez zaślepek typu TODO, bez ścieżek Windows, z licencją.
Ryzykobrak sygnałów w skanie
Pliki w paczce
1
Skrypty
0
Komentarze HTML
0
Adresy URL w SKILL.md
7
Przestarzałość

Brak sygnałów. Repozytorium zmienione 24.10.2026, bez odwołań do wycofanych modeli i API.

Licencja

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

  1. What LLMs Often Miss
  2. RETURNING
  3. ON DUPLICATE KEY UPDATE (the real upsert)
  4. INSERT IGNORE
  5. Forms and modifiers
  6. See Also
Sekcje z nagłówków SKILL.md, w oryginale · 93 linii, 1 025 słów
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.

Uwagi i zgłoszenia

Wiadomość trafi do Bartosza Sokolińskiego. Adres e-mail jest potrzebny tylko do odpowiedzi. Szczegóły w polityce prywatności.