7. Data Definition Language (DDL)

This chapter describes the statements that create, modify, and drop MIRAJ databases, tables, indexes, views, sequences, stored routines, triggers, and events. For the list and ranges of data types, see Data Types.

The examples are run in a database d with miraj-cli; the error messages are those of a server running in English (--lang en).

CREATE DATABASE#

CREATE [OR REPLACE] DATABASE [IF NOT EXISTS] database_name [[DEFAULT] CHARACTER SET [=] charset] [[DEFAULT] COLLATE [=] collation];
CREATE [OR REPLACE] SCHEMA [IF NOT EXISTS] database_name [options];

SCHEMA is a synonym for DATABASE. COLLATE sets the database's default collation: tables created afterwards without COLLATE= take it (see Collations, § 13.2); with no option, or with CHARACTER SET utf8mb4 alone, it is the general collation utf8mb4_general_ci. COMMENT is accepted and ignored.

[DEFAULT] ENCRYPTION [=] 'Y' | 'N' sets, on an encrypted data directory, the default encryption of the database's tables and of their definitions; ALTER DATABASE name [DEFAULT] ENCRYPTION = 'Y' | 'N' changes it. See Encryption at rest.

CREATE DATABASE IF NOT EXISTS gestium;
-- OK, 1 row(s) affected
CREATE DATABASE IF NOT EXISTS gestium;
-- OK, 0 row(s) affected; Warning: Can't create database 'gestium'; database exists
CREATE DATABASE compta CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW CREATE DATABASE compta;
-- CREATE DATABASE `compta` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */
CREATE OR REPLACE DATABASE essais;
-- OK, 2 row(s) affected   (the database existed and was replaced)

SHOW CREATE DATABASE returns the DEFAULT CHARACTER SET … COLLATE … clause only for a database whose collation is not the default; another character set (CHARACTER SET latin1) is accepted and the database stays in utf8mb4.

CREATE OR REPLACE DATABASE db is equivalent to DROP DATABASE IF EXISTS db followed by CREATE DATABASE db: tables, views, routines, and sequences of the old database are lost, and the database takes the options written (not those of the replaced database). The DROP and CREATE privileges on the database are required and checked before anything is dropped. The refusals of DROP DATABASE still apply (system database, table held by another session); if the replaced database is the current database, the session has no current database afterwards, as after a DROP DATABASE. The statement returns 2 rows when a database was replaced, 1 otherwise, with no note for an absent database. The two statements are executed and journaled one after the other (binary log: DROP DATABASE IF EXISTS then CREATE DATABASE): if the creation fails after the drop, the database stays dropped.

ALTER DATABASE#

ALTER {DATABASE | SCHEMA} [database_name] [[DEFAULT] CHARACTER SET [=] charset] [[DEFAULT] COLLATE [=] collation];

Changes the database's default collation (the current database when no name is given) for tables created afterwards; existing tables keep theirs. ALTER privilege on the database.

DROP DATABASE#

DROP DATABASE [IF EXISTS] database_name;
DROP SCHEMA [IF EXISTS] database_name;

DROP DATABASE also drops the database's views, routines, triggers, events, and sequences.

CREATE TABLE#

Full definition#

CREATE [OR REPLACE] [TEMPORARY | GLOBAL TEMPORARY] TABLE [IF NOT EXISTS] [db.]table (
    column_definition [, column_definition ...]
    [, [CONSTRAINT [name]] PRIMARY KEY [USING {BTREE | HASH}] (key_element [, ...])]
    [, [CONSTRAINT [name]] UNIQUE [KEY | INDEX] [name] (key_element [, ...]) [index_options]]
    [, {INDEX | KEY} [name] (key_element [, ...]) [index_options]]
    [, FULLTEXT [KEY | INDEX] [name] (column [WEIGHT n] [, ...]) [index_options]]
    [, SPATIAL [KEY | INDEX] [name] (column) [index_options]]
    [, VECTOR [KEY | INDEX] [name] (column) [index_options]]
    [, [CONSTRAINT [name]] FOREIGN KEY [name] (column [, ...])
         REFERENCES [db.]ref_table (column [, ...])
         [ON DELETE action] [ON UPDATE action]]
    [, [CONSTRAINT [name]] CHECK (expression) [[NOT] ENFORCED]]
    [, PERIOD FOR {SYSTEM_TIME | period} (start, end)]
) [table_options] [PARTITION BY ...] [[IGNORE | REPLACE] [AS] query];

key_element   : column [(length)] [ASC | DESC]
index_options : [USING {BTREE | HASH}] [COMMENT 'text'] [VISIBLE | INVISIBLE] [IGNORED | NOT IGNORED]
table_options : [AUTO_INCREMENT [=] n] [ENGINE [=] engine] [[DEFAULT] CHARSET [=] charset]
                [[DEFAULT] COLLATE [=] collation] [COMMENT [=] 'text']
                [ENCRYPTION [=] {'Y' | 'N'}] [WITH SYSTEM VERSIONING] [ROW_FORMAT = ...] ...

Where column_definition is written:

column type
    [UNSIGNED] [ZEROFILL] [[CHARACTER SET charset] COLLATE collation] [BINARY]
    [NOT NULL | NULL]
    [DEFAULT value | DEFAULT (expression) | DEFAULT function(...)]
    [AUTO_INCREMENT]
    [UNIQUE [KEY]] [PRIMARY KEY | KEY]
    [COMMENT 'text'] [COMPRESSED] [INVISIBLE | VISIBLE]
    [ON UPDATE CURRENT_TIMESTAMP[(n)]]
    [[CONSTRAINT [name]] CHECK (expression)]
    [REFERENCES ref_table (column) [ON DELETE action] [ON UPDATE action]]
    [[GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED | PERSISTENT]]
    [FIRST | AFTER column]          -- in ALTER TABLE only

The attributes can be written in any order after the type.

A realistic example:

CREATE TABLE client (
    id            INT PRIMARY KEY AUTO_INCREMENT,
    code          VARCHAR(20) NOT NULL,
    nom           VARCHAR(100) NOT NULL,
    email         VARCHAR(255),
    plafond       DECIMAL(12,2) NOT NULL DEFAULT 0,
    cree_le       DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (code),
    CHECK (plafond >= 0)
);

CREATE TABLE commande (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    client_id   INT NOT NULL,
    montant     DECIMAL(12,2) NOT NULL,
    statut      ENUM('nouvelle', 'expediee', 'annulee') NOT NULL DEFAULT 'nouvelle',
    KEY idx_statut (statut),
    FOREIGN KEY (client_id) REFERENCES client (id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

Table clauses#

Non-unique secondary indexes#

CREATE TABLE mouvement (
    id        INT PRIMARY KEY AUTO_INCREMENT,
    article   VARCHAR(20) NOT NULL,
    date_mvt  DATE NOT NULL,
    KEY idx_article (article)
);

CREATE INDEX idx_date ON mouvement (date_mvt);

KEY / INDEX in CREATE TABLE, CREATE INDEX, and ALTER TABLE ... ADD INDEX create a secondary index, with an unlimited number per table. These indexes are kept in memory and rebuilt when the database is opened. They serve an equality condition covering all of their columns (integers, decimals, strings, dates; not floats): KEY (n, s) serves n = 5 AND s = 'v5' but not n = 5 alone; any other condition of the same query is filtered on the rows thus found. For a VECTOR column, the index also serves IN, IS NULL, <, <=, >, >=, and BETWEEN. A VIRTUAL generated column may be included (see Data Types).

USING BTREE / USING HASH is kept and returned by SHOW CREATE TABLE and SHOW INDEX (Index_type, HASH or BTREE), without changing the index.

Order and names of keys. SHOW CREATE TABLE, SHOW INDEX, and information_schema.STATISTICS present the keys in the order of the reference, whatever the order they were written in: primary key, UNIQUE on NOT NULL columns (without prefix, then with prefix), other UNIQUE (without prefix, then with prefix), hash UNIQUE (USING HASH, or on a whole TEXT / BLOB), ordinary and spatial indexes, FULLTEXT, VECTOR; within the same rank, in declaration order (keys written inline on a column included). A key added by ALTER TABLE or CREATE INDEX takes its rank among the existing keys, which do not move (a column that became NOT NULL, a key renamed or dropped then added back under the same name keep their place). A key without a name takes the name of its first column, then _2, _3… in declaration order:

CREATE TABLE ko3 (a INT UNIQUE, b INT NOT NULL UNIQUE, c INT PRIMARY KEY, KEY (a), UNIQUE (a));
SELECT INDEX_NAME, COLUMN_NAME FROM information_schema.STATISTICS WHERE TABLE_NAME = 'ko3';
-- PRIMARY | c
-- b       | b
-- a       | a
-- a_3     | a      (UNIQUE (a), declared third)
-- a_2     | a      (KEY (a))

COMMENT (apart from COMMENT 'miraj:trigram', see below) and VISIBLE / INVISIBLE are accepted with no effect. An index identical to an existing index is created anyway, with note 1831 ("Duplicate index x. This is deprecated and will be disallowed in a future release").

Foreign keys#

CREATE TABLE ligne_commande (
    id           INT PRIMARY KEY AUTO_INCREMENT,
    commande_id  INT NOT NULL,
    article_id   INT NOT NULL,
    quantite     INT NOT NULL,
    FOREIGN KEY (commande_id) REFERENCES commande (id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (article_id) REFERENCES article (id)
        ON DELETE RESTRICT ON UPDATE RESTRICT
);

Referential actions recognized by the parser: RESTRICT, CASCADE, SET NULL, NO ACTION, SET DEFAULT. Only the first four are actually applied: SET DEFAULT is parsed but rejected when the key is created with error 1215 (ER_CANNOT_ADD_FOREIGN).

A foreign key without a name receives <table>_ibfk_<n>. The column attribute REFERENCES table (col) also creates a foreign key.

The referenced table must be in the same database (otherwise error 1235).

CREATE TABLE ... LIKE#

CREATE TABLE archive_client LIKE client;
CREATE TABLE archive_client (LIKE client);

Copies the source table: columns (types, default values, generated expressions, AUTO_INCREMENT attribute), primary key, UNIQUE constraints, secondary indexes, full-text and IGNORED indexes, CHECK constraints, engine, and any partitioning. The following are not copied: the value of the AUTO_INCREMENT counter (the new table starts at 1, even if the source was created with AUTO_INCREMENT = 50) and foreign keys. The new table is empty. LIKE followed by a query is a syntax error (1064).

CREATE TABLE ... [AS] SELECT (CTAS)#

CREATE TABLE bilan_client [IF NOT EXISTS] AS
SELECT c.id, c.nom, SUM(cmd.montant) AS total
FROM client c
JOIN commande cmd ON cmd.client_id = c.id
GROUP BY c.id, c.nom;

CREATE TABLE [IF NOT EXISTS] table [(definitions)] [table_options] [IGNORE | REPLACE] [AS] query;

The word AS is optional; the query may be written in parentheses ((SELECT ...)) or not, may start with WITH or be a VALUES (…), (…) constructor, and may draw on any source (table, view, join, UNION, subquery, aggregate, LIMIT). The table options (ENGINE = Aria, COMMENT…) and PARTITION BY are written before the query.

What is taken from the source:

• the name (or alias) of each result column;
• the type of the expression: the declared type if the result column comes as-is from a table, otherwise a type sized for the expression (CONCAT(nom, '!') on a VARCHAR(100) gives varchar(101), plafond * 2 on a DECIMAL(12,2) gives decimal(13,2), the literal 1 gives int(1), a lone NULL gives bigint(20)); FLOOR, CEILING, ROUND, and TRUNCATE of an integer column keep its type (FLOOR(ti) on a TINYINT: tinyint(4)), of a decimal of 10 to 18 digits give a bigint(digits + 1) (FLOOR(d) on a DECIMAL(10,0): bigint(11)), of an UNSIGNED decimal an unsigned integer, of a date an int(8) unsigned; these types also apply to the columns of a view;
• the ZEROFILL attribute of a column, including through a derived table;
• the nullability of the result.

A JSON column of the source becomes a longtext in utf8mb4_bin, without the json_valid constraint of a JSON column (see Data Types).

What is never taken: primary key, index, UNIQUE constraint, AUTO_INCREMENT, DEFAULT, generated column (which becomes an ordinary column holding the already computed value).

Columns and keys declared with the query. A list of definitions in parentheses may precede the query: a declared column that has the name of a result column takes its definition (type, DEFAULT…) and receives its values; a declared column absent from the result receives its default value. Column order of the created table: first the declared columns that no result column names, then the result columns in their order (a declared column that has the name of a result column takes its place). The list may also contain only keys ((PRIMARY KEY (id))). IGNORE discards the rows that hit a uniqueness key (warning 1062), REPLACE replaces them; with neither, a duplicate makes the statement fail.

-- client table from the "Full definition" example
INSERT INTO client (code, nom, plafond) VALUES ('C1', 'Dupont', 100), ('C2', 'Martin', 250.5);
CREATE TABLE c2 (remarque VARCHAR(20) DEFAULT 'rien', id INT) SELECT id, nom FROM client;
SELECT * FROM c2;

+----------+----+--------+
| remarque | id | nom    |
+----------+----+--------+
| rien     |  1 | Dupont |
| rien     |  2 | Martin |
+----------+----+--------+

CREATE TABLE c3 (PRIMARY KEY (id)) AS
  SELECT id, nom, plafond * 2 AS double_plafond, CONCAT(nom, '!') AS cri, 1 AS un, NULL AS rien FROM client;
SHOW CREATE TABLE c3;

CREATE TABLE `c3` (
  `id` int(11) NOT NULL,
  `nom` varchar(100) NOT NULL,
  `double_plafond` decimal(13,2) NOT NULL,
  `cri` varchar(101) NOT NULL,
  `un` int(1) NOT NULL,
  `rien` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

The statement returns the number of rows inserted (like an INSERT), not a result set. The source query is read in full (source tables locked for reading) before the table is created: it does not yet exist during the read, and a failure while populating leaves the table nonexistent.

CREATE OR REPLACE [TEMPORARY] TABLE#

CREATE OR REPLACE TABLE client (
    id   INT PRIMARY KEY AUTO_INCREMENT,
    nom  VARCHAR(100) NOT NULL
);

Equivalent to DROP TABLE IF EXISTS table followed by the corresponding CREATE TABLE: rows, indexes, keys, constraints, AUTO_INCREMENT counter, and triggers of the old table are lost.

• IF NOT EXISTS is forbidden with OR REPLACE (error 1221, ER_WRONG_USAGE), as for a view.
• The new definition is fully validated before the old table is dropped (columns, keys, indexes, constraints, default values — the definition is built in memory and then discarded for this check): an invalid definition leaves the old table intact.
• Whatever can only be verified at actual write time (file not written, rows from the ... [AS] SELECT rejected) occurs after the drop: the old table is then lost.
• A table referenced by another table's foreign key is still refused (error 3730), as is the name of a system database or an invalid table name (error 1103).
• A name already held by a view is never replaced by a table (error 1050, "Table 'v' already exists").
• CREATE OR REPLACE TEMPORARY TABLE replaces only the session's temporary table, never a database table of the same name that it masks.
• Required privileges: CREATE and DROP on the table.

DROP TABLE#

DROP [TEMPORARY] TABLE [IF EXISTS] [db.]table [, [db.]table2 ...];

A table referenced by a foreign key of another table is never dropped (error 3730): drop the foreign key first. DROP TEMPORARY TABLE drops only the session's temporary tables (error 1051 if it does not exist); an ordinary DROP TABLE first drops a session temporary table of the same name, and otherwise a database table. With IF EXISTS, each absent table gives a note 1051 and the others are dropped. A view is dropped with DROP VIEW (DROP TABLE on a view returns 1051).

TRUNCATE TABLE#

TRUNCATE [TABLE] table;

Empties the table (rows, associated LOB store) without going through the row-by-row journal as a DELETE does; the AUTO_INCREMENT counter restarts at 1 (unlike DELETE FROM table, which keeps it). A table held for reading by an open transaction of another session, or with uncommitted writes, conflicts with TRUNCATE (error 1213, without waiting). See also the section "TRUNCATE: details" at the end of the chapter.

ALTER TABLE#

ALTER TABLE [db.]table clause [, clause ...];

Several clauses separated by commas apply all or nothing within the same statement, each on the state left by the previous clauses (dropping then re-creating a column or an index in a single statement does both). A column dropped then added back under the same name (DROP COLUMN b, ADD COLUMN b BIGINT) keeps its primary key, its UNIQUE keys, and its indexes: they apply to the new column and are rebuilt on its values (its default value for each row, hence a 1062 duplicate under a uniqueness key if it is not NULL and the table has several rows). A CHECK constraint or a foreign key that cites the column always makes dropping it fail (3959, 1828, 1829).

Columns#

ALTER TABLE client ADD COLUMN telephone VARCHAR(20);
ALTER TABLE client ADD COLUMN IF NOT EXISTS telephone VARCHAR(20);
ALTER TABLE client ADD COLUMN a INT, ADD COLUMN b INT;
ALTER TABLE client DROP COLUMN telephone;
ALTER TABLE client DROP COLUMN IF EXISTS telephone;
ALTER TABLE client MODIFY COLUMN nom VARCHAR(150) NOT NULL;
ALTER TABLE client CHANGE COLUMN nom nom_complet VARCHAR(150) NOT NULL;
ALTER TABLE client RENAME COLUMN nom_complet TO nom;
ALTER TABLE client ALTER COLUMN plafond SET DEFAULT 0;
ALTER TABLE client ALTER COLUMN plafond DROP DEFAULT;

MODIFY and CHANGE COLUMN redefine the entire column: an existing ON UPDATE CURRENT_TIMESTAMP clause is therefore lost if it is not rewritten in the new definition.

Collation (see Collations): ADD, MODIFY, and CHANGE without COLLATE take the table's default, RENAME COLUMN keeps the column's own; [DEFAULT] COLLATE = name changes the table's default without touching existing columns, and [DEFAULT] CHARSET = charset alone resets it to the character set's default (no row copy in either case); CONVERT TO CHARACTER SET utf8mb4 [COLLATE name] converts all text columns, JSON included. A key column whose values become equal under the new collation ('A1' and 'a1' going from utf8mb4_bin to utf8mb4_general_ci) returns 1062 and the table stays unchanged; changing the collation of a partitioning column returns 1235.

Type conversions under MODIFY / CHANGE: an existing value that no longer fits the new type returns, in strict mode, the generic truncation error 1265 ("Data truncated for column... at row..."), and not the code specific to an INSERT / UPDATE (neither 1406 nor 1138). A NOT NULL column without DEFAULT added to a non-empty table receives in the existing rows the implicit value of its type: 0, empty string, or zero date (0000-00-00, 0000-00-00 00:00:00) for a date/time column.

Indexes and keys#

ALTER TABLE commande ADD PRIMARY KEY (id);
ALTER TABLE commande DROP PRIMARY KEY;
ALTER TABLE commande ADD UNIQUE (numero);
ALTER TABLE commande ADD UNIQUE KEY IF NOT EXISTS uq_numero (numero);
ALTER TABLE commande ADD INDEX idx_statut (statut);
ALTER TABLE commande ADD KEY IF NOT EXISTS idx_statut (statut);
ALTER TABLE commande DROP INDEX idx_statut;
ALTER TABLE commande DROP INDEX IF EXISTS idx_statut;
ALTER TABLE commande ADD FOREIGN KEY (client_id) REFERENCES client (id);
ALTER TABLE commande DROP FOREIGN KEY commande_ibfk_1;
ALTER TABLE commande RENAME INDEX idx_statut TO idx_etat;
ALTER TABLE commande RENAME KEY IF EXISTS idx_statut TO idx_etat;
ALTER TABLE commande ALTER INDEX idx_etat IGNORED;
ALTER TABLE commande ALTER INDEX idx_etat NOT IGNORED;

The column order of an added primary key always follows the column order of the table (ADD PRIMARY KEY (b, a) produces PRIMARY KEY (a, b), not the order written in the clause); a UNIQUE on a single column of a composite primary key is ignored, as in CREATE TABLE.

A foreign key without an explicit name receives the generated name <table>_ibfk_<n> (visible in SHOW CREATE TABLE and in the error messages that cite it, such as DROP FOREIGN KEY).

RENAME {INDEX | KEY} [IF EXISTS] old TO new renames an index without rebuilding it, whatever its kind (UNIQUE, secondary, FULLTEXT, trigram, SPATIAL, VECTOR): the new name appears in SHOW CREATE TABLE, SHOW INDEX, information_schema.STATISTICS, EXPLAIN, and the 1062 message of a uniqueness key; a foreign key that relies on the index keeps doing so. Errors: missing index 1176 ("Key 'x' doesn't exist in table 't'"; note with IF EXISTS), name already used by another index 1061, PRIMARY as old or new name 1280. In a statement with several clauses, the indexes being dropped are dropped first, then the renames apply, then the additions: RENAME INDEX a TO b, ADD INDEX a (c) succeeds. Each clause designates the index by its name from before the statement and the renames take effect together: RENAME INDEX a TO b, RENAME INDEX b TO a swaps two names; renaming the same index twice in one statement returns 1176.

ALTER {INDEX | KEY} [IF EXISTS] name [NOT] IGNORED marks an index as ignored: it is still kept up to date by writes (an IGNORED uniqueness key still rejects duplicates, 1062) but is never chosen for a read again — useful to measure the effect of dropping an index before dropping it. The attribute can also be written in an index definition (KEY i (a) IGNORED in CREATE TABLE, ALTER TABLE … ADD INDEX i (a) IGNORED, CREATE INDEX i ON t (a) IGNORED, likewise for UNIQUE, FULLTEXT, SPATIAL, and VECTOR); NOT IGNORED is the default. SHOW CREATE TABLE displays IGNORED after the index, SHOW INDEX returns it in its Ignored column (YES / NO, and Visible at NO), information_schema.STATISTICS in IGNORED (and IS_VISIBLE at NO); EXPLAIN no longer lists the index in possible_keys. The attribute follows the index through a rename, a rebuild of the table, CREATE TABLE … LIKE, and a reopen. A MATCH … AGAINST whose only FULLTEXT index is ignored returns 1191, as if it did not exist; an index hint (FORCE INDEX (i)…) that cites it returns 1176. Errors: primary key 4174 ("A primary key cannot be marked as IGNORE", in CREATE TABLE too), missing index 1176 (note with IF EXISTS).

AUTO_INCREMENT#

ALTER TABLE commande AUTO_INCREMENT = 10000;

Sets or raises the next counter value; it has no effect when lowering it below the maximum already used.

RENAME#

ALTER TABLE commande RENAME TO commande_ancienne;
RENAME TABLE commande_ancienne TO commande_archivee, autre_table TO autre_nouveau;

RENAME TABLE (several pairs in one statement) checks all the pairs before performing any renaming (errors 1146, 1050, 1103 depending on the case), then applies them one after the other. Renaming to another database is refused (error 1235); renaming a table to its own name returns error 1050.

FORCE, ORDER BY, and ALTER ONLINE TABLE#

ALTER TABLE commande FORCE;
ALTER TABLE commande ORDER BY date_commande, id DESC;
ALTER ONLINE TABLE commande ADD COLUMN note VARCHAR(50);

FORCE rebuilds the table without changing its definition, by the same path as an ALTER TABLE that modifies a column (file rewritten in full, indexes rebuilt); it combines with the other clauses.

ORDER BY column [ASC | DESC] [, ...], the last clause of the statement, checks that the columns exist in the table as it is after the statement (1054, "Unknown column 'x' in 'order clause'") and rebuilds the table by copying its rows in that order (collation of each column, NULL first); the order is not maintained beyond that (subsequent writes undo it) and only an ORDER BY in the query fixes the order of a result. On an in-memory table with a primary key (or, failing that, a uniqueness key on NOT NULL columns, then treated as the primary key), the rows stay in place and the statement returns the warning 1105 ("ORDER BY ignored as there is a user-defined clustered index in the table 't'"); a disk table (ENGINE = Aria…) is reordered, with no warning. A partitioned table is not reordered.

ALTER ONLINE TABLE is accepted: ONLINE has no effect, like LOCK = NONE. ALTER IGNORE TABLE (duplicates removed when a uniqueness key is created) is refused (1235).

IF [NOT] EXISTS clauses: behavior when not applied#

ALTER TABLE commande
    ADD COLUMN IF NOT EXISTS x INT,
    DROP COLUMN IF EXISTS x_ancien;

Each conditional clause (ADD [COLUMN], DROP [COLUMN], ADD / DROP {INDEX | KEY}, ADD UNIQUE [KEY | INDEX], ADD PRIMARY KEY, ADD / DROP FOREIGN KEY, CHANGE, MODIFY, RENAME COLUMN, RENAME {INDEX | KEY}, ALTER COLUMN, ALTER {INDEX | KEY}, DROP CONSTRAINT IF EXISTS name, ADD CONSTRAINT IF NOT EXISTS name CHECK (...)) is judged on the state left by the previous clauses of the same statement. A clause whose condition is not met (column already present for an ADD ... IF NOT EXISTS, column absent for a DROP ... IF EXISTS, etc.) is skipped and gives a note carrying the code of the underlying conflict (1060 duplicate column, 1061 duplicate index name, 1091 missing object, 1054 unknown column, 1176 index to rename or to mark missing, 1826 CHECK constraint of that name already on the table); the other clauses of the statement apply normally. If all the clauses are skipped this way, the whole statement returns OK with no error.

DROP PARTITION IF EXISTS p1, p2 skips the absent partitions (a note 1507 for each) and drops the others; ADD PARTITION IF NOT EXISTS (…) is skipped entirely, with the note 1517, if one of the names is already taken. A non-partitioned table remains an error (1505). DROP PRIMARY KEY IF EXISTS and DROP CHECK IF EXISTS do not exist (syntax error 1064): write DROP CONSTRAINT IF EXISTS name or DROP INDEX IF EXISTS.

Options with no effect#

[DEFAULT] CHARSET / CHARACTER SET alone, ALGORITHM = ..., LOCK = ... are accepted but have no effect on storage; COMMENT = 'text' changes the table comment, returned by SHOW CREATE TABLE (COMMENT='text'). ENGINE has no effect only for an engine that keeps the table in memory: ENGINE = Aria, MyISAM, or DISK converts the table into a disk table, and ENGINE = InnoDB (or MIRAJ, MEMORY) brings a disk table back into memory (see Storage engines). The following are errors (1235, not supported): DISCARD TABLESPACE, IMPORT TABLESPACE (irrelevant to MIRAJ storage), and ALTER IGNORE TABLE. A column can be declared INVISIBLE by ADD, MODIFY, or CHANGE (see "Column attributes" below).

CREATE [UNIQUE] INDEX and DROP INDEX#

CREATE [OR REPLACE] [UNIQUE | FULLTEXT | SPATIAL | VECTOR] INDEX [IF NOT EXISTS] name
    [USING {BTREE | HASH}] ON [db.]table (key_element [, ...])
    [index_options] [ALGORITHM [=] ...] [LOCK [=] ...];
DROP INDEX [IF EXISTS] name ON [db.]table;

CREATE INDEX idx_nom ON client (nom);
CREATE UNIQUE INDEX uq_code USING BTREE ON client (code) ALGORITHM = INPLACE LOCK = NONE;
CREATE OR REPLACE INDEX idx_nom ON client (nom, email);
DROP INDEX idx_nom ON client;
DROP INDEX IF EXISTS idx_nom ON client;

CREATE INDEX without UNIQUE creates a non-unique secondary index (see the dedicated section above); CREATE UNIQUE INDEX is equivalent to ALTER TABLE ... ADD UNIQUE. The options ALGORITHM = ... and LOCK = ... are accepted with no effect. DROP INDEX `PRIMARY` ON table drops the primary key. DROP INDEX name ON table is equivalent to ALTER TABLE table DROP INDEX name; DROP INDEX IF EXISTS name ON table does nothing (note 1091, Can't DROP INDEX `name`; check that it exists) if the table has no index of that name — an absent table remains an error (1146). CREATE INDEX IF NOT EXISTS does nothing (note 1061) if an index of that name already exists. CREATE OR REPLACE [UNIQUE | FULLTEXT | SPATIAL | VECTOR] INDEX name ON table (...) replaces the index of the same name, whatever its kind, in the same statement: if the creation fails (duplicate for a UNIQUE, unknown column, index still needed by a foreign key: 1553), the previous index stays in place; without an index of that name, it is a plain CREATE INDEX, with no note. OR REPLACE and IF NOT EXISTS are mutually exclusive (1221); USING BTREE / USING HASH is written before or after the table name and is still displayed with the index.

The index that carries the AUTO_INCREMENT column can be replaced only by a uniqueness key on that column alone: CREATE OR REPLACE UNIQUE INDEX k ON t (id) or ALTER TABLE t DROP INDEX k, ADD UNIQUE k2 (id) are accepted, whereas CREATE OR REPLACE INDEX k ON t (id), a key on several columns, or a lone DROP INDEX k return 1075 and leave the table unchanged.

CREATE INDEX and DROP INDEX require the INDEX privilege on the table (ALTER is not enough and is not required); ALTER TABLE … ADD INDEX requires ALTER.

Vector indexes (CREATE VECTOR INDEX, VECTOR INDEX in CREATE TABLE, and the form CREATE INDEX … USING hnsw (column class)) are described in chapter 11. Vector search.

Views#

CREATE [OR REPLACE] [CACHED] VIEW#

CREATE [OR REPLACE]
    [ALGORITHM = ...]
    [DEFINER = account]
    [SQL SECURITY {DEFINER | INVOKER}]
    [CACHED] VIEW [IF NOT EXISTS] [db.]name [(column [, ...])]
    AS query
    [WITH [CASCADED | LOCAL] CHECK OPTION];

-- Ordinary view: always reflects the current state of the tables
CREATE VIEW v_client_actif AS
SELECT id, nom, email FROM client WHERE actif = 1;

-- Cached view: frozen until the next REFRESH VIEW
CREATE CACHED VIEW v_bilan_mensuel AS
SELECT DATE_FORMAT(date_mvt, '%Y-%m') AS mois, SUM(montant) AS total
FROM mouvement
GROUP BY DATE_FORMAT(date_mvt, '%Y-%m');

OR REPLACE and IF NOT EXISTS are mutually exclusive (error 1221). ALGORITHM = TEMPTABLE makes the view non-updatable; MERGE and UNDEFINED have no other effect. WITH CHECK OPTION (see "Updatable views" below) requires an updatable view (error 1368 otherwise). The definitions are kept in <database>/views.mrv, written durably before the statement returns.

Ordinary view or CACHED view#

Updatable views#

INSERT, UPDATE, DELETE, REPLACE, and LOAD DATA can target a view: the statement is rewritten onto the table the view reads (each view column replaced by its expression, the view's WHERE condition added to that of an UPDATE or a DELETE), then executed as a direct write to the table: triggers, foreign keys, CHECK constraints, IGNORE, ON DUPLICATE KEY UPDATE, transactions, multi-table statements. A view built on an updatable view is updatable too, step by step.

CREATE VIEW v_client_actif AS SELECT id, nom, email FROM client WHERE actif = 1;
UPDATE v_client_actif SET email = '[email protected]' WHERE id = 7;   -- only if the client is active
DELETE FROM v_client_actif WHERE nom LIKE 'Test%';                -- active clients only
INSERT INTO v_client_actif VALUES (99, 'Nouveau', '[email protected]'); -- active: default value

A view is updatable when its query reads a single table (or updatable view) without DISTINCT, GROUP BY, HAVING, aggregate, window function, UNION, LIMIT, subquery in the select list, derived table, or information_schema table, with at least one column taken as-is, and when it is neither ALGORITHM = TEMPTABLE nor CACHED (the frozen result of a cached view would not follow writes: it stays read-only). information_schema.VIEWS.IS_UPDATABLE is YES for such a view.

Join view: an UPDATE can modify the columns of only one of its tables (inner joins only if it is not the first table of the view); an INSERT, with a column list, fills only one of its tables.

WITH CHECK OPTION: a row written by INSERT or UPDATE must remain visible in the view; otherwise error 1369 (CHECK OPTION failed) cancels the statement (with IGNORE: row discarded and warning 1369). A condition that evaluates to NULL is a failure. LOCAL checks the condition of the view and those of the views read that have their own option; CASCADED (the default) those of all the views read. An option carried by a join view, or a condition that contains a subquery, is refused at write time (1235). DELETE is not concerned.

Privileges: the caller must have the privilege on the view (INSERT, UPDATE, DELETE); the table is written with the privileges of the view's definer (1356 if the definer lacks them), or of the caller with SQL SECURITY INVOKER (1142). No privilege of the caller on the table is required.

Limits: a statement in the body of a trigger, or of a stored function called row by row, does not write through a view (1288 / 1471); an unqualified reference to a view column in a subquery of the statement is not translated (qualify it with the name of the view). A session temporary table masks the view of the same name.

A view can be built on another view (up to 32 levels of nesting; a cycle returns error 1462).

REFRESH VIEW#

REFRESH VIEW v_bilan_mensuel;
REFRESH VIEW v_bilan_mensuel, v_autre_vue;

Applies only to a CACHED view: recomputes its result immediately and replaces the frozen version. No particular effect on an ordinary view (whose result, if kept, already updates itself).

ALTER VIEW#

ALTER VIEW v_client_actif AS
SELECT id, nom, email, telephone FROM client WHERE actif = 1;

Same syntax as CREATE VIEW (except IF NOT EXISTS): fully redefines the view.

DROP VIEW#

DROP VIEW [IF EXISTS] v_client_actif [, v_autre_vue ...] [RESTRICT | CASCADE];

RESTRICT and CASCADE are accepted with no effect. Unknown views are cited together by error 4092 ("Unknown VIEW: 'd.absente1,d.absente2'"); with IF EXISTS, one note 4092 per absent view, the others being dropped.

SHOW CREATE VIEW#

SHOW CREATE VIEW v_client_actif;

Returns the canonical definition of the view, as recorded (including CACHED where applicable), with the columns View, Create View, character_set_client, and collation_connection:

CREATE VIEW v_actif AS SELECT id, nom, email FROM cli WHERE actif = 1 WITH CHECK OPTION;
SHOW CREATE VIEW v_actif;   -- Create View column:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_actif` AS SELECT id, nom, email FROM cli WHERE actif = 1 WITH CASCADED CHECK OPTION

information_schema.VIEWS lists the views of the current database; information_schema.COLUMNS describes only the views whose result is currently in memory.

Syntax supplements: CREATE TABLE#

This section complements the previous ones: column attributes, table options, named constraints, temporary tables. Every statement has been verified on the engine.

Column attributes: what counts, what is ignored#

An unknown attribute keyword is a syntax error (1064). A column definition repeating GENERATED is rejected.

Table options#

All the options placed after the closing parenthesis are accepted by the parser (ENGINE, [DEFAULT] CHARSET, COLLATE, COMMENT, ROW_FORMAT, etc., with or without =) and ignored, with these exceptions:

• COMMENT [=] 'text' is kept and returned by SHOW CREATE TABLE () COMMENT='text');
• ENCRYPTION [=] 'Y' | 'N' sets the encryption of the table files (see Encryption at rest);

• WITH SYSTEM VERSIONING keeps the history of rows (see chapter 12);
• AUTO_INCREMENT = n sets the first number assigned (0 is ignored);
• ENGINE = Aria, MyISAM, or DISK creates a disk table (see Storage engines);
• PARTITION BY ... defines the partitioning (see below);
• [DEFAULT] COLLATE = name (with or without CHARSET) sets the default collation of text columns written without COLLATE (see Collations).

SHOW CREATE TABLE returns DEFAULT CHARSET=utf8mb4 COLLATE=name only if the table default is not utf8mb4_general_ci (and CHARACTER SET utf8mb4 COLLATE name after the type of a column that differs from it), and returns ENGINE only for a disk table; it does not return the value of the AUTO_INCREMENT counter.

CREATE TABLE cm (a INT COMMENT 'col', b INT) COMMENT 'table' ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8mb4;
SHOW CREATE TABLE cm;

CREATE TABLE `cm` (
  `a` int(11) DEFAULT NULL COMMENT 'col',
  `b` int(11) DEFAULT NULL
) COMMENT='table'

Named constraints#

In front of PRIMARY KEY, UNIQUE, FOREIGN KEY, and CHECK, CONSTRAINT name gives a name:

CREATE TABLE y (
    a INT, b INT,
    CONSTRAINT pk_y PRIMARY KEY (a),
    CONSTRAINT uq_b UNIQUE (b),
    CONSTRAINT ck_a CHECK (a > 0)
);

The primary key is always called PRIMARY (pk_y is ignored). The name of uq_b becomes that of the index (UNIQUE KEY uq_b). A table CHECK constraint without a name receives CONSTRAINT_<n> (counter specific to the table), a column constraint the name of its column; CHECK names are unique within the table (error 1826 on a duplicate), two tables being able to carry the same name. information_schema.CHECK_CONSTRAINTS gives the table (TABLE_NAME) and the level (LEVEL: Column or Table) of each constraint.

A CHECK violation returns error 4025:

INSERT INTO y VALUES (0, 1);
-- ERROR 4025 (23000): CONSTRAINT `ck_a` failed for `d`.`y`

A primary key declared both on a column (a INT PRIMARY KEY) and by a table PRIMARY KEY (b) clause is merged into a composite key (a, b); two table PRIMARY KEY clauses, or two PRIMARY KEY columns, return error 1068 ("Multiple primary key defined").

Temporary tables#

CREATE TEMPORARY TABLE [IF NOT EXISTS] [db.]t (...);
CREATE GLOBAL TEMPORARY TABLE [db.]t (...);      -- MIRAJ extension
DROP TEMPORARY TABLE [IF EXISTS] t;

• In memory only: no data file, no journal. Never listed by SHOW TABLES or by information_schema.
• A session temporary table masks a database table of the same name as long as it exists; DROP TABLE first drops the temporary one, then (failing that) the database table.
• CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE do not commit the current transaction; the rows, for their part, remain transactional.
• Privilege: CREATE TEMPORARY TABLES on the database (no check afterwards for the session form); the GLOBAL form follows table privileges.
• The LIKE and [AS] SELECT forms are accepted as for an ordinary table.
• Refused: foreign key on or to a temporary table (error 1215), trigger (1361), partitioning (1562), system database.

Syntax supplements: ALTER TABLE#

Additional forms#

-- Several columns in parentheses
ALTER TABLE p ADD COLUMN (a INT, b INT DEFAULT 7);

-- Position
ALTER TABLE p ADD COLUMN z INT AFTER nom, ADD COLUMN y INT FIRST;

-- CHECK constraints
ALTER TABLE p ADD CONSTRAINT ck_q CHECK (qte >= 0);
ALTER TABLE p ALTER CHECK ck_q NOT ENFORCED;          -- MIRAJ extension
ALTER TABLE p ALTER CONSTRAINT ck_q ENFORCED;         -- synonym, MIRAJ extension
ALTER TABLE p DROP CONSTRAINT ck_q;                   -- or DROP CHECK ck_q
ALTER TABLE p DROP CONSTRAINT IF EXISTS ck_q;         -- absent: note 1091
ALTER TABLE p ADD CONSTRAINT IF NOT EXISTS ck_q CHECK (qte >= 0);

-- Uniqueness constraints and named keys
ALTER TABLE p ADD CONSTRAINT uq_nom UNIQUE (nom);
ALTER TABLE p DROP INDEX uq_nom;

-- Default value set to NULL
ALTER TABLE p ALTER COLUMN qte SET DEFAULT NULL;

Notes:

• DROP COLUMN col accepts a trailing RESTRICT or CASCADE, with no effect.
• ADD CHECK and any rebuild of the table reread all the rows to check the constraint. A NOT ENFORCED constraint is kept but never evaluated.
• ADD / DROP VECTOR INDEX: see chapter 11. Vector search.
• ADD SYSTEM VERSIONING and DROP SYSTEM VERSIONING add or remove the row history; the other clauses on a versioned table that change its columns require system_versioning_alter_history = KEEP (4119 otherwise) and also apply to its history; ADD PERIOD [IF NOT EXISTS] FOR p (start, end), DROP PERIOD [IF EXISTS] FOR p, and the keys (…, p WITHOUT OVERLAPS) manage application-time periods (see chapter 12).
• The options ENGINE = ..., ALGORITHM = ..., LOCK = ... can be combined with other clauses: ALTER TABLE p ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE succeeds without changing anything on an in-memory table. On a disk table, ENGINE=InnoDB brings it back into memory.
• A table is rebuilt in memory and its file rewritten in full: count two in-memory copies for the modified columns. This is also true of a disk table: its ALTER TABLE writes a new .dmrj file.

Errors specific to ALTER TABLE#

Runnable example#

CREATE TABLE p (id INT PRIMARY KEY AUTO_INCREMENT, nom VARCHAR(10) NOT NULL, qte INT DEFAULT 5);
INSERT INTO p (nom) VALUES ('ab'), ('cd');

ALTER TABLE p ADD COLUMN z INT AFTER nom, ADD COLUMN y INT FIRST;
DESCRIBE p;

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| y     | int(11)     | YES  |     | NULL    |                |
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| nom   | varchar(10) | NO   |     | NULL    |                |
| z     | int(11)     | YES  |     | NULL    |                |
| qte   | int(11)     | YES  |     | 5       |                |
+-------+-------------+------+-----+---------+----------------+

ALTER TABLE p ADD COLUMN z INT;
-- ERROR 1060 (42S21): Duplicate column name 'z'
ALTER TABLE p ADD COLUMN IF NOT EXISTS z INT;
-- OK, 0 row(s) affected; Warning: Duplicate column name 'z'
ALTER TABLE p MODIFY nom VARCHAR(1) NOT NULL;
-- ERROR 1265 (01000): Data truncated for column 'nom' at row 1
ALTER TABLE p DROP PRIMARY KEY;
-- ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Full-text index (FULLTEXT) and trigrams#

CREATE TABLE art (
  id INT PRIMARY KEY,
  designation VARCHAR(100),
  notes TEXT,
  FULLTEXT ft (designation WEIGHT 3, notes) COMMENT 'recherche des articles',
  KEY tri (designation) COMMENT 'miraj:trigram'
);
ALTER TABLE art ADD FULLTEXT ft_notes (notes);
CREATE FULLTEXT INDEX ft_des ON art (designation WEIGHT 2.5, notes);

A FULLTEXT index serves MATCH (columns) AGAINST (…) (chapter 9, §9.22) on CHAR, VARCHAR, TEXT, or JSON columns of the same collation (1283 otherwise, and for an INT or BLOB column); WITH PARSER is refused (1128), as is a temporary table (1796) and a disk table (1214). The default name is that of the first column (_2, _3… afterwards). The index follows a renamed column (CHANGE), loses a dropped column, and disappears with its last column; CREATE TABLE … LIKE copies it. It is kept in memory and rebuilt when the table is opened; CHECK TABLE compares it to a recomputed index (terms, frequencies, and row lengths), REPAIR TABLE rebuilds it. The stopword list in force (innodb_ft_enable_stopword, innodb_ft_user_stopword_table, innodb_ft_server_stopword_table) is frozen in the index when it is created.

Per-column weights (MIRAJ extension). In the column list of a FULLTEXT index (in its three forms: CREATE TABLE, ALTER TABLE … ADD FULLTEXT, CREATE FULLTEXT INDEX), a column can be followed by WEIGHT n: a strictly positive real number, integer or decimal (3, 2.5), 1 when absent. In scoring, a word found in a column counts for its weight (designation WEIGHT 3, notes: a word in the designation weighs three times a word in the notes), in both modes of miraj_ft_ranking; the index and the rows found do not change.

• Zero, negative, or non-numeric weight (WEIGHT 0, WEIGHT -1, WEIGHT 'deux'): 9051; WEIGHT without a value, or in an index that is not FULLTEXT (KEY, UNIQUE, PRIMARY KEY, trigram, SPATIAL): 1064.
• SHOW CREATE TABLE returns FULLTEXT KEY `ft` (`designation` WEIGHT 3,`notes`): weight 1 omitted, number without a useless zero. This text is reread by MIRAJ (miraj-dump export, replication, Cluster edition); since WEIGHT is a MIRAJ extension, remove the weights from a script intended for another server.
• The weight follows its renamed column (CHANGE, RENAME COLUMN), goes away with a dropped column, is copied by CREATE TABLE … LIKE, and is kept on reopen. SHOW INDEX and information_schema.STATISTICS do not show the weights (read SHOW CREATE TABLE).
• WEIGHT is not a reserved word: a column named weight can be named and weighted normally (FULLTEXT (weight WEIGHT 2, nom)).

The comment of a FULLTEXT index is free-form: it is returned as written by SHOW CREATE TABLE, SHOW INDEX (Index_comment), and information_schema.STATISTICS, with no effect on the index.

Trigram. KEY name (col) COMMENT 'miraj:trigram' (a CHAR or VARCHAR column) creates a trigram index, a MIRAJ extension that serves col LIKE '%…%' (and LOWER(col) / UPPER(col) LIKE …) without changing the SQL: SHOW INDEX returns it with Index_type = TRIGRAM (and Index_comment = miraj:trigram); another server reads the same declaration as an ordinary commented index. On a TEXT column, it returns error 1283.

Storage engines: in-memory tables and disk tables#

MIRAJ stores a table in one of two ways:

Both engines offer the same SQL: INSERT, UPDATE, DELETE, SELECT, transactions and ROLLBACK, primary and unique keys, indexes, foreign keys, CHECK constraints, triggers, AUTO_INCREMENT, ALTER TABLE, RENAME, TRUNCATE, DROP. A disk table is as durable as an in-memory table: its committed writes go through the database journal and are replayed after a hard stop.

Choosing the engine#

The engine is chosen with the ENGINE clause of CREATE TABLE (case-insensitive, with or without =):

CREATE TABLE mouvements_2019 (
    id      BIGINT PRIMARY KEY AUTO_INCREMENT,
    compte  INT NOT NULL,
    montant DECIMAL(12, 2),
    libelle VARCHAR(200),
    KEY (compte)
) ENGINE = Aria;

SHOW CREATE TABLE mouvements_2019;   -- … ) ENGINE=Aria

A disk table keeps the declared name: an existing script written with ENGINE=Aria or ENGINE=MyISAM, or a reread miraj-dump, therefore recreates the same disk tables. information_schema.TABLES.ROW_FORMAT is Page for a disk table, Dynamic for an in-memory table.

Default engine. A CREATE TABLE without ENGINE follows the variable default_storage_engine (MIRAJ by default), which can be changed for the session or for the server:

SET default_storage_engine = Aria;          -- disk tables for this session
SET GLOBAL default_storage_engine = MyISAM; -- sessions opened afterwards
SET default_storage_engine = DEFAULT;       -- takes the server value again

An unknown name is refused (1286); HEAP is displayed as MEMORY. CREATE TABLE … SELECT follows default_storage_engine; CREATE TABLE … LIKE takes the engine of the source table.

Changing engine. ALTER TABLE t ENGINE = Aria converts an in-memory table into a disk table; ALTER TABLE t ENGINE = InnoDB does the reverse. The rows are copied from one file to the other. Any other ALTER TABLE clause keeps the table's engine.

SHOW ENGINES and information_schema.ENGINES list MIRAJ (DEFAULT), then DISK, Aria, and MyISAM (YES, "Alias of DISK"), all transactional (Transactions, XA, and Savepoints at YES).

Temporary tables#

A temporary table always stays in memory. CREATE TEMPORARY TABLE … ENGINE = Aria (or ALTER TABLE … ENGINE = MyISAM on a temporary table) creates the table in memory with warning 1266 ("Using storage engine MIRAJ for table 'tt'"). default_storage_engine does not apply to temporary tables, and default_tmp_storage_engine is accepted with no effect.

What a disk table does not allow yet#

These features are refused, at creation as well as by ALTER TABLE, CREATE INDEX, or ALTER TABLE … ENGINE (never silently ignored):

Other differences:

• The space of rows deleted from a disk table is not yet reclaimed, neither automatically nor by OPTIMIZE TABLE: the .dmrj file does not shrink.
• A damaged page of the .dmrj file (wrong CRC) is reported by CHECK TABLE and repaired by REPAIR TABLE (see Table maintenance).
• Full scans (SUM, COUNT(*) WHERE …, sort without an index) are slower than on an in-memory table. Reads by primary key and COUNT(*) without WHERE are just as fast.
• Modified pages stay in memory until the next save point: a very large load into a disk table occupies memory until that point.

A script that creates an ENGINE=MyISAM table with a FULLTEXT index is therefore refused (1214): remove the ENGINE clause (or replace it with ENGINE=InnoDB) to keep that table in memory.

Partitioning#

Syntax#

CREATE TABLE table (...)
    PARTITION BY {
        [LINEAR] HASH (expression)
      | [LINEAR] KEY [ALGORITHM = {1 | 2}] ([column [, ...]])
      | RANGE (expression)
      | RANGE COLUMNS (column [, ...])
      | LIST (expression)
      | LIST COLUMNS (column [, ...])
    }
    [PARTITIONS n]
    [SUBPARTITION BY {[LINEAR] HASH (expression) | [LINEAR] KEY (...)} [SUBPARTITIONS n]]
    [(partition_definition [, ...])];

partition_definition :
    PARTITION name
        [VALUES {LESS THAN {(value [, ...]) | MAXVALUE} | IN (value | (value, ...) [, ...])}]
        [COMMENT [=] 'text'] [MAX_ROWS [=] n] [MIN_ROWS [=] n]
        [{DATA | INDEX} DIRECTORY [=] 'path'] [TABLESPACE [=] name] [[STORAGE] ENGINE [=] engine]
        [NODE [=] 'node']
        [(SUBPARTITION name [options] [, ...])]

NODE 'node' (Cluster edition, 9002 elsewhere) stores the rows of the partition on that single node of the cluster instead of copying them to all: see chapter 17, §17.10.

The clause is written after the table options, or in a versioned comment /*!50100 ... */ (this is how SHOW CREATE TABLE and miraj-dump return it). CREATE TABLE t PARTITION BY ... SELECT ... partitions a table filled by a query. CREATE TABLE ... LIKE copies the partitioning.

Runnable example#

CREATE TABLE ventes (
    id INT NOT NULL, annee INT NOT NULL, montant DECIMAL(10,2),
    PRIMARY KEY (id, annee)
) PARTITION BY RANGE (annee) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
);
INSERT INTO ventes VALUES (1, 2023, 10), (2, 2024, 20), (3, 2030, 30);

SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_DESCRIPTION
FROM information_schema.PARTITIONS WHERE TABLE_NAME = 'ventes';

+----------------+------------------+-----------------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_DESCRIPTION |
+----------------+------------------+-----------------------+
| p2023          | RANGE            | 2024                  |
| p2024          | RANGE            | 2025                  |
| pmax           | RANGE            | MAXVALUE              |
+----------------+------------------+-----------------------+

ALTER TABLE ventes DROP PARTITION p2023;      -- also drops the rows of the partition
SELECT id, annee FROM ventes ORDER BY id;     -- (2, 2024) and (3, 2030)
ALTER TABLE ventes TRUNCATE PARTITION pmax;   -- empties the partition

Partition management (ALTER TABLE)#

A partition management clause cannot be combined with other clauses in the same statement. On a non-partitioned table, these clauses return error 1505.

Rules and errors#

What depends on the edition#

• Express and Enterprise: the definition is checked, kept, and returned, but the rows stay in the single file of the table (no pruning or per-partition reads). SELECT ... FROM t PARTITION (p), INSERT INTO t PARTITION (p), UPDATE / DELETE / LOAD DATA with PARTITION (p), EXCHANGE PARTITION, and TRUNCATE PARTITION of a HASH / KEY partition (or of a subpartition) return error 9002 (Cluster edition required); TRUNCATE PARTITION ALL, or the list of all the partitions, remains allowed.
• Cluster: each partition (or subpartition) is a separate file segment (#p#<partition>.mrj), with read pruning and locks limited to the segments concerned. A table stored in segments is unreadable from another edition (error 1194). See Cluster and replication.

TRUNCATE: details#

TRUNCATE [TABLE] [db.]table;

• The AUTO_INCREMENT counter restarts at 1:

  CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT, n INT);
  INSERT INTO t (n) VALUES (1), (2), (3);
  DELETE FROM t;                 -- keeps the counter
  INSERT INTO t (n) VALUES (9);  -- id = 4
  TRUNCATE TABLE t;              -- resets the counter to 1
  INSERT INTO t (n) VALUES (9);  -- id = 1

• TRUNCATE fires no DELETE trigger.
• A table referenced by another table's foreign key cannot be emptied by TRUNCATE, even if no row is referenced: error 1701. Use DELETE FROM or drop the foreign key first.
• TRUNCATE on a view: error 1347 ('db.view' is not BASE TABLE). Unknown table: 1146.
• On a partitioned table, TRUNCATE takes all the segments (see the section "Partitioning").

Foreign keys: example and verified behaviors#

CREATE TABLE parent (id INT PRIMARY KEY AUTO_INCREMENT, nom VARCHAR(10));
CREATE TABLE enfant (
    id  INT PRIMARY KEY AUTO_INCREMENT,
    pid INT,
    FOREIGN KEY (pid) REFERENCES parent (id) ON DELETE CASCADE ON UPDATE CASCADE
);
INSERT INTO parent (nom) VALUES ('a'), ('b'), ('c');
INSERT INTO enfant (pid) VALUES (1), (1), (2);

INSERT INTO enfant (pid) VALUES (9);
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
--   (`d`.`enfant`, CONSTRAINT `enfant_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `parent` (`id`)
--   ON DELETE CASCADE ON UPDATE CASCADE)

DELETE FROM parent WHERE id = 1;          -- also drops the two child rows with pid = 1
UPDATE parent SET id = 20 WHERE id = 2;   -- the child row follows: pid = 20

DROP TABLE parent;
-- ERROR 3730 (HY000): Cannot drop table 'parent' referenced by a foreign key constraint
--   'enfant_ibfk_1' on table 'enfant'.
TRUNCATE parent;
-- ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (...)

ALTER TABLE enfant DROP FOREIGN KEY enfant_ibfk_1;
DROP TABLE parent;                        -- now allowed

• A NULL value in the foreign key column does not need a parent.
• Checks are immediate, row by row, in the processing order of the statement (see chapter DML, "Foreign keys"): a multi-row insert cannot reference a row that it inserts further on.
• The referenced table must have an index (primary key or UNIQUE) on the columns cited; otherwise creation fails (error 1822 "Missing index for constraint"). A referenced table not found returns 1824.
• A foreign key to a table in another database: error 1235.
• Child rows modified by a cascade fire no trigger, no ON UPDATE CURRENT_TIMESTAMP timestamp, and no CHECK check.

Sequences#

A sequence is a database object, in the same namespace as tables and views, that hands out increasing (or decreasing) 64-bit integers on demand.

CREATE [OR REPLACE] SEQUENCE [IF NOT EXISTS] [db.]s
    [START [WITH] n] [INCREMENT [BY] n]
    [MINVALUE n | NO MINVALUE] [MAXVALUE n | NO MAXVALUE]
    [CACHE n | NOCACHE] [CYCLE | NOCYCLE];
ALTER SEQUENCE [IF EXISTS] s [options] [RESTART [WITH n]];
DROP SEQUENCE [IF EXISTS] s [, ...];
SHOW CREATE SEQUENCE s;

Default values: START at the minimum (at the maximum if the step is negative), INCREMENT BY 1, MINVALUE 1, MAXVALUE 9223372036854775806, CACHE 1000, NOCYCLE. The words WITH and BY are optional (START 5 INCREMENT -1); NOCACHE is equivalent to CACHE 0.

CREATE SEQUENCE seq_facture START WITH 1000 INCREMENT BY 1;
CREATE TABLE facture (n BIGINT DEFAULT NEXTVAL(seq_facture) PRIMARY KEY, client INT);
INSERT INTO facture (client) VALUES (7), (8);     -- n = 1000, 1001
SELECT NEXT VALUE FOR seq_facture;                -- 1002
SHOW CREATE SEQUENCE seq_facture;
-- CREATE SEQUENCE `seq_facture` start with 1000 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle

CREATE SEQUENCE compte_a_rebours START 5 INCREMENT -1 MINVALUE 1 MAXVALUE 5 CYCLE NOCACHE;
SELECT NEXTVAL(compte_a_rebours), NEXTVAL(compte_a_rebours), NEXTVAL(compte_a_rebours),
       NEXTVAL(compte_a_rebours), NEXTVAL(compte_a_rebours), NEXTVAL(compte_a_rebours);
-- 5 | 4 | 3 | 2 | 1 | 5       (CYCLE: starts again from the maximum; cycle_count is then 1)

CREATE SEQUENCE courte MAXVALUE 2;
SELECT NEXTVAL(courte), NEXTVAL(courte);          -- 1 | 2
SELECT NEXTVAL(courte);
-- ERROR 4084 (HY000): Sequence 'd.courte' has run out

The sequence calls of a statement are checked when it is opened, before any execution, even if they would never be evaluated (SELECT NEXTVAL(t) FROM t WHERE 0, IF(0, NEXTVAL(t), 1) return 4089). In a routine, each statement of the body is checked when it runs. DROP SEQUENCE of a list that mixes sequences and other objects returns an error (4090, 1965, or 4091), but still drops the sequences cited; with IF EXISTS, each refused object gives a note (1965, 4090, 4091) and the sequences are dropped.

A sequence does not depend on any transaction: a value served is never given back by ROLLBACK. The state is written durably to disk before the reserve of CACHE values is served; after a hard stop or a restart, the sequence resumes at the end of the last reserve (values reserved but not served are lost, never served twice). CACHE 0 (NOCACHE) writes at every value. Privileges: INSERT on the sequence for NEXTVAL and SETVAL, SELECT for LASTVAL. Sequences are included in BACKUP / RESTORE and in miraj-dump. The error codes are detailed in chapter 28.

Sequences appear in information_schema.TABLES (type SEQUENCE) and in information_schema.SEQUENCES, one row per sequence with the columns of the SQL standard: SEQUENCE_CATALOG (def), SEQUENCE_SCHEMA, SEQUENCE_NAME, DATA_TYPE (always bigint), NUMERIC_PRECISION (64), NUMERIC_PRECISION_RADIX (2), NUMERIC_SCALE (0), START_VALUE, MINIMUM_VALUE, MAXIMUM_VALUE, INCREMENT (as text), and CYCLE_OPTION (YES / NO). SHOW CREATE SEQUENCE returns the text of the sequence without an ENGINE clause, like SHOW CREATE TABLE for an in-memory table.

Unsupported syntaxes (DDL)#

These forms are recognized by the parser but refused with error 1235 ("This version of Miraj doesn't yet support ..."), unless stated otherwise:

Table maintenance#

CHECK TABLE[S] t [, ...] [QUICK | FAST | CHANGED | FOR UPGRADE | MEDIUM | EXTENDED];
REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE[S] t [, ...] [QUICK] [EXTENDED] [USE_FRM] [FORCE];
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE[S] t [, ...] [WAIT n | NOWAIT];
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE[S] t [, ...]
    [PERSISTENT FOR {ALL | COLUMNS (col, ...) INDEXES (index, ...)}];

These statements return four columns Table, Op, Msg_type, and Msg_text, with one or several rows per table cited. The last row of each table gives the status (status or error). If one table fails, the following ones are still processed; only the absence of a current database makes the whole statement fail (1046).

CHECK TABLE cli, absente;

+-----------+-------+----------+---------------------------------+
| Table     | Op    | Msg_type | Msg_text                        |
+-----------+-------+----------+---------------------------------+
| d.cli     | check | status   | OK                              |
| d.absente | check | Error    | Table 'd.absente' doesn't exist |
| d.absente | check | status   | Operation failed                |
+-----------+-------+----------+---------------------------------+

ANALYZE TABLE returns one status / OK row per table; with PERSISTENT FOR …, an Engine-independent statistics collected row precedes it (see also chapter 9).

Disk tables (ENGINE = Aria, MyISAM, or DISK):

• CHECK TABLE (from MEDIUM) rereads every page of the .dmrj file: checksum (CRC), page number and file, row slots; EXTENDED also decodes every row. Each damaged page gives an error row (Fichier « t.dmrj » : … page 12 : CRC …). A page modified since the last save point is not reread: its in-memory version will replace the one in the file.
• REPAIR TABLE of an open table with damaged pages rereads its rows (those of a page still in the cache remain readable) and rewrites them, at the same numbers, in a new file. If rows are unreadable, only EXTENDED agrees to lose them, after copying the files to quarantine.
• The superblock (page 0) has a copy at the end of the first extent (page 63), and the table schema is written in two copies: if one is damaged, the table opens on the other, which rewrites it (noted in the database recovery notes). CHECK TABLE also reports a damaged superblock, copy of it, or schema copy. A .dmrj file written before these copies does not have them; it receives them when it is rewritten in full (TRUNCATE, ALTER TABLE, REPAIR TABLE).
• A damaged page found when the database is opened makes the table unreadable (error 1194). REPAIR TABLE alone leaves it as is; REPAIR TABLE … EXTENDED copies the .dmrj and its .dbmrj to quarantine, recovers the rows of the readable pages, reports the lost pages (warning row), and rewrites the table. If no page is lost (only values are), the journal is replayed as for an in-memory table; otherwise the row numbers have changed and the changes journaled after the last save point are not replayed (reported).
• A power cut during a save point tears no page: the modified pages, the superblock and, after a DDL, the pages of the new schema are first written and synced to <table>.dmrj.ckpt, then to their place; at open, a complete .ckpt is replayed and an incomplete .ckpt ignored (nothing had been written in its place). No page is written outside this path: the database's double write (doublewrite.mrw) is not used yet.
• CHECK TABLE … EXTENDED also checks, for each live row, its values stored in the .dbmrj LOB store (store absent: référence de LOB sans magasin).

Last row of OPTIMIZE TABLE:

DELETE FROM mouvements WHERE date_mvt < '2020-01-01';
OPTIMIZE TABLE mouvements;
-- compta.mouvements | optimize | info   | 184320 lignes supprimées retirées
-- compta.mouvements | optimize | status | OK

Without OPTIMIZE TABLE, deleted rows are removed automatically: during writes as soon as they reach 20% of the table, and at every save point. See Server administration, § 16.4.3 for details (manual policy, Cluster edition).

REPAIR and OPTIMIZE commit the session's open transaction and take the catalog in write mode: other statements wait until they finish. A view receives, from REPAIR and OPTIMIZE, an Error row ("'d.v' is not BASE TABLE") followed by Operation failed; CHECK TABLE of a view returns OK. information_schema tables and system tables receive a "doesn't support" note. The detail messages specific to MIRAJ (info, warning, error of the file checks) are in French.

Stored routines, triggers, and events#

The following sections describe the syntax of the MIRAJ language. In miraj-cli, a BEGIN … END body that contains ; is written between DELIMITER // and DELIMITER ;. The privileges (CREATE ROUTINE, ALTER ROUTINE, EXECUTE, TRIGGER, EVENT) are described in chapter 15.

CREATE PROCEDURE and CREATE FUNCTION#

CREATE [OR REPLACE] [DEFINER = {account | CURRENT_USER}]
    PROCEDURE [IF NOT EXISTS] [db.]name ([[IN | OUT | INOUT] param type [DEFAULT expr] [, ...]])
    [characteristic ...] body;

CREATE [OR REPLACE] [DEFINER = {account | CURRENT_USER}]
    FUNCTION [IF NOT EXISTS] [db.]name ([param type [DEFAULT expr] [, ...]])
    RETURNS type [characteristic ...] body;

characteristic :
    COMMENT 'text' | LANGUAGE SQL | [NOT] DETERMINISTIC
  | CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA
  | SQL SECURITY {DEFINER | INVOKER}

DROP {PROCEDURE | FUNCTION} [IF EXISTS] [db.]name;
CALL [db.]name[([argument [, ...]])];
SHOW CREATE {PROCEDURE | FUNCTION} name;
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern' | WHERE condition];

The body is a single SQL statement (RETURN expr for a function) or a block [label:] BEGIN … END [label] that contains, in this order, its declarations then its statements:

DELIMITER //
CREATE PROCEDURE ajuster(IN p_id INT, IN delta INT, OUT p_qte INT)
  MODIFIES SQL DATA
  COMMENT 'Ajuste un stock'
BEGIN
  DECLARE courant INT DEFAULT 0;
  DECLARE EXIT HANDLER FOR NOT FOUND SET p_qte = NULL;
  SELECT qte INTO courant FROM stock WHERE id = p_id;
  UPDATE stock SET qte = courant + delta WHERE id = p_id;
  SET p_qte = courant + delta;
END//
CREATE PROCEDURE total_stock(OUT total INT)
  READS SQL DATA
BEGIN
  DECLARE fini INT DEFAULT 0;
  DECLARE q INT;
  DECLARE c CURSOR FOR SELECT qte FROM stock;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fini = 1;
  SET total = 0;
  OPEN c;
  lire: LOOP
    FETCH c INTO q;
    IF fini THEN LEAVE lire; END IF;
    SET total = total + q;
  END LOOP;
  CLOSE c;
END//
CREATE FUNCTION categorie(m DECIMAL(10,2)) RETURNS VARCHAR(10) DETERMINISTIC
BEGIN
  CASE
    WHEN m < 100 THEN RETURN 'petit';
    WHEN m < 1000 THEN RETURN 'moyen';
    ELSE RETURN 'gros';
  END CASE;
END//
DELIMITER ;

CREATE TABLE stock (id INT PRIMARY KEY, qte INT NOT NULL, maj DATETIME);
INSERT INTO stock VALUES (1, 10, NULL), (2, 7, NULL);
CALL ajuster(1, 5, @q);   SELECT @q;      -- 15
CALL inconnue();
-- ERROR 1305 (42000): PROCEDURE d.inconnue does not exist
CALL ajuster(99, 5, @q);  SELECT @q;      -- NULL (NOT FOUND handler)
CALL total_stock(@tot);   SELECT @tot;    -- 22
SELECT categorie(50), categorie(500), categorie(5000);   -- petit | moyen | gros

SHOW CREATE PROCEDURE returns the text of the routine with its definer (CREATE DEFINER=`root`@`localhost` PROCEDURE `ajuster`(…)), the sql_mode, and the collation of its creation; information_schema.ROUTINES describes each routine (ROUTINE_TYPE, DATA_TYPE of the result, IS_DETERMINISTIC, SQL_DATA_ACCESS, SECURITY_TYPE). A call statement without parentheses (CALL p) is allowed for a procedure without parameters.

CREATE TRIGGER#

CREATE [OR REPLACE] [DEFINER = {account | CURRENT_USER}]
    TRIGGER [IF NOT EXISTS] [db.]name
    {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table
    FOR EACH ROW [{FOLLOWS | PRECEDES} other_trigger]
    body;
DROP TRIGGER [IF EXISTS] [db.]name;
SHOW TRIGGERS [FROM db] [LIKE 'pattern' | WHERE condition];
SHOW CREATE TRIGGER name;

The body runs once per row written. NEW.col (insert, update) designates the row written, OLD.col (update, delete) the row from before; a BEFORE trigger can modify the row with SET NEW.col = …, and an error raised (SIGNAL) cancels the statement that fired it. Several triggers of the same timing and the same event run in creation order, which FOLLOWS / PRECEDES adjust: with t1, t2, then t3 … PRECEDES t1, the order is t3, t1, t2.

DELIMITER //
CREATE TRIGGER stock_bu BEFORE UPDATE ON stock FOR EACH ROW
BEGIN
  IF NEW.qte < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Stock négatif';
  END IF;
  SET NEW.maj = '2026-10-19 08:00:00';
END//
DELIMITER ;
CREATE TABLE journal (id INT PRIMARY KEY AUTO_INCREMENT, article INT, ancien INT, nouveau INT);
CREATE TRIGGER stock_au AFTER UPDATE ON stock FOR EACH ROW
  INSERT INTO journal (article, ancien, nouveau) VALUES (OLD.id, OLD.qte, NEW.qte);

UPDATE stock SET qte = qte + 5 WHERE id = 2;
UPDATE stock SET qte = qte - 50 WHERE id = 2;
-- ERROR 1644 (45000): Stock négatif
SELECT * FROM stock WHERE id = 2;
SELECT * FROM journal;

+----+-----+---------------------+
| id | qte | maj                 |
+----+-----+---------------------+
|  2 |  12 | 2026-10-19 08:00:00 |
+----+-----+---------------------+
+----+---------+--------+---------+
| id | article | ancien | nouveau |
+----+---------+--------+---------+
|  1 |       2 |      7 |      12 |
+----+---------+--------+---------+

CREATE EVENT#

CREATE [OR REPLACE] [DEFINER = {account | CURRENT_USER}]
    EVENT [IF NOT EXISTS] [db.]name
    ON SCHEDULE {AT instant | EVERY n unit [STARTS instant] [ENDS instant]}
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'text']
    DO body;

ALTER [DEFINER = account] EVENT [db.]name
    [ON SCHEDULE …] [ON COMPLETION [NOT] PRESERVE] [RENAME TO new_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'text'] [DO body];
DROP EVENT [IF EXISTS] [db.]name;
SHOW EVENTS [FROM db] [LIKE 'pattern' | WHERE condition];
SHOW CREATE EVENT name;

An event runs its body at the AT instant, or every n units (SECOND, MINUTE, HOUR, DAY, WEEK, MONTH…) between STARTS and ENDS, as long as the scheduler is running: variable event_scheduler (ON, OFF, or DISABLED at startup through --event-scheduler), which can be changed with SET GLOBAL event_scheduler = ON. A finished event (AT instant passed, or ENDS reached) is dropped; with ON COMPLETION PRESERVE, it stays, in the DISABLED state. DISABLE ON SLAVE displays it as SLAVESIDE_DISABLED.

CREATE EVENT ev_purge ON SCHEDULE EVERY 1 DAY STARTS '2026-11-01 03:00:00'
  ON COMPLETION PRESERVE COMMENT 'Purge nocturne'
  DO DELETE FROM journal WHERE id < 0;
ALTER EVENT ev_purge ON SCHEDULE EVERY 2 HOUR RENAME TO ev_nettoyage ENABLE;
SELECT EVENT_NAME, EVENT_TYPE, INTERVAL_VALUE, INTERVAL_FIELD, STATUS, ON_COMPLETION
FROM information_schema.EVENTS;

+--------------+------------+----------------+----------------+---------+---------------+
| EVENT_NAME   | EVENT_TYPE | INTERVAL_VALUE | INTERVAL_FIELD | STATUS  | ON_COMPLETION |
+--------------+------------+----------------+----------------+---------+---------------+
| ev_nettoyage | RECURRING  | 2              | HOUR           | ENABLED | PRESERVE      |
+--------------+------------+----------------+----------------+---------+---------------+

An ON SCHEDULE given by ALTER EVENT without STARTS starts from the instant of the statement. Errors: event already present 1537 (note with IF NOT EXISTS), unknown 1539 (note with IF EXISTS), OR REPLACE with IF NOT EXISTS 1221.

Routines: parameters with default values#

An IN or INOUT parameter of a procedure or a function can carry a default value; a call that omits the last arguments takes their values (a parameter without a default value cannot follow a parameter that has one):

CREATE FUNCTION remise(montant DECIMAL(10,2), taux DECIMAL(4,2) DEFAULT 0.1) RETURNS DECIMAL(10,2)
  RETURN ROUND(montant * (1 - taux), 2);
SELECT remise(100), remise(100, 0.5);      -- 90.00, 50.00
CALL p(1);                                  -- following parameters: their default values

Routines: FOR loops#

The counter of a FOR i IN a .. b loop is an integer specific to the loop: it starts at a and advances by 1 as long as it does not exceed b, evaluated once. With REVERSE, the bounds stay written in ascending order: the counter starts at b and goes back by 1 as long as it is not less than a, evaluated once. A NULL bound, or a greater than b: no iteration. LEAVE and ITERATE are used with the label of the loop.

CREATE PROCEDURE remplir(n INT)
BEGIN
  FOR i IN 1 .. n DO
    INSERT INTO cases (numero) VALUES (i);
  END FOR;
  FOR i IN REVERSE 1 .. n DO          -- n, n-1, … 1
    INSERT INTO rebours (numero) VALUES (i);
  END FOR;
END

A FOR row IN … loop walks through the rows of a declared cursor or of a query in parentheses; it opens the cursor and closes it on exit. The columns of the current row are read with row.column:

CREATE PROCEDURE relancer()
BEGIN
  DECLARE impayees CURSOR FOR SELECT id, client FROM factures WHERE payee = 0;
  FOR f IN impayees DO
    INSERT INTO relances (facture, client) VALUES (f.id, f.client);
  END FOR;
  lignes: FOR c IN (SELECT client, COUNT(*) AS n FROM relances GROUP BY client) DO
    IF c.n < 3 THEN ITERATE lignes; END IF;
    UPDATE clients SET bloque = 1 WHERE id = c.client;
  END FOR;
END

A field that the row does not have returns error 4082; two columns of the same name in the query, error 1060 (give an alias). A field is assigned with SET row.column = …, and the whole row can receive a row through SELECT … INTO row or FETCH cursor INTO row (values converted to the type of the fields); cited alone in an expression (row + 1), it returns error 1241.

A cursor can take parameters: DECLARE name CURSOR (parameter type, …) FOR query. The parameters are variables visible only to the cursor's query (in that query, a parameter masks a variable of the same name). Their values are given at open time, by OPEN name (arguments) or by FOR row IN name (arguments) DO; the arguments are evaluated at that moment and converted to the type of the parameters:

CREATE PROCEDURE relancer_periode(debut DATE, fin DATE)
BEGIN
  DECLARE n INT;
  DECLARE impayees CURSOR (du DATE, au DATE) FOR
    SELECT id, client FROM factures WHERE payee = 0 AND date_facture BETWEEN du AND au;
  DECLARE compte CURSOR (c INT) FOR SELECT COUNT(*) FROM relances WHERE client = c;
  FOR f IN impayees (debut, fin) DO
    INSERT INTO relances (facture, client) VALUES (f.id, f.client);
  END FOR;
  OPEN compte (42);
  FETCH compte INTO n;
  CLOSE compte;
END

A number of arguments different from the number of parameters (arguments given to a cursor without parameters, or OPEN name without the expected arguments) is refused when the routine is created with error 4080; two parameters of the same name, with error 1330. A cursor without parameters is opened with OPEN name or OPEN name ().

Routines: GET DIAGNOSTICS#

In a handler, GET DIAGNOSTICS reads the error being handled. Outside a routine, it reads into user variables the conditions left by the previous statement (those of SHOW WARNINGS, error included), without clearing them:

CREATE TABLE gd (s VARCHAR(3));
SET sql_mode = '';
INSERT INTO gd VALUES ('trop long'), ('ok');
GET DIAGNOSTICS @n = NUMBER, @lignes = ROW_COUNT;      -- conditions, rows modified
GET DIAGNOSTICS CONDITION 1 @etat = RETURNED_SQLSTATE, @message = MESSAGE_TEXT, @code = MIRAJ_ERRNO;
SELECT @n, @lignes, @etat, @message, @code;

+----+---------+-------+----------------------------------------+-------+
| @n | @lignes | @etat | @message                               | @code |
+----+---------+-------+----------------------------------------+-------+
|  1 |       2 | 01000 | Data truncated for column 's' at row 1 |  1265 |
+----+---------+-------+----------------------------------------+-------+

A condition number out of bounds does not make the statement fail: condition 1758 is added to the list and the variables keep their value. The same goes inside a routine: in a handler, the only condition that can be read is the error being handled (NUMBER is 1, and 0 outside a handler); CONDITION 2 adds 1758, and GET STACKED DIAGNOSTICS outside an active handler adds 1887 ("GET STACKED DIAGNOSTICS when handler not active"), without making the routine fail.

MIRAJ_ERRNO designates the error number item of SIGNAL … SET and of GET DIAGNOSTICS CONDITION (the historical name of this item is also accepted). On an instance in PostgreSQL language, routines and triggers are written in PL/pgSQL: see 30.13.

Routines: functions in a WHERE (performance)#

A stored function called with a column as an argument runs in principle row by row: the condition WHERE f(col) = … then can neither use an index nor be pushed down to the table read. For the simplest functions, MIRAJ replaces the call with the expression of its RETURN before choosing the plan, without changing the result:

CREATE FUNCTION est_actif(s INT) RETURNS BOOLEAN DETERMINISTIC RETURN s = 1;
CREATE INDEX t_statut ON t (statut);
SELECT id FROM t WHERE est_actif(statut);   -- read as WHERE statut = 1: index t_statut

The call is inlined only if all these conditions are met (otherwise it runs row by row, as before):

• it is written in the WHERE of a SELECT (subqueries and unions included), of an UPDATE or of a DELETE on a single table; a call in the SELECT list, in an ON condition, in GROUP BY, HAVING, or ORDER BY is not inlined;
• the body is a single RETURN expression (or a LANGUAGE sql function reduced to a SELECT of an expression, on an instance in PostgreSQL language), without variable, subquery, table read, or call to another stored function;
• the expression uses only comparisons, AND / OR / NOT, IS NULL, IN, BETWEEN, LIKE, CASE, IF, IFNULL, COALESCE, +, -, * between integers, ABS, LOWER, UPPER, CONCAT, LTRIM, RTRIM, LEFT, RIGHT, CHAR_LENGTH, LENGTH, YEAR, MONTH, DAY, on values of the same family (no division, no type conversion);
• each argument is a column of a table of the block, of the same type as the parameter (a VARCHAR may be shorter), or an integer literal; a call whose arguments are all constant is still computed once, before the query;
• the result fits without conversion in the RETURNS type (a RETURNS TINYINT of a sum of INT integers is not inlined); a text result is inlined only when compared to a literal or under IS NULL, a BOOLEAN only as a condition (WHERE f(x), AND f(x), NOT f(x));
• the function was created with the same sql_mode as the session, under the general collation, and it is SQL SECURITY INVOKER or belongs to the session's account; the EXECUTE privilege is checked as for an ordinary call.

The function is reread at every statement: a replaced or dropped function is taken into account immediately. EXPLAIN shows the inlined condition. Inlining is turned off with SET optimizer_switch = 'function_inlining=off' (same result, row-by-row call).

See also#

• Data Types: details of the types, generated columns, AUTO_INCREMENT, CHECK constraints.
• Vector search: vector indexes.
• Temporal tables: WITH SYSTEM VERSIONING, periods, PARTITION BY SYSTEM_TIME.
• Collations: COLLATE of databases, tables, and columns.