> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/tools/community/mariadb-ai-plugins/skills-reference/sql-statement-skills.md).

# SQL Statement Skills

Reference for the MariaDB AI Plugins skills that cover individual SQL statements and their MariaDB-specific syntax and behavior.

Each of these skills covers one SQL statement, with the MariaDB-specific syntax and behavior that general SQL knowledge often gets wrong, for example the online DDL algorithms of `ALTER TABLE`, atomic `CREATE OR REPLACE`, and `RETURNING` in `INSERT`.

| Skill                      | Plugins      | What it covers                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| -------------------------- | ------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `mariadb-create-table`     | `dev`, `sql` | MariaDB-specific syntax and behavior for CREATE TABLE — atomic CREATE OR REPLACE, INVISIBLE / COMPRESSED columns, system-versioned and application-time PERIOD clauses, IGNORED indexes, table-level encryption, page compression, sequence and Aria table options, and the utf8/utf8mb4 trap.                                                                                                                                                                                                                                                                                                    |
| `mariadb-alter-table`      | `dev`, `sql` | MariaDB-specific syntax and behavior for ALTER TABLE — online DDL with ALGORITHM (DEFAULT/COPY/INPLACE/NOCOPY/INSTANT) and LOCK clauses, ALTER ONLINE TABLE shorthand, WAIT/NOWAIT lock timeouts, IF EXISTS/IF NOT EXISTS on subclauses, ADD/DROP SYSTEM VERSIONING, ADD PERIOD FOR, DROP CONSTRAINT, FORCE rebuild, CONVERT PARTITION/TABLE, DISCARD/IMPORT TABLESPACE, atomic ALTER, two-phase replication.                                                                                                                                                                                     |
| `mariadb-select`           | `dev`, `sql` | MariaDB-specific syntax and behavior for SELECT — optimizer hints (/\*+ ... \*/, JOIN\_PREFIX/JOIN\_ORDER/JOIN\_FIXED\_ORDER/JOIN\_SUFFIX, MAX\_EXECUTION\_TIME) and legacy query-level hints, LIMIT extensions (comma-form, ROWS EXAMINED), OFFSET … FETCH … ONLY/WITH TIES, FOR UPDATE / LOCK IN SHARE MODE with WAIT/NOWAIT/SKIP LOCKED, PARTITION pruning, FOR SYSTEM\_TIME temporal queries, SELECT INTO OUTFILE/DUMPFILE/variable, WITH ROLLUP, max\_statement\_time, FROM DUAL. Covers SELECT, SELECT INTO OUTFILE, SELECT INTO DUMPFILE, SELECT … OFFSET … FETCH, and SELECT WITH ROLLUP. |
| `mariadb-insert`           | `dev`, `sql` | 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.                                                                                                                                                                                                |
| `mariadb-update`           | `dev`, `sql` | MariaDB-specific syntax and behavior for UPDATE — single-table vs multi-table forms and their different rules, UPDATE … ORDER BY … LIMIT, PARTITION pruning, RETURNING with OLD\_VALUE() (since 13.0), left-to-right assignment evaluation and SIMULTANEOUS\_ASSIGNMENT sql\_mode, IGNORE / LOW\_PRIORITY modifiers, rows-matched-vs-changed reporting, updating system-versioned and application-time-period (FOR PORTION OF) tables, and CTEs before UPDATE (since 12.3).                                                                                                                       |
| `mariadb-delete`           | `dev`, `sql` | MariaDB-specific syntax and behavior for DELETE — RETURNING deleted rows in one statement, both multi-table forms (DELETE … FROM and DELETE FROM … USING) with ORDER BY/LIMIT (since 11.8), single-table ORDER BY/LIMIT/PARTITION/alias/index hints, DELETE HISTORY for system-versioned tables, FOR PORTION OF for application-time periods, QUICK/LOW\_PRIORITY/IGNORE modifiers and their engine limits, deleting from a table referenced in its own subquery, and DELETE-vs-TRUNCATE differences.                                                                                             |
| `mariadb-replace`          | `dev`, `sql` | MariaDB-specific syntax and behavior for REPLACE — why REPLACE is DELETE-then-INSERT (not an upsert/UPDATE), when it triggers (PRIMARY/UNIQUE conflicts only), its data-loss footguns (unlisted columns reset to DEFAULT, AUTO\_INCREMENT burned, ON DELETE cascades and DELETE+INSERT triggers fired), the INSERT … ON DUPLICATE KEY UPDATE alternative, REPLACE … RETURNING, the SET and SELECT forms, PARTITION targeting, and the INSERT+DELETE privilege requirement.                                                                                                                        |
| `mariadb-create-view`      | `dev`, `sql` | MariaDB-specific syntax and behavior for views — CREATE OR REPLACE VIEW vs CREATE VIEW IF NOT EXISTS (mutually exclusive), ALGORITHM = UNDEFINED/MERGE/TEMPTABLE and its effect on updatability and performance, the SQL SECURITY DEFINER default, WITH CASCADED/LOCAL CHECK OPTION, what makes a view updatable/insertable, frozen-at-creation definitions, and ALTER VIEW / DROP VIEW.                                                                                                                                                                                                          |
| `mariadb-create-index`     | `dev`, `sql` | MariaDB-specific syntax and behavior for CREATE INDEX / DROP INDEX — that it maps to ALTER TABLE (batch multiple adds), can't create a PRIMARY KEY, needs a prefix length on TEXT/BLOB columns, has no expression/functional indexes (index a generated column instead), real descending indexes (since 10.8), IGNORED indexes for safe optimizer testing, UNIQUE/FULLTEXT/SPATIAL/VECTOR kinds, USING BTREE/HASH/RTREE, CREATE OR REPLACE / IF NOT EXISTS, KEY\_BLOCK\_SIZE being ignored here, and dropping a PRIMARY KEY via DROP INDEX `PRIMARY`.                                             |
| `mariadb-drop-table`       | `dev`, `sql` | MariaDB-specific syntax and behavior for DROP TABLE — RESTRICT/CASCADE being no-ops, the DROP TEMPORARY TABLE guard against dropping a same-named base table, partial drops in a multi-table statement, IF EXISTS warning behavior, failure when a table is referenced by a foreign key, DROP TABLE on a view, automatic trigger removal, implicit-commit (and the DROP TEMPORARY exception), WAIT/NOWAIT, and CREATE OR REPLACE TABLE as the atomic replace.                                                                                                                                     |
| `mariadb-create-database`  | `dev`, `sql` | MariaDB-specific syntax and behavior for databases — CREATE DATABASE / CREATE SCHEMA (exact synonyms), the OR REPLACE data-loss footgun vs IF NOT EXISTS, CHARACTER SET / COLLATE / COMMENT specifications and the utf8mb4 default-charset change, ALTER DATABASE (charset/collate/comment + UPGRADE DATA DIRECTORY NAME), DROP DATABASE, and the absence of a RENAME DATABASE statement.                                                                                                                                                                                                         |
| `mariadb-load-data`        | `dev`, `sql` | MariaDB-specific syntax and behavior for LOAD DATA \[LOCAL] INFILE (and LOAD XML) — the LOCAL vs server-side fork and its security/privilege requirements (local\_infile, FILE privilege, secure\_file\_priv), tab/newline (not CSV) defaults, how LOCAL silently downgrades duplicate handling to IGNORE and disables strict-mode aborts, IGNORE n LINES, user-variable + SET transformation, the CHARACTER SET clause, and priority/concurrency.                                                                                                                                                |
| `mariadb-transactions`     | `dev`, `sql` | MariaDB-specific syntax and behavior for transaction control — START TRANSACTION / BEGIN \[WORK], WITH CONSISTENT SNAPSHOT, READ ONLY / READ WRITE, COMMIT \[WORK] \[AND \[NO] CHAIN] \[\[NO] RELEASE], ROLLBACK \[WORK] \[AND \[NO] CHAIN] \[\[NO] RELEASE], ROLLBACK TO SAVEPOINT, SAVEPOINT, RELEASE SAVEPOINT, autocommit, implicit commit, completion\_type, in\_transaction, and idle transaction timeouts. Covers START TRANSACTION, BEGIN, COMMIT, ROLLBACK, and SAVEPOINT.                                                                                                               |
| `mariadb-set-transaction`  | `dev`, `sql` | MariaDB-specific behavior for SET TRANSACTION and transaction isolation — the GLOBAL/SESSION/next-transaction scope rule, the transaction\_isolation system variable (successor to deprecated tx\_isolation), READ WRITE/READ ONLY access mode, and innodb\_snapshot\_isolation write-write conflict detection (ON by default under REPEATABLE READ since 11.6.2, raising ER\_CHECKREAD/1020 and rolling back the whole transaction).                                                                                                                                                             |
| `mariadb-lock-tables`      | `dev`, `sql` | MariaDB explicit table locking (LOCK TABLES / UNLOCK TABLES: READ, READ LOCAL, WRITE, LOW\_PRIORITY WRITE, WRITE CONCURRENT, WAIT n/NOWAIT) and the named user-level lock functions GET\_LOCK, RELEASE\_LOCK, RELEASE\_ALL\_LOCKS, IS\_FREE\_LOCK, IS\_USED\_LOCK — including the alias-matching trap, the implicit-commit interaction with transactions, and multi-lock-per-connection semantics since 10.0.2.                                                                                                                                                                                   |
| `mariadb-create-user`      | `dev`, `sql` | MariaDB-specific syntax and behavior for CREATE USER — multiple authentication methods chained with OR, IDENTIFIED BY / IDENTIFIED BY PASSWORD / IDENTIFIED VIA\|WITH plugin USING\|AS 'string'\|PASSWORD(...), the default authentication plugin, REQUIRE TLS options, WITH resource-limit options, PASSWORD EXPIRE variants, ACCOUNT LOCK/UNLOCK, OR REPLACE, IF NOT EXISTS, and account-name/host defaulting. Covers CREATE USER only (not GRANT's implicit user-creation path).                                                                                                               |
| `mariadb-grant`            | `dev`, `sql` | MariaDB-specific GRANT behavior — privilege scope levels (global/db/table/column/routine), implicit account creation interacting with the NO\_AUTO\_CREATE\_USER sql\_mode (on by default), MariaDB-only privileges LLMs won't know (BINLOG MONITOR, DELETE HISTORY, SET USER, FEDERATED ADMIN, READ\_ONLY ADMIN, etc.) and the SUPER-privilege split, GRANT PROXY, resource limits, REQUIRE TLS options, and MariaDB roles (GRANT role TO user/role, WITH ADMIN OPTION, single-active-role semantics).                                                                                           |
| `mariadb-revoke`           | `dev`, `sql` | MariaDB-specific syntax and behavior for REVOKE — the mirrored five-form grammar (privilege ON object, the special ALL PRIVILEGES/GRANT OPTION form with no ON clause, PROXY, role revocation, ADMIN OPTION FOR role), the USAGE-privilege gotcha, ER\_NONEXISTING\_GRANT on mismatched revoke level, and the absence of an IF EXISTS clause.                                                                                                                                                                                                                                                     |
| `mariadb-alter-user`       | `dev`, `sql` | MariaDB-specific syntax and behavior for ALTER USER and SET PASSWORD — IF EXISTS warning-not-error semantics, IDENTIFIED BY vs IDENTIFIED BY PASSWORD vs IDENTIFIED VIA/WITH multi-plugin auth, PASSWORD EXPIRE variants and default\_password\_lifetime interaction, ACCOUNT LOCK/UNLOCK, resource limits, TLS/REQUIRE options, and SET PASSWORD's three literal forms (PASSWORD(), OLD\_PASSWORD(), pre-hashed string — there is no cleartext-string form).                                                                                                                                     |
| `mariadb-drop-user`        | `dev`, `sql` | MariaDB-specific behavior for DROP USER and RENAME USER — multiple accounts per statement, IF EXISTS turning a missing-user error into a note, the active-connections warning (dropped users keep working until their session ends; new connections are blocked), no cascade onto objects owned or privileges granted by the dropped user, dangling DEFINER references left behind on views/routines/triggers, and RENAME USER preserving privileges across the rename with no active-session check.                                                                                              |
| `mariadb-create-procedure` | `dev`, `sql` | MariaDB-specific syntax and behavior for CREATE PROCEDURE — OR REPLACE / IF NOT EXISTS (mutually exclusive), DEFINER and SQL SECURITY DEFINER/INVOKER, parameter modes IN/OUT/INOUT (plus IN OUT and NOCOPY in Oracle mode only), parameter DEFAULT values (11.8+), the CONTAINS SQL/NO SQL/READS SQL DATA/MODIFIES SQL DATA characteristics (advisory, default CONTAINS SQL), DETERMINISTIC being a no-op on procedures, required CREATE ROUTINE privilege plus automatic ALTER ROUTINE/EXECUTE grants, and the client-side DELIMITER convention.                                                |
| `mariadb-create-function`  | `dev`, `sql` | MariaDB-specific syntax and behavior for CREATE FUNCTION across its three distinct forms — stored functions (RETURNS + RETURN, IN/OUT/INOUT parameters, DETERMINISTIC/binlog interaction), stored aggregate functions (CREATE AGGREGATE FUNCTION with FETCH GROUP NEXT ROW), and loadable user-defined functions (CREATE FUNCTION ... SONAME 'lib.so', a plugin\_dir-loaded shared library unrelated to stored routines).                                                                                                                                                                         |
| `mariadb-create-trigger`   | `dev`, `sql` | MariaDB-specific syntax and behavior for CREATE TRIGGER — OR REPLACE, DEFINER, IF NOT EXISTS, multiple trigger\_events in one statement with INSERTING/UPDATING/DELETING predicates (since 12.0), multiple triggers per action-time/event ordered with FOLLOWS/PRECEDES, OLD/NEW row-reference rules, the no-ALTER-TRIGGER gotcha, and restrictions on modifying tables already touched by the firing statement.                                                                                                                                                                                  |
| `mariadb-call`             | `dev`, `sql` | MariaDB-specific syntax and behavior for CALL — optional parentheses when a procedure takes no arguments, OUT/INOUT parameters that must bind to user-defined variables, procedures returning result sets directly to the client (unlike stored functions), the EXECUTE privilege requirement, CALL as a prepared statement, and max\_sp\_recursion\_depth for recursive procedures.                                                                                                                                                                                                              |
| `mariadb-create-sequence`  | `dev`, `sql` | MariaDB-specific syntax and behavior for CREATE SEQUENCE — sequences are ordinary tables (any transactional engine, default BIGINT SIGNED) wrapped with sequence semantics, support atomic CREATE OR REPLACE / IF NOT EXISTS, default to CACHE 1000, expose ANSI (NEXT VALUE FOR), PostgreSQL (NEXTVAL()), and Oracle-mode (seq.nextval) access forms, special-case INCREMENT BY 0 for multi-master/Galera replication, require row-based binlogging, and (since 11.5) truncate rather than reject out-of-range MINVALUE/MAXVALUE/START literals.                                                 |
| `mariadb-truncate-table`   | `dev`, `sql` | MariaDB-specific syntax and behavior for TRUNCATE TABLE — DROP privilege (not DELETE), implicit commit with no rollback, unconditional AUTO\_INCREMENT reset, no ON DELETE triggers, FK-parent tables rejected unless self-referencing, system-versioned tables and SEQUENCEs reject TRUNCATE outright, always '0 rows affected', and the WAIT n \| NOWAIT extension.                                                                                                                                                                                                                             |
| `mariadb-rename-table`     | `dev`, `sql` | MariaDB-specific syntax and behavior for RENAME TABLE — atomic multi-table renames executed left-to-right with full rollback on any failure (enabling the classic swap trick), works directly on TEMPORARY tables (no ALTER TABLE needed), refuses to run inside an active transaction or under LOCK TABLES, requires ALTER+DROP on the source and CREATE+INSERT on the target, supports IF EXISTS and WAIT/NOWAIT, and has specific cross-database move restrictions for views and tables with triggers.                                                                                         |
| `mariadb-set`              | `dev`, `sql` | MariaDB-specific syntax and behavior for SET — GLOBAL/SESSION/LOCAL scope rules and their session-isolation semantics, the SET STATEMENT ... FOR extension (and its fixed list of 13 variables it cannot touch), `=`/`:=` equivalence for user variables inside SET (but not outside it), var = DEFAULT restore semantics, the SUPER privilege gate on SET GLOBAL, and the absence of SET PERSIST (MariaDB persists via config file, not SQL).                                                                                                                                                    |
| `mariadb-show`             | `dev`, `sql` | MariaDB-specific behavior of the SHOW statement family — replication SHOW forms are spelled BINLOG STATUS/REPLICA STATUS/REPLICA HOSTS (REPLICA is a lexer-level synonym for SLAVE) while the underlying status counters still say slave/master; bare SHOW `<name>` is generic sugar for any plugin-registered INFORMATION\_SCHEMA table (userstat, Galera, query-response-time); SHOW EXPLAIN/SHOW ANALYZE inspect another connection's live plan; only a fixed subset of SHOW forms accept LIKE/WHERE.                                                                                          |
| `mariadb-explain`          | `dev`, `sql` | MariaDB-specific behavior of EXPLAIN and its relatives (ANALYZE, EXPLAIN FORMAT=JSON, SHOW EXPLAIN/ANALYZE FOR) — MariaDB has no `EXPLAIN ANALYZE`; it uses a standalone `ANALYZE <statement>` that actually executes the query (and, for UPDATE/DELETE/INSERT, actually writes); plain EXPLAIN never runs anything; only FORMAT=JSON/TRADITIONAL exist (no TREE); and EXPLAIN covers INSERT/REPLACE directly.                                                                                                                                                                                    |
| `mariadb-prepare`          | `dev`, `sql` | MariaDB-specific behavior of server-side prepared statements — PREPARE / EXECUTE / DEALLOCATE PREPARE and EXECUTE IMMEDIATE — covers the broad preparable-statement allow-list, EXECUTE..USING accepting arbitrary expressions (not just user variables), the stored-function/trigger ban on dynamic SQL, session-local statement lifetime, and EXECUTE IMMEDIATE's DEFAULT/IGNORE bind parameters.                                                                                                                                                                                               |

<sub>*This page is licensed: GPLv2*</sub>

{% @marketo/form formId="4316" %}
