| 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:
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)
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
INSERT IGNORE INTO t (id, name) VALUES (1, 'a'), (1, 'dup'), (2, 'b');
SHOW WARNINGS;
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
INSERT INTO person SET first_name = 'John', last_name = 'Doe';
INSERT INTO t VALUES (1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO archive (id, payload) SELECT id, payload FROM live WHERE ts < '2025-01-01';
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 upsertmariadb-select — the SELECT source for INSERT … SELECT, plus PARTITION pruning and priority semanticsmariadb-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):
