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.
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.
This page is licensed: GPLv2
Last updated
Was this helpful?

