7. Langage de définition des données (DDL)

Ce chapitre décrit les instructions qui créent, modifient et suppriment les bases, tables, index, vues, séquences, routines stockées, déclencheurs et événements de MIRAJ. Pour la liste et les plages des types de données, voir Types de données.

Les exemples sont exécutés dans une base d avec miraj-cli ; les messages d'erreur sont ceux d'un serveur en anglais (--lang en).

CREATE DATABASE#

CREATE [OR REPLACE] DATABASE [IF NOT EXISTS] nom_base [[DEFAULT] CHARACTER SET [=] jeu] [[DEFAULT] COLLATE [=] interclassement];
CREATE [OR REPLACE] SCHEMA [IF NOT EXISTS] nom_base [options];

SCHEMA est un synonyme de DATABASE. COLLATE fixe l'interclassement par défaut de la base : les tables créées ensuite sans COLLATE= le prennent (voir Interclassements, § 13.2) ; sans option, ou avec CHARACTER SET utf8mb4 seul, c'est l'interclassement général utf8mb4_general_ci. COMMENT est accepté et ignoré.

[DEFAULT] ENCRYPTION [=] 'Y' | 'N' choisit, sur un dossier de données chiffré, le chiffrement par défaut des tables de la base et de ses définitions ; ALTER DATABASE nom [DEFAULT] ENCRYPTION = 'Y' | 'N' le change. Voir Chiffrement au repos.

CREATE DATABASE IF NOT EXISTS gestium;
-- OK, 1 ligne(s) affectée(s)
CREATE DATABASE IF NOT EXISTS gestium;
-- OK, 0 ligne(s) affectée(s) ; Avertissement : 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 ligne(s) affectée(s)   (la base existait et a été remplacée)

SHOW CREATE DATABASE ne rend la clause DEFAULT CHARACTER SET … COLLATE … que pour une base dont l'interclassement n'est pas le défaut ; un autre jeu de caractères (CHARACTER SET latin1) est accepté et la base reste en utf8mb4.

CREATE OR REPLACE DATABASE base équivaut à DROP DATABASE IF EXISTS base suivi de CREATE DATABASE base : tables, vues, routines et séquences de l'ancienne base sont perdues, et la base prend les options écrites (pas celles de la base remplacée). Les privilèges DROP et CREATE sur la base sont exigés et vérifiés avant de rien supprimer. Les refus de DROP DATABASE restent (base système, table tenue par une autre session) ; si la base remplacée est la base courante, la session n'a plus de base courante ensuite, comme après un DROP DATABASE. L'instruction rend 2 lignes quand une base a été remplacée, 1 sinon, sans note pour une base absente. Les deux instructions sont menées et journalisées l'une après l'autre (journal binaire : DROP DATABASE IF EXISTS puis CREATE DATABASE) : si la création échoue après la suppression, la base reste supprimée.

ALTER DATABASE#

ALTER {DATABASE | SCHEMA} [nom_base] [[DEFAULT] CHARACTER SET [=] jeu] [[DEFAULT] COLLATE [=] interclassement];

Change l'interclassement par défaut de la base (la base courante sans nom) pour les tables créées ensuite ; les tables existantes gardent le leur. Privilège ALTER sur la base.

DROP DATABASE#

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

DROP DATABASE supprime aussi les vues, routines, déclencheurs, événements et séquences de la base.

CREATE TABLE#

Définition complète#

CREATE [OR REPLACE] [TEMPORARY | GLOBAL TEMPORARY] TABLE [IF NOT EXISTS] [base.]table (
    définition_colonne [, définition_colonne ...]
    [, [CONSTRAINT [nom]] PRIMARY KEY [USING {BTREE | HASH}] (élément_clé [, ...])]
    [, [CONSTRAINT [nom]] UNIQUE [KEY | INDEX] [nom] (élément_clé [, ...]) [options_index]]
    [, {INDEX | KEY} [nom] (élément_clé [, ...]) [options_index]]
    [, FULLTEXT [KEY | INDEX] [nom] (colonne [WEIGHT n] [, ...]) [options_index]]
    [, SPATIAL [KEY | INDEX] [nom] (colonne) [options_index]]
    [, VECTOR [KEY | INDEX] [nom] (colonne) [options_index]]
    [, [CONSTRAINT [nom]] FOREIGN KEY [nom] (colonne [, ...])
         REFERENCES [base.]table_ref (colonne [, ...])
         [ON DELETE action] [ON UPDATE action]]
    [, [CONSTRAINT [nom]] CHECK (expression) [[NOT] ENFORCED]]
    [, PERIOD FOR {SYSTEM_TIME | période} (début, fin)]
) [options_table] [PARTITION BY ...] [[IGNORE | REPLACE] [AS] requête];

élément_clé   : colonne [(longueur)] [ASC | DESC]
options_index : [USING {BTREE | HASH}] [COMMENT 'texte'] [VISIBLE | INVISIBLE] [IGNORED | NOT IGNORED]
options_table : [AUTO_INCREMENT [=] n] [ENGINE [=] moteur] [[DEFAULT] CHARSET [=] jeu]
                [[DEFAULT] COLLATE [=] interclassement] [COMMENT [=] 'texte']
                [ENCRYPTION [=] {'Y' | 'N'}] [WITH SYSTEM VERSIONING] [ROW_FORMAT = ...] ...

Où définition_colonne s'écrit :

colonne type
    [UNSIGNED] [ZEROFILL] [[CHARACTER SET jeu] COLLATE interclassement] [BINARY]
    [NOT NULL | NULL]
    [DEFAULT valeur | DEFAULT (expression) | DEFAULT fonction(...)]
    [AUTO_INCREMENT]
    [UNIQUE [KEY]] [PRIMARY KEY | KEY]
    [COMMENT 'texte'] [COMPRESSED] [INVISIBLE | VISIBLE]
    [ON UPDATE CURRENT_TIMESTAMP[(n)]]
    [[CONSTRAINT [nom]] CHECK (expression)]
    [REFERENCES table_ref (colonne) [ON DELETE action] [ON UPDATE action]]
    [[GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED | PERSISTENT]]
    [FIRST | AFTER colonne]          -- dans ALTER TABLE seulement

Les attributs s'écrivent dans un ordre quelconque après le type.

Exemple réaliste :

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
);

Clauses de table#

Index secondaires non uniques#

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 dans CREATE TABLE, CREATE INDEX et ALTER TABLE ... ADD INDEX créent un index secondaire, en nombre illimité par table. Ces index sont tenus en mémoire et reconstruits à l'ouverture de la base. Ils servent une égalité portant sur la totalité de leurs colonnes (entiers, décimaux, chaînes, dates ; pas les flottants) : KEY (n, s) sert n = 5 AND s = 'v5' mais pas n = 5 seul ; une autre condition de la même requête est filtrée sur les lignes ainsi trouvées. Pour une colonne VECTOR, l'index sert aussi IN, IS NULL, <, <=, >, >= et BETWEEN. Une colonne générée VIRTUAL peut y figurer (voir Types de données).

USING BTREE / USING HASH est conservé et rendu par SHOW CREATE TABLE et SHOW INDEX (Index_type, HASH ou BTREE), sans changer l'index.

Ordre et noms des clés. SHOW CREATE TABLE, SHOW INDEX et information_schema.STATISTICS présentent les clés dans l'ordre de la référence, quel que soit l'ordre d'écriture : clé primaire, UNIQUE sur des colonnes NOT NULL (sans préfixe, puis à préfixe), autres UNIQUE (sans préfixe, puis à préfixe), UNIQUE par hachage (USING HASH, ou sur un TEXT / BLOB entier), index ordinaires et spatiaux, FULLTEXT, VECTOR ; à rang égal, dans l'ordre de déclaration (clés écrites en ligne sur une colonne comprises). Une clé ajoutée par ALTER TABLE ou CREATE INDEX prend son rang parmi les clés existantes, qui ne bougent pas (une colonne devenue NOT NULL, une clé renommée ou retirée puis rajoutée sous le même nom gardent leur place). Une clé sans nom prend celui de sa première colonne, puis _2, _3… dans l'ordre de déclaration :

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), déclaré en troisième)
-- a_2     | a      (KEY (a))
``` `COMMENT` (hors `COMMENT 'miraj:trigram'`, voir plus bas) et
`VISIBLE` / `INVISIBLE` sont acceptés sans effet. Un index identique à un index existant est créé
quand même, avec la note **1831** (« Duplicate index `x`. This is deprecated and will be
disallowed in a future release »).

### Clés étrangères

```sql
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
);

Actions référentielles reconnues à l'analyse : RESTRICT, CASCADE, SET NULL, NO ACTION, SET DEFAULT. Seules les quatre premières sont réellement appliquées : SET DEFAULT est analysée mais refusée à la création de la clé avec l'erreur 1215 (ER_CANNOT_ADD_FOREIGN).

Une clé étrangère sans nom reçoit <table>_ibfk_<n>. L'attribut de colonne REFERENCES table (col) crée lui aussi une clé étrangère.

La table référencée doit se trouver dans la même base (erreur 1235 sinon).

CREATE TABLE ... LIKE#

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

Recopie de la table source : colonnes (types, valeurs par défaut, expressions générées, attribut AUTO_INCREMENT), clé primaire, contraintes UNIQUE, index secondaires, plein texte et IGNORED, contraintes CHECK, moteur et partitionnement éventuel. Ne sont pas recopiés : la valeur du compteur AUTO_INCREMENT (la nouvelle table commence à 1, même si la source a été créée avec AUTO_INCREMENT = 50) et les clés étrangères. La nouvelle table est vide. LIKE suivi d'une requête est une erreur de syntaxe (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 [(définitions)] [options_table] [IGNORE | REPLACE] [AS] requête;

Le mot AS est facultatif ; la requête peut être écrite entre parenthèses ((SELECT ...)) ou non, commencer par WITH ou être un constructeur VALUES (…), (…), et porter sur n'importe quelle source (table, vue, jointure, UNION, sous-requête, agrégat, LIMIT). Les options de table (ENGINE = Aria, COMMENT…) et PARTITION BY s'écrivent devant la requête.

Ce qui est repris de la source :

• le nom (ou l'alias) de chaque colonne du résultat ;
• le type de l'expression : le type déclaré si la colonne du résultat vient telle quelle d'une table, sinon un type dimensionné sur l'expression (CONCAT(nom, '!') sur un VARCHAR(100) donne varchar(101), plafond * 2 sur un DECIMAL(12,2) donne decimal(13,2), le littéral 1 donne int(1), NULL seul donne bigint(20)) ; FLOOR, CEILING, ROUND et TRUNCATE d'une colonne entière gardent son type (FLOOR(ti) sur un TINYINT : tinyint(4)), d'un décimal de 10 à 18 chiffres donnent un bigint(chiffres + 1) (FLOOR(d) sur un DECIMAL(10,0) : bigint(11)), d'un décimal UNSIGNED un entier sans signe, d'une date un int(8) unsigned ; ces types valent aussi pour les colonnes d'une vue ;
• l'attribut ZEROFILL d'une colonne, y compris au travers d'une table dérivée ;
• la nullabilité du résultat.

Une colonne JSON de la source devient un longtext en utf8mb4_bin, sans la contrainte json_valid d'une colonne JSON (voir Types de données).

Ce qui n'est jamais repris : clé primaire, index, contrainte UNIQUE, AUTO_INCREMENT, DEFAULT, colonne générée (qui devient une colonne ordinaire portant la valeur déjà calculée).

Colonnes et clés déclarées avec la requête. Une liste de définitions entre parenthèses peut précéder la requête : une colonne déclarée qui porte le nom d'une colonne du résultat prend sa définition (type, DEFAULT…) et reçoit ses valeurs ; une colonne déclarée absente du résultat reçoit sa valeur par défaut. Ordre des colonnes de la table créée : d'abord les colonnes déclarées qu'aucune colonne du résultat ne nomme, puis les colonnes du résultat dans leur ordre (une colonne déclarée qui porte le nom d'une colonne du résultat prend la place de celle-ci). La liste peut aussi ne contenir que des clés ((PRIMARY KEY (id))). IGNORE écarte les lignes qui heurtent une clé d'unicité (avertissement 1062), REPLACE les remplace ; sans l'un ni l'autre, un doublon fait échouer l'instruction.

-- table client de l'exemple « Définition complète »
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`)
)

L'instruction rend le nombre de lignes insérées (comme un INSERT), pas un jeu de résultat. La requête source est lue en entier (tables sources verrouillées en lecture) avant que la table ne soit créée : elle n'existe pas encore pendant la lecture, et un échec du remplissage laisse la table inexistante.

CREATE OR REPLACE [TEMPORARY] TABLE#

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

Équivaut à DROP TABLE IF EXISTS table suivi du CREATE TABLE correspondant : lignes, index, clés, contraintes, compteur AUTO_INCREMENT et déclencheurs de l'ancienne table sont perdus.

• IF NOT EXISTS est interdit avec OR REPLACE (erreur 1221, ER_WRONG_USAGE), comme pour une vue.
• La nouvelle définition est entièrement vérifiée avant la suppression de l'ancienne table (colonnes, clés, index, contraintes, valeurs par défaut — la définition est montée en mémoire puis jetée pour ce contrôle) : une définition invalide laisse l'ancienne table intacte.
• Ce qui ne peut être vérifié qu'à l'écriture effective (fichier non écrit, lignes du ... [AS] SELECT refusées) survient après la suppression : l'ancienne table est alors perdue.
• Une table référencée par la clé étrangère d'une autre table reste refusée (erreur 3730), de même que le nom d'une base système ou un nom de table invalide (erreur 1103).
• Un nom déjà porté par une vue n'est jamais remplacé par une table (erreur 1050, « Table 'v' already exists »).
• CREATE OR REPLACE TEMPORARY TABLE ne remplace que la table temporaire de la session, jamais une table de la base de même nom qu'elle masque.
• Droits requis : CREATE et DROP sur la table.

DROP TABLE#

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

Une table référencée par une clé étrangère d'une autre table n'est jamais supprimée (erreur 3730) : retirer d'abord la clé étrangère. DROP TEMPORARY TABLE ne supprime que les tables temporaires de la session (erreur 1051 si elle n'existe pas) ; un DROP TABLE ordinaire supprime d'abord une table temporaire de la session de même nom, puis à défaut une table de la base. Avec IF EXISTS, chaque table absente donne une note 1051 et les autres sont supprimées. Une vue se supprime par DROP VIEW (DROP TABLE d'une vue rend 1051).

TRUNCATE TABLE#

TRUNCATE [TABLE] table;

Vide la table (lignes, magasin de LOB associé) sans passer par le journal ligne à ligne comme un DELETE ; le compteur AUTO_INCREMENT repart de 1 (contrairement à DELETE FROM table, qui le conserve). Une table tenue en lecture par une transaction ouverte d'une autre session, ou dont des écritures ne sont pas validées, entre en conflit avec TRUNCATE (erreur 1213, sans attente). Voir aussi la section « TRUNCATE : précisions » en fin de chapitre.

ALTER TABLE#

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

Plusieurs clauses séparées par des virgules s'appliquent tout ou rien dans la même instruction, chacune sur l'état laissé par les clauses précédentes (supprimer puis recréer une colonne ou un index dans une seule instruction fait bien les deux). Une colonne supprimée puis rajoutée sous le même nom (DROP COLUMN b, ADD COLUMN b BIGINT) garde sa clé primaire, ses clés UNIQUE et ses index : ils portent sur la nouvelle colonne et sont reconstruits sur ses valeurs (sa valeur par défaut pour chaque ligne, d'où un doublon 1062 sous une clé d'unicité si elle n'est pas NULL et que la table a plusieurs lignes). Une contrainte CHECK ou une clé étrangère qui cite la colonne font toujours refuser sa suppression (3959, 1828, 1829).

Colonnes#

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 et CHANGE COLUMN redéfinissent la colonne entière : une clause ON UPDATE CURRENT_TIMESTAMP existante est donc perdue si elle n'est pas réécrite dans la nouvelle définition.

Interclassement (voir Interclassements) : ADD, MODIFY et CHANGE sans COLLATE prennent le défaut de la table, RENAME COLUMN garde celui de la colonne ; [DEFAULT] COLLATE = nom change le défaut de la table sans toucher aux colonnes existantes, et [DEFAULT] CHARSET = jeu seul le remet au défaut du jeu (sans recopie des lignes dans les deux cas) ; CONVERT TO CHARACTER SET utf8mb4 [COLLATE nom] convertit toutes les colonnes texte, JSON compris. Une colonne de clé dont les valeurs deviennent égales sous le nouvel interclassement ('A1' et 'a1' passant de utf8mb4_bin à utf8mb4_general_ci) rend 1062 et la table reste inchangée ; changer l'interclassement d'une colonne de partitionnement rend 1235.

Conversions de type sous MODIFY / CHANGE : une valeur existante qui ne tient plus dans le nouveau type rend, en mode strict, l'erreur générique de troncature 1265 (« Data truncated for column... at row... »), et non le code propre à un INSERT / UPDATE (ni 1406, ni 1138). Une colonne NOT NULL sans DEFAULT ajoutée à une table non vide reçoit dans les lignes existantes la valeur implicite de son type : 0, chaîne vide, ou date zéro (0000-00-00, 0000-00-00 00:00:00) pour une colonne date/heure.

Index et clés#

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;

L'ordre des colonnes d'une clé primaire ajoutée suit toujours l'ordre des colonnes de la table (ADD PRIMARY KEY (b, a) produit PRIMARY KEY (a, b), pas l'ordre écrit dans la clause) ; un UNIQUE sur une seule colonne d'une clé primaire composée est ignoré, comme dans CREATE TABLE.

Une clé étrangère sans nom explicite reçoit le nom généré <table>_ibfk_<n> (visible par SHOW CREATE TABLE et dans les messages d'erreur qui la citent, comme DROP FOREIGN KEY).

RENAME {INDEX | KEY} [IF EXISTS] ancien TO nouveau renomme un index sans le reconstruire, quelle que soit sa nature (UNIQUE, secondaire, FULLTEXT, trigramme, SPATIAL, VECTOR) : le nouveau nom paraît dans SHOW CREATE TABLE, SHOW INDEX, information_schema.STATISTICS, EXPLAIN et le message 1062 d'une clé d'unicité ; une clé étrangère qui s'appuie sur l'index continue de le faire. Erreurs : index absent 1176 (« Key 'x' doesn't exist in table 't' » ; note avec IF EXISTS), nom déjà porté par un autre index 1061, PRIMARY comme ancien ou nouveau nom 1280. Dans une instruction à plusieurs clauses, les index retirés le sont d'abord, puis les renommages s'appliquent, puis les ajouts : RENAME INDEX a TO b, ADD INDEX a (c) réussit. Chaque clause désigne l'index par son nom d'avant l'instruction et les renommages valent ensemble : RENAME INDEX a TO b, RENAME INDEX b TO a échange deux noms ; renommer deux fois le même index dans une instruction rend 1176.

ALTER {INDEX | KEY} [IF EXISTS] nom [NOT] IGNORED marque un index ignoré : il reste tenu à jour par les écritures (une clé d'unicité IGNORED refuse toujours les doublons, 1062) mais n'est plus jamais retenu pour une lecture — utile pour mesurer l'effet d'un retrait avant de retirer l'index. L'attribut s'écrit aussi dans la définition d'un index (KEY i (a) IGNORED dans CREATE TABLE, ALTER TABLE … ADD INDEX i (a) IGNORED, CREATE INDEX i ON t (a) IGNORED, de même pour UNIQUE, FULLTEXT, SPATIAL et VECTOR) ; NOT IGNORED est le défaut. SHOW CREATE TABLE affiche IGNORED après l'index, SHOW INDEX le rend dans sa colonne Ignored (YES / NO, et Visible à NO), information_schema.STATISTICS dans IGNORED (et IS_VISIBLE à NO) ; EXPLAIN ne cite plus l'index dans possible_keys. L'attribut suit l'index dans un renommage, une reconstruction de la table, CREATE TABLE … LIKE et une réouverture. Un MATCH … AGAINST dont le seul index FULLTEXT est ignoré rend 1191, comme s'il n'existait pas ; une indication d'index (FORCE INDEX (i)…) qui le cite rend 1176. Erreurs : clé primaire 4174 (« A primary key cannot be marked as IGNORE », dans CREATE TABLE aussi), index absent 1176 (note avec IF EXISTS).

AUTO_INCREMENT#

ALTER TABLE commande AUTO_INCREMENT = 10000;

Fixe ou relève le prochain compteur ; sans effet pour le baisser en dessous du maximum déjà utilisé.

RENAME#

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

RENAME TABLE (plusieurs paires en une instruction) vérifie toutes les paires avant d'effectuer le moindre renommage (erreurs 1146, 1050, 1103 selon le cas), puis les applique l'une après l'autre. Renommer vers une autre base est refusé (erreur 1235) ; renommer une table vers son propre nom rend l'erreur 1050.

FORCE, ORDER BY et 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 reconstruit la table sans changer sa définition, par le même chemin qu'un ALTER TABLE qui modifie une colonne (fichier réécrit en entier, index refaits) ; il se combine aux autres clauses.

ORDER BY colonne [ASC | DESC] [, ...], dernière clause de l'instruction, vérifie que les colonnes existent dans la table d'après l'instruction (1054, « Unknown column 'x' in 'order clause' ») et reconstruit la table en recopiant ses lignes dans cet ordre (interclassement de chaque colonne, NULL en tête) ; l'ordre ne se maintient pas au-delà (les écritures suivantes le défont) et seul un ORDER BY dans la requête fixe l'ordre d'un résultat. Sur une table en mémoire à clé primaire (ou, à défaut, à clé d'unicité sur des colonnes NOT NULL, tenue alors pour clé primaire), les lignes restent en place et l'instruction rend l'avertissement 1105 (« ORDER BY ignored as there is a user-defined clustered index in the table 't' ») ; une table disque (ENGINE = Aria…) est réordonnée, sans avertissement. Une table partitionnée n'est pas réordonnée.

ALTER ONLINE TABLE est accepté : ONLINE est sans effet, comme LOCK = NONE. ALTER IGNORE TABLE (doublons supprimés à la création d'une clé d'unicité) est refusé (1235).

Clauses IF [NOT] EXISTS : comportement en cas de non-application#

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

Chaque clause conditionnelle (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 nom, ADD CONSTRAINT IF NOT EXISTS nom CHECK (...)) est jugée sur l'état laissé par les clauses précédentes de la même instruction. Une clause dont la condition n'est pas remplie (colonne déjà présente pour un ADD ... IF NOT EXISTS, colonne absente pour un DROP ... IF EXISTS, etc.) est ignorée et donne une note portant le code du conflit sous-jacent (1060 doublon de colonne, 1061 doublon de nom d'index, 1091 objet absent, 1054 colonne inconnue, 1176 index à renommer ou à marquer absent, 1826 contrainte CHECK de ce nom déjà sur la table) ; les autres clauses de l'instruction s'appliquent normalement. Si toutes les clauses sont ainsi ignorées, l'instruction entière rend OK sans erreur.

DROP PARTITION IF EXISTS p1, p2 écarte les partitions absentes (une note 1507 pour chacune) et supprime les autres ; ADD PARTITION IF NOT EXISTS (…) est ignorée en entier, avec la note 1517, si l'un des noms est déjà pris. Une table non partitionnée reste une erreur (1505). DROP PRIMARY KEY IF EXISTS et DROP CHECK IF EXISTS n'existent pas (erreur de syntaxe 1064) : écrire DROP CONSTRAINT IF EXISTS nom ou DROP INDEX IF EXISTS.

Options sans effet#

[DEFAULT] CHARSET / CHARACTER SET seul, ALGORITHM = ..., LOCK = ... sont acceptées mais n'ont aucun effet sur le stockage ; COMMENT = 'texte' change le commentaire de la table, rendu par SHOW CREATE TABLE (COMMENT='texte'). ENGINE n'est sans effet que pour un moteur qui garde la table en mémoire : ENGINE = Aria, MyISAM ou DISK convertit la table en table disque, et ENGINE = InnoDB (ou MIRAJ, MEMORY) ramène une table disque en mémoire (voir Moteurs de stockage). Sont en revanche des erreurs (1235, non pris en charge) : DISCARD TABLESPACE, IMPORT TABLESPACE (sans objet pour le stockage de MIRAJ) et ALTER IGNORE TABLE. Une colonne peut être déclarée INVISIBLE par ADD, MODIFY ou CHANGE (voir « Attributs de colonne » plus bas).

CREATE [UNIQUE] INDEX et DROP INDEX#

CREATE [OR REPLACE] [UNIQUE | FULLTEXT | SPATIAL | VECTOR] INDEX [IF NOT EXISTS] nom
    [USING {BTREE | HASH}] ON [base.]table (élément_clé [, ...])
    [options_index] [ALGORITHM [=] ...] [LOCK [=] ...];
DROP INDEX [IF EXISTS] nom ON [base.]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 sans UNIQUE crée un index secondaire non unique (voir la section dédiée plus haut) ; CREATE UNIQUE INDEX équivaut à ALTER TABLE ... ADD UNIQUE. Les options ALGORITHM = ... et LOCK = ... sont acceptées sans effet. DROP INDEX `PRIMARY` ON table retire la clé primaire. DROP INDEX nom ON table équivaut à ALTER TABLE table DROP INDEX nom ; DROP INDEX IF EXISTS nom ON table ne fait rien (note 1091, Can't DROP INDEX `nom`; check that it exists) si la table n'a pas d'index de ce nom — une table absente reste une erreur (1146). CREATE INDEX IF NOT EXISTS ne fait rien (note 1061) si un index de ce nom existe déjà. CREATE OR REPLACE [UNIQUE | FULLTEXT | SPATIAL | VECTOR] INDEX nom ON table (...) remplace l'index du même nom, quelle que soit sa nature, par la même instruction : si la création échoue (doublon pour un UNIQUE, colonne inconnue, index encore nécessaire à une clé étrangère : 1553), l'index d'avant reste en place ; sans index de ce nom, c'est un simple CREATE INDEX, sans note. OR REPLACE et IF NOT EXISTS s'excluent (1221) ; USING BTREE / USING HASH s'écrit avant ou après le nom de la table et reste affiché avec l'index.

L'index qui porte la colonne AUTO_INCREMENT ne peut être remplacé que par une clé d'unicité sur cette seule colonne : CREATE OR REPLACE UNIQUE INDEX k ON t (id) ou ALTER TABLE t DROP INDEX k, ADD UNIQUE k2 (id) sont acceptés, alors que CREATE OR REPLACE INDEX k ON t (id), une clé sur plusieurs colonnes ou un DROP INDEX k seul rendent 1075 et laissent la table inchangée.

CREATE INDEX et DROP INDEX demandent le privilège INDEX sur la table (ALTER n'y suffit pas et n'est pas requis) ; ALTER TABLE … ADD INDEX demande ALTER.

Les index vectoriels (CREATE VECTOR INDEX, VECTOR INDEX dans CREATE TABLE, et la forme CREATE INDEX … USING hnsw (colonne classe)) sont décrits au chapitre 11. Recherche vectorielle.

Vues#

CREATE [OR REPLACE] [CACHED] VIEW#

CREATE [OR REPLACE]
    [ALGORITHM = ...]
    [DEFINER = compte]
    [SQL SECURITY {DEFINER | INVOKER}]
    [CACHED] VIEW [IF NOT EXISTS] [base.]nom [(colonne [, ...])]
    AS requête
    [WITH [CASCADED | LOCAL] CHECK OPTION];

-- Vue ordinaire : reflète toujours l'état courant des tables
CREATE VIEW v_client_actif AS
SELECT id, nom, email FROM client WHERE actif = 1;

-- Vue en cache : figée jusqu'au prochain 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 et IF NOT EXISTS sont mutuellement exclusifs (erreur 1221). ALGORITHM = TEMPTABLE rend la vue non modifiable ; MERGE et UNDEFINED sont sans autre effet. WITH CHECK OPTION (voir « Vues modifiables » ci-dessous) demande une vue modifiable (erreur 1368 sinon). Les définitions sont conservées dans <base>/views.mrv, écrit de façon durable avant que l'instruction ne rende la main.

Vue ordinaire ou vue CACHED#

Vues modifiables#

INSERT, UPDATE, DELETE, REPLACE et LOAD DATA peuvent viser une vue : l'instruction est réécrite sur la table que la vue lit (chaque colonne de la vue remplacée par son expression, la condition WHERE de la vue ajoutée à celle d'un UPDATE ou d'un DELETE), puis exécutée comme une écriture directe de la table : déclencheurs, clés étrangères, contraintes CHECK, IGNORE, ON DUPLICATE KEY UPDATE, transactions, instructions multi-tables. Une vue construite sur une vue modifiable l'est aussi, de proche en proche.

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;   -- seulement si le client est actif
DELETE FROM v_client_actif WHERE nom LIKE 'Test%';                -- clients actifs seulement
INSERT INTO v_client_actif VALUES (99, 'Nouveau', '[email protected]'); -- actif : valeur par défaut

Une vue est modifiable quand sa requête lit une seule table (ou vue modifiable) sans DISTINCT, GROUP BY, HAVING, agrégat, fonction de fenêtrage, UNION, LIMIT, sous-requête dans la liste de sélection, table dérivée ni table d'information_schema, avec au moins une colonne reprise telle quelle, et qu'elle n'est ni ALGORITHM = TEMPTABLE ni CACHED (le résultat figé d'une vue en cache ne suivrait pas les écritures : elle reste en lecture seule). information_schema.VIEWS.IS_UPDATABLE vaut YES pour une telle vue.

Vue de jointure : un UPDATE peut modifier les colonnes d'une seule de ses tables (jointures internes seulement si ce n'est pas la première table de la vue) ; un INSERT, avec une liste de colonnes, remplit une seule de ses tables.

WITH CHECK OPTION : une ligne écrite par INSERT ou UPDATE doit rester visible dans la vue ; sinon l'erreur 1369 (CHECK OPTION failed) annule l'instruction (avec IGNORE : ligne écartée et avertissement 1369). Une condition qui vaut NULL est un échec. LOCAL vérifie la condition de la vue et celles des vues lues qui ont leur propre option ; CASCADED (le défaut) celles de toutes les vues lues. Une option portée par une vue de jointure, ou une condition qui contient une sous-requête, est refusée à l'écriture (1235). DELETE n'est pas concerné.

Droits : l'appelant doit avoir le privilège sur la vue (INSERT, UPDATE, DELETE) ; la table est écrite avec les droits du définisseur de la vue (1356 s'il ne les a pas), ou de l'appelant avec SQL SECURITY INVOKER (1142). Aucun droit de l'appelant sur la table n'est demandé.

Limites : une instruction du corps d'un déclencheur, ou d'une fonction stockée appelée ligne à ligne, n'écrit pas au travers d'une vue (1288 / 1471) ; une référence non qualifiée à une colonne de la vue dans une sous-requête de l'instruction n'est pas traduite (la qualifier par le nom de la vue). Une table temporaire de la session masque la vue de même nom.

Une vue peut être construite sur une autre vue (jusqu'à 32 niveaux d'imbrication ; un cycle rend l'erreur 1462).

REFRESH VIEW#

REFRESH VIEW v_bilan_mensuel;
REFRESH VIEW v_bilan_mensuel, v_autre_vue;

Ne s'applique qu'à une vue CACHED : recalcule son résultat immédiatement et remplace la version figée. Sans effet particulier sur une vue ordinaire (dont le résultat, s'il est gardé, se met déjà à jour de lui-même).

ALTER VIEW#

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

Même syntaxe que CREATE VIEW (hors IF NOT EXISTS) : redéfinit entièrement la vue.

DROP VIEW#

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

RESTRICT et CASCADE sont acceptés sans effet. Les vues inconnues sont citées ensemble par l'erreur 4092 (« Unknown VIEW: 'd.absente1,d.absente2' ») ; avec IF EXISTS, une note 4092 par vue absente, les autres étant supprimées.

SHOW CREATE VIEW#

SHOW CREATE VIEW v_client_actif;

Rend la définition canonique de la vue, telle qu'enregistrée (y compris CACHED s'il y a lieu), avec les colonnes View, Create View, character_set_client et collation_connection :

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

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 liste les vues de la base courante ; information_schema.COLUMNS ne décrit que les vues dont le résultat est actuellement en mémoire.

Compléments de syntaxe : CREATE TABLE#

Cette section complète les sections précédentes : attributs de colonne, options de table, contraintes nommées, tables temporaires. Chaque affirmation a été vérifiée sur le moteur.

Attributs de colonne : ce qui compte, ce qui est ignoré#

Un mot-clé d'attribut inconnu est une erreur de syntaxe (1064). Une définition de colonne répétant GENERATED est rejetée.

Options de table#

Toutes les options placées après la parenthèse fermante sont acceptées à l'analyse (ENGINE, [DEFAULT] CHARSET, COLLATE, COMMENT, ROW_FORMAT, etc., avec ou sans =) et ignorées, à ces exceptions près :

• COMMENT [=] 'texte' est conservé et rendu par SHOW CREATE TABLE () COMMENT='texte') ;
• ENCRYPTION [=] 'Y' | 'N' choisit le chiffrement des fichiers de la table (voir Chiffrement au repos) ;

• WITH SYSTEM VERSIONING garde l'historique des lignes (voir le chapitre 12) ;
• AUTO_INCREMENT = n fixe le premier numéro attribué (0 est ignoré) ;
• ENGINE = Aria, MyISAM ou DISK crée une table disque (voir Moteurs de stockage) ;
• PARTITION BY ... définit le partitionnement (voir plus bas) ;
• [DEFAULT] COLLATE = nom (avec ou sans CHARSET) fixe l'interclassement par défaut des colonnes texte écrites sans COLLATE (voir Interclassements).

SHOW CREATE TABLE ne rend DEFAULT CHARSET=utf8mb4 COLLATE=nom que si le défaut de la table n'est pas utf8mb4_general_ci (et CHARACTER SET utf8mb4 COLLATE nom après le type d'une colonne qui s'en écarte), et ne rend ENGINE que pour une table disque ; il ne rend pas la valeur du compteur AUTO_INCREMENT.

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'

Contraintes nommées#

Devant PRIMARY KEY, UNIQUE, FOREIGN KEY et CHECK, CONSTRAINT nom donne un nom :

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

La clé primaire s'appelle toujours PRIMARY (pk_y est ignoré). Le nom de uq_b devient celui de l'index (UNIQUE KEY uq_b). Une contrainte CHECK de table sans nom reçoit CONSTRAINT_<n> (compteur propre à la table), une contrainte de colonne le nom de sa colonne ; les noms de CHECK sont uniques dans la table (erreur 1826 en cas de doublon), deux tables pouvant porter le même nom. information_schema.CHECK_CONSTRAINTS donne la table (TABLE_NAME) et le niveau (LEVEL : Column ou Table) de chaque contrainte.

Une violation de CHECK rend l'erreur 4025 :

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

Une clé primaire déclarée à la fois sur une colonne (a INT PRIMARY KEY) et par une clause PRIMARY KEY (b) de table est fusionnée en une clé composite (a, b) ; deux clauses PRIMARY KEY de table, ou deux colonnes PRIMARY KEY, rendent l'erreur 1068 (« Multiple primary key defined »).

Tables temporaires#

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

• En mémoire seulement : ni fichier de données, ni journal. Jamais listée par SHOW TABLES ni par information_schema.
• Une table temporaire de session masque une table de la base du même nom tant qu'elle existe ; DROP TABLE supprime d'abord la temporaire, puis (à défaut) la table de la base.
• CREATE TEMPORARY TABLE et DROP TEMPORARY TABLE ne valident pas la transaction en cours ; les lignes restent, elles, transactionnelles.
• Privilège : CREATE TEMPORARY TABLES sur la base (aucun contrôle ensuite pour la forme de session) ; la forme GLOBAL suit les privilèges de table.
• Les formes LIKE et [AS] SELECT sont acceptées comme pour une table ordinaire.
• Refusés : clé étrangère sur ou vers une table temporaire (erreur 1215), déclencheur (1361), partitionnement (1562), base système.

Compléments de syntaxe : ALTER TABLE#

Formes supplémentaires#

-- Plusieurs colonnes entre parenthèses
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;

-- Contraintes CHECK
ALTER TABLE p ADD CONSTRAINT ck_q CHECK (qte >= 0);
ALTER TABLE p ALTER CHECK ck_q NOT ENFORCED;          -- extension MIRAJ
ALTER TABLE p ALTER CONSTRAINT ck_q ENFORCED;         -- synonyme, extension MIRAJ
ALTER TABLE p DROP CONSTRAINT ck_q;                   -- ou DROP CHECK ck_q
ALTER TABLE p DROP CONSTRAINT IF EXISTS ck_q;         -- absente : note 1091
ALTER TABLE p ADD CONSTRAINT IF NOT EXISTS ck_q CHECK (qte >= 0);

-- Contraintes d'unicité et clés nommées
ALTER TABLE p ADD CONSTRAINT uq_nom UNIQUE (nom);
ALTER TABLE p DROP INDEX uq_nom;

-- Valeur par défaut à NULL
ALTER TABLE p ALTER COLUMN qte SET DEFAULT NULL;

Notes :

• DROP COLUMN col accepte un RESTRICT ou CASCADE final, sans effet.
• ADD CHECK et toute reconstruction de la table relisent toutes les lignes pour vérifier la contrainte. Une contrainte NOT ENFORCED est conservée mais jamais évaluée.
• ADD / DROP VECTOR INDEX : voir le chapitre 11. Recherche vectorielle.
• ADD SYSTEM VERSIONING et DROP SYSTEM VERSIONING ajoutent ou retirent l'historique des lignes ; les autres clauses d'une table versionnée qui changent ses colonnes demandent system_versioning_alter_history = KEEP (4119 sinon) et s'appliquent aussi à son historique ; ADD PERIOD [IF NOT EXISTS] FOR p (début, fin), DROP PERIOD [IF EXISTS] FOR p et les clés (…, p WITHOUT OVERLAPS) gèrent les périodes applicatives (voir le chapitre 12).
• Les options ENGINE = ..., ALGORITHM = ..., LOCK = ... peuvent être combinées à d'autres clauses : ALTER TABLE p ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE réussit sans rien changer sur une table en mémoire. Sur une table disque, ENGINE=InnoDB la ramène en mémoire.
• Une table est reconstruite en mémoire et son fichier réécrit en entier : compter deux copies en mémoire pour les colonnes modifiées. C'est aussi vrai d'une table disque : son ALTER TABLE écrit un nouveau fichier .dmrj.

Erreurs propres à ALTER TABLE#

Exemple exécutable#

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;
-- ERREUR 1060 (42S21) : Duplicate column name 'z'
ALTER TABLE p ADD COLUMN IF NOT EXISTS z INT;
-- OK, 0 ligne(s) affectée(s) ; Avertissement : Duplicate column name 'z'
ALTER TABLE p MODIFY nom VARCHAR(1) NOT NULL;
-- ERREUR 1265 (01000) : Data truncated for column 'nom' at row 1
ALTER TABLE p DROP PRIMARY KEY;
-- ERREUR 1075 (42000) : Incorrect table definition; there can be only one auto column and it must be defined as a key

Index plein texte (FULLTEXT) et trigrammes#

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);

Un index FULLTEXT sert MATCH (colonnes) AGAINST (…) (chapitre 9, §9.22) sur des colonnes CHAR, VARCHAR, TEXT ou JSON d'un même interclassement (1283 sinon, et pour une colonne INT ou BLOB) ; WITH PARSER est refusé (1128), une table temporaire aussi (1796), une table disque aussi (1214). Le nom par défaut est celui de la première colonne (_2, _3… ensuite). L'index suit une colonne renommée (CHANGE), perd une colonne supprimée et disparaît avec sa dernière colonne ; CREATE TABLE … LIKE le copie. Il est tenu en mémoire et reconstruit à l'ouverture de la table ; CHECK TABLE le compare à un index recalculé (termes, fréquences et longueurs des lignes), REPAIR TABLE le reconstruit. La liste de mots vides en vigueur (innodb_ft_enable_stopword, innodb_ft_user_stopword_table, innodb_ft_server_stopword_table) est figée dans l'index à sa création.

Poids par colonne (extension MIRAJ). Dans la liste des colonnes d'un index FULLTEXT (sous ses trois formes : CREATE TABLE, ALTER TABLE … ADD FULLTEXT, CREATE FULLTEXT INDEX), une colonne peut être suivie de WEIGHT n : un nombre réel strictement positif, entier ou décimal (3, 2.5), 1 quand il est absent. Au score, un mot trouvé dans une colonne compte pour son poids (designation WEIGHT 3, notes : un mot de la désignation pèse trois fois un mot des notes), dans les deux modes de miraj_ft_ranking ; l'index et les lignes trouvées ne changent pas.

• Poids nul, négatif ou non numérique (WEIGHT 0, WEIGHT -1, WEIGHT 'deux') : 9051 ; WEIGHT sans valeur, ou dans un index qui n'est pas FULLTEXT (KEY, UNIQUE, PRIMARY KEY, trigramme, SPATIAL) : 1064.
• SHOW CREATE TABLE rend FULLTEXT KEY `ft` (`designation` WEIGHT 3,`notes`) : poids 1 omis, nombre sans zéro inutile. Ce texte est relu par MIRAJ (export miraj-dump, réplication, édition Cluster) ; WEIGHT étant une extension MIRAJ, retirer les poids d'un script destiné à un autre serveur.
• Le poids suit sa colonne renommée (CHANGE, RENAME COLUMN), part avec une colonne supprimée, est copié par CREATE TABLE … LIKE et gardé à la réouverture. SHOW INDEX et information_schema.STATISTICS ne montrent pas les poids (lire SHOW CREATE TABLE).
• WEIGHT n'est pas un mot réservé : une colonne weight se nomme et se pondère normalement (FULLTEXT (weight WEIGHT 2, nom)).

Le commentaire d'un index FULLTEXT est libre : il est rendu tel qu'écrit par SHOW CREATE TABLE, SHOW INDEX (Index_comment) et information_schema.STATISTICS, sans effet sur l'index.

Trigramme. KEY nom (col) COMMENT 'miraj:trigram' (une colonne CHAR ou VARCHAR) crée un index trigramme, extension MIRAJ qui sert col LIKE '%…%' (et LOWER(col) / UPPER(col) LIKE …) sans changer le SQL : SHOW INDEX le rend avec Index_type = TRIGRAM (et Index_comment = miraj:trigram) ; un autre serveur lit la même déclaration comme un index ordinaire commenté. Sur une colonne TEXT, il rend l'erreur 1283.

Moteurs de stockage : tables en mémoire et tables disque#

MIRAJ range une table de deux façons :

Les deux moteurs offrent le même SQL : INSERT, UPDATE, DELETE, SELECT, transactions et ROLLBACK, clés primaires et uniques, index, clés étrangères, contraintes CHECK, déclencheurs, AUTO_INCREMENT, ALTER TABLE, RENAME, TRUNCATE, DROP. Une table disque est aussi durable qu'une table en mémoire : ses écritures validées passent par le journal de la base et sont rejouées après un arrêt brutal.

Choisir le moteur#

Le moteur se choisit par la clause ENGINE de CREATE TABLE (casse indifférente, avec ou sans =) :

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

Une table disque garde le nom déclaré : un script existant écrit en ENGINE=Aria ou ENGINE=MyISAM, ou un miraj-dump relu, recrée donc les mêmes tables disque. information_schema.TABLES.ROW_FORMAT vaut Page pour une table disque, Dynamic pour une table en mémoire.

Moteur par défaut. Un CREATE TABLE sans ENGINE suit la variable default_storage_engine (MIRAJ par défaut), modifiable pour la session ou pour le serveur :

SET default_storage_engine = Aria;          -- tables disque pour cette session
SET GLOBAL default_storage_engine = MyISAM; -- sessions ouvertes ensuite
SET default_storage_engine = DEFAULT;       -- reprend la valeur du serveur

Un nom inconnu est refusé (1286) ; HEAP s'affiche MEMORY. CREATE TABLE … SELECT suit default_storage_engine ; CREATE TABLE … LIKE reprend le moteur de la table source.

Changer de moteur. ALTER TABLE t ENGINE = Aria convertit une table en mémoire en table disque ; ALTER TABLE t ENGINE = InnoDB fait l'inverse. Les lignes sont recopiées d'un fichier à l'autre. Toute autre clause de ALTER TABLE garde le moteur de la table.

SHOW ENGINES et information_schema.ENGINES listent MIRAJ (DEFAULT), puis DISK, Aria et MyISAM (YES, « Alias of DISK »), tous transactionnels (Transactions, XA et Savepoints à YES).

Tables temporaires#

Une table temporaire reste toujours en mémoire. CREATE TEMPORARY TABLE … ENGINE = Aria (ou ALTER TABLE … ENGINE = MyISAM sur une table temporaire) crée la table en mémoire avec l'avertissement 1266 (« Using storage engine MIRAJ for table 'tt' »). default_storage_engine ne s'applique pas aux tables temporaires, et default_tmp_storage_engine est accepté sans effet.

Ce qu'une table disque ne permet pas encore#

Ces fonctions sont refusées, à la création comme par ALTER TABLE, CREATE INDEX ou ALTER TABLE … ENGINE (jamais ignorées en silence) :

Autres différences :

• La place des lignes supprimées d'une table disque n'est pas encore récupérée, ni automatiquement ni par OPTIMIZE TABLE : le fichier .dmrj ne rétrécit pas.
• Une page abîmée du fichier .dmrj (CRC faux) est signalée par CHECK TABLE et se répare par REPAIR TABLE (voir Maintenance des tables).
• Les balayages complets (SUM, COUNT(*) WHERE …, tri sans index) sont plus lents que sur une table en mémoire. La lecture par clé primaire et COUNT(*) sans WHERE sont aussi rapides.
• Les pages modifiées restent en mémoire jusqu'au point de sauvegarde suivant : un très gros chargement dans une table disque occupe de la mémoire jusqu'à ce point.

Un script qui crée une table ENGINE=MyISAM avec un index FULLTEXT est donc refusé (1214) : retirez la clause ENGINE (ou remplacez-la par ENGINE=InnoDB) pour garder cette table en mémoire.

Partitionnement#

Syntaxe#

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

définition_partition :
    PARTITION nom
        [VALUES {LESS THAN {(valeur [, ...]) | MAXVALUE} | IN (valeur | (valeur, ...) [, ...])}]
        [COMMENT [=] 'texte'] [MAX_ROWS [=] n] [MIN_ROWS [=] n]
        [{DATA | INDEX} DIRECTORY [=] 'chemin'] [TABLESPACE [=] nom] [[STORAGE] ENGINE [=] moteur]
        [NODE [=] 'nœud']
        [(SUBPARTITION nom [options] [, ...])]

NODE 'nœud' (édition Cluster, 9002 ailleurs) range les lignes de la partition chez ce seul nœud du cluster au lieu de les copier sur tous : voir le chapitre 17, §17.10.

La clause s'écrit après les options de table, ou dans un commentaire versionné /*!50100 ... */ (c'est ainsi que SHOW CREATE TABLE et miraj-dump la restituent). CREATE TABLE t PARTITION BY ... SELECT ... partitionne une table remplie par une requête. CREATE TABLE ... LIKE recopie le partitionnement.

Exemple exécutable#

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;      -- supprime aussi les lignes de la partition
SELECT id, annee FROM ventes ORDER BY id;     -- (2, 2024) et (3, 2030)
ALTER TABLE ventes TRUNCATE PARTITION pmax;   -- vide la partition

Gestion des partitions (ALTER TABLE)#

Une clause de gestion de partition ne se combine pas avec d'autres clauses dans la même instruction. Sur une table non partitionnée, ces clauses rendent l'erreur 1505.

Règles et erreurs#

Ce qui dépend de l'édition#

• Express et Entreprise : la définition est vérifiée, conservée et restituée, mais les lignes restent dans le seul fichier de la table (pas d'élagage ni de lecture par partition). SELECT ... FROM t PARTITION (p), INSERT INTO t PARTITION (p), UPDATE / DELETE / LOAD DATA avec PARTITION (p), EXCHANGE PARTITION et TRUNCATE PARTITION d'une partition HASH / KEY (ou d'une sous-partition) rendent l'erreur 9002 (édition Cluster requise) ; TRUNCATE PARTITION ALL, ou la liste de toutes les partitions, reste permis.
• Cluster : chaque partition (ou sous-partition) est un segment de fichier distinct (#p#<partition>.mrj), avec élagage des lectures et verrous limités aux segments concernés. Une table rangée en segments est illisible depuis une autre édition (erreur 1194). Voir Cluster et réplication.

TRUNCATE : précisions#

TRUNCATE [TABLE] [base.]table;

• Le compteur AUTO_INCREMENT repart de 1 :

  CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT, n INT);
  INSERT INTO t (n) VALUES (1), (2), (3);
  DELETE FROM t;                 -- conserve le compteur
  INSERT INTO t (n) VALUES (9);  -- id = 4
  TRUNCATE TABLE t;              -- remet le compteur à 1
  INSERT INTO t (n) VALUES (9);  -- id = 1

• TRUNCATE ne déclenche aucun déclencheur DELETE.
• Une table référencée par la clé étrangère d'une autre table ne peut pas être vidée par TRUNCATE, même si aucune ligne n'est référencée : erreur 1701. Utiliser DELETE FROM ou retirer d'abord la clé étrangère.
• TRUNCATE sur une vue : erreur 1347 ('base.vue' is not BASE TABLE). Table inconnue : 1146.
• Sur une table partitionnée, TRUNCATE prend tous les segments (voir la section « Partitionnement »).

Clés étrangères : exemple et comportements vérifiés#

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);
-- ERREUR 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;          -- supprime aussi les deux lignes enfant de pid = 1
UPDATE parent SET id = 20 WHERE id = 2;   -- la ligne enfant suit : pid = 20

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

ALTER TABLE enfant DROP FOREIGN KEY enfant_ibfk_1;
DROP TABLE parent;                        -- désormais permis

• Une valeur NULL dans la colonne de clé étrangère n'a pas besoin de parent.
• Les contrôles sont immédiats, ligne par ligne, dans l'ordre de traitement de l'instruction (voir le chapitre DML, « Clés étrangères ») : une insertion multi-lignes ne peut pas référencer une ligne qu'elle insère plus loin.
• La table référencée doit avoir un index (clé primaire ou UNIQUE) sur les colonnes citées ; sinon la création échoue (erreur 1822 « Missing index for constraint »). Une table référencée introuvable rend 1824.
• Une clé étrangère vers une table d'une autre base : erreur 1235.
• Les lignes enfants modifiées par une cascade ne déclenchent ni déclencheur, ni horodatage ON UPDATE CURRENT_TIMESTAMP, ni contrôle CHECK.

Séquences#

Une séquence est un objet de la base, de même espace de noms que les tables et les vues, qui distribue des entiers 64 bits croissants (ou décroissants) à la demande.

CREATE [OR REPLACE] SEQUENCE [IF NOT EXISTS] [base.]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;

Valeurs par défaut : START au minimum (au maximum si le pas est négatif), INCREMENT BY 1, MINVALUE 1, MAXVALUE 9223372036854775806, CACHE 1000, NOCYCLE. Les mots WITH et BY sont facultatifs (START 5 INCREMENT -1) ; NOCACHE équivaut à 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 : repart du maximum ; cycle_count vaut alors 1)

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

Les appels de séquence d'une instruction sont contrôlés à son ouverture, avant toute exécution, même s'ils ne seraient jamais évalués (SELECT NEXTVAL(t) FROM t WHERE 0, IF(0, NEXTVAL(t), 1) rendent 4089). Dans une routine, chaque instruction du corps est contrôlée au moment où elle s'exécute. DROP SEQUENCE d'une liste qui mêle séquences et autres objets rend une erreur (4090, 1965 ou 4091), mais supprime quand même les séquences citées ; avec IF EXISTS, chaque objet refusé donne une note (1965, 4090, 4091) et les séquences sont supprimées.

Une séquence ne dépend d'aucune transaction : une valeur servie n'est jamais rendue par ROLLBACK. L'état est écrit sur le disque de façon durable avant que la réserve de CACHE valeurs ne soit servie ; après un arrêt brutal ou un redémarrage, la séquence reprend à la fin de la dernière réserve (les valeurs réservées mais non servies sont perdues, jamais servies deux fois). CACHE 0 (NOCACHE) écrit à chaque valeur. Droits : INSERT sur la séquence pour NEXTVAL et SETVAL, SELECT pour LASTVAL. Les séquences sont comprises dans BACKUP / RESTORE et dans miraj-dump. Les codes d'erreur sont détaillés au chapitre 28.

Les séquences figurent dans information_schema.TABLES (type SEQUENCE) et dans information_schema.SEQUENCES, une ligne par séquence avec les colonnes de la norme SQL : SEQUENCE_CATALOG (def), SEQUENCE_SCHEMA, SEQUENCE_NAME, DATA_TYPE (toujours bigint), NUMERIC_PRECISION (64), NUMERIC_PRECISION_RADIX (2), NUMERIC_SCALE (0), START_VALUE, MINIMUM_VALUE, MAXIMUM_VALUE, INCREMENT (en texte) et CYCLE_OPTION (YES / NO). SHOW CREATE SEQUENCE rend le texte de la séquence sans clause ENGINE, comme SHOW CREATE TABLE pour une table en mémoire.

Syntaxes non prises en charge (DDL)#

Ces formes sont reconnues à l'analyse mais refusées avec l'erreur 1235 (« This version of Miraj doesn't yet support ... »), sauf mention contraire :

Maintenance des tables#

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, ...)}];

Ces instructions rendent quatre colonnes Table, Op, Msg_type et Msg_text, avec une ou plusieurs lignes par table citée. La dernière ligne de chaque table donne l'état (status ou error). Si une table échoue, les suivantes sont quand même traitées ; seule l'absence de base courante fait échouer l'instruction entière (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 rend une ligne status / OK par table ; avec PERSISTENT FOR …, une ligne Engine-independent statistics collected la précède (voir aussi le chapitre 9).

Tables disque (ENGINE = Aria, MyISAM ou DISK) :

• CHECK TABLE (à partir de MEDIUM) relit chaque page du fichier .dmrj : somme de contrôle (CRC), numéro et fichier de la page, cases des lignes ; EXTENDED décode aussi chaque ligne. Chaque page abîmée donne une ligne error (Fichier « t.dmrj » : … page 12 : CRC …). Une page modifiée depuis le dernier point de sauvegarde n'est pas relue : sa version en mémoire remplacera celle du fichier.
• REPAIR TABLE d'une table ouverte dont des pages sont abîmées relit ses lignes (celles d'une page encore dans le cache restent lisibles) et les réécrit, aux mêmes numéros, dans un fichier neuf. Si des lignes sont illisibles, seul EXTENDED accepte de les perdre, après copie des fichiers en quarantaine.
• Le super-bloc (page 0) a une copie en fin de première extension (page 63), et le schéma de la table est écrit en deux exemplaires : si l'un est abîmé, la table s'ouvre sur l'autre, qui le réécrit (constat dans les notes de récupération de la base). CHECK TABLE signale aussi un super-bloc, sa copie ou un exemplaire du schéma abîmé. Un fichier .dmrj écrit avant ces copies n'en a pas ; il les reçoit quand il est réécrit en entier (TRUNCATE, ALTER TABLE, REPAIR TABLE).
• Une page abîmée trouvée à l'ouverture de la base rend la table illisible (erreur 1194). REPAIR TABLE seul la laisse en l'état ; REPAIR TABLE … EXTENDED copie le .dmrj et son .dbmrj en quarantaine, récupère les lignes des pages lisibles, signale les pages perdues (ligne warning) et réécrit la table. Si aucune page n'est perdue (seules des valeurs le sont), le journal est rejoué comme pour une table en mémoire ; sinon les numéros de ligne ont changé et les modifications journalisées après le dernier point de sauvegarde ne sont pas rejouées (signalé).
• Une coupure pendant un point de sauvegarde ne déchire aucune page : les pages modifiées, le super-bloc et, après un DDL, les pages du nouveau schéma sont d'abord écrits et synchronisés dans <table>.dmrj.ckpt, puis à leur place ; à l'ouverture, un .ckpt complet est rejoué et un .ckpt incomplet ignoré (rien n'avait été écrit à sa place). Aucune page n'est écrite hors de ce chemin : la double écriture de la base (doublewrite.mrw) ne sert pas encore.
• CHECK TABLE … EXTENDED contrôle aussi, pour chaque ligne vivante, ses valeurs déposées au magasin de LOB .dbmrj (magasin absent : référence de LOB sans magasin).

Dernière ligne de 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

Sans OPTIMIZE TABLE, les lignes supprimées sont retirées automatiquement : pendant les écritures dès qu'elles atteignent 20 % de la table, et à chaque point de sauvegarde. Voir Administration du serveur, § 16.4.3 pour le détail (politique manual, édition Cluster).

REPAIR et OPTIMIZE valident la transaction ouverte de la session et prennent le catalogue en écriture : les autres instructions attendent qu'elles finissent. Une vue reçoit, de REPAIR et d'OPTIMIZE, une ligne Error (« 'd.v' is not BASE TABLE ») suivie de Operation failed ; CHECK TABLE d'une vue rend OK. Les tables information_schema et les tables système reçoivent une note « doesn't support ». Les messages de détail propres à MIRAJ (info, warning, error des contrôles du fichier) sont en français.

Routines stockées, déclencheurs et événements#

Les sections suivantes décrivent la syntaxe du langage MIRAJ. Dans miraj-cli, un corps BEGIN … END qui contient des ; s'écrit entre DELIMITER // et DELIMITER ;. Les privilèges (CREATE ROUTINE, ALTER ROUTINE, EXECUTE, TRIGGER, EVENT) sont décrits au chapitre 15.

CREATE PROCEDURE et CREATE FUNCTION#

CREATE [OR REPLACE] [DEFINER = {compte | CURRENT_USER}]
    PROCEDURE [IF NOT EXISTS] [base.]nom ([[IN | OUT | INOUT] param type [DEFAULT expr] [, ...]])
    [caractéristique ...] corps;

CREATE [OR REPLACE] [DEFINER = {compte | CURRENT_USER}]
    FUNCTION [IF NOT EXISTS] [base.]nom ([param type [DEFAULT expr] [, ...]])
    RETURNS type [caractéristique ...] corps;

caractéristique :
    COMMENT 'texte' | LANGUAGE SQL | [NOT] DETERMINISTIC
  | CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA
  | SQL SECURITY {DEFINER | INVOKER}

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

Le corps est une instruction SQL seule (RETURN expr pour une fonction) ou un bloc [étiquette:] BEGIN … END [étiquette] qui contient, dans cet ordre, ses déclarations puis ses instructions :

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();
-- ERREUR 1305 (42000) : PROCEDURE d.inconnue does not exist
CALL ajuster(99, 5, @q);  SELECT @q;      -- NULL (gestionnaire NOT FOUND)
CALL total_stock(@tot);   SELECT @tot;    -- 22
SELECT categorie(50), categorie(500), categorie(5000);   -- petit | moyen | gros

SHOW CREATE PROCEDURE rend le texte de la routine avec son définisseur (CREATE DEFINER=`root`@`localhost` PROCEDURE `ajuster`(…)), le sql_mode et l'interclassement de sa création ; information_schema.ROUTINES décrit chaque routine (ROUTINE_TYPE, DATA_TYPE du résultat, IS_DETERMINISTIC, SQL_DATA_ACCESS, SECURITY_TYPE). Une instruction d'appel sans parenthèses (CALL p) est admise pour une procédure sans paramètre.

CREATE TRIGGER#

CREATE [OR REPLACE] [DEFINER = {compte | CURRENT_USER}]
    TRIGGER [IF NOT EXISTS] [base.]nom
    {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table
    FOR EACH ROW [{FOLLOWS | PRECEDES} autre_déclencheur]
    corps;
DROP TRIGGER [IF EXISTS] [base.]nom;
SHOW TRIGGERS [FROM base] [LIKE 'motif' | WHERE condition];
SHOW CREATE TRIGGER nom;

Le corps s'exécute une fois par ligne écrite. NEW.col (insertion, mise à jour) désigne la ligne écrite, OLD.col (mise à jour, suppression) la ligne d'avant ; un déclencheur BEFORE peut modifier la ligne par SET NEW.col = …, et une erreur levée (SIGNAL) annule l'instruction qui l'a déclenché. Plusieurs déclencheurs d'un même moment et d'un même événement s'exécutent dans l'ordre de création, que FOLLOWS / PRECEDES ajustent : avec t1, t2 puis t3 … PRECEDES t1, l'ordre est 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;
-- ERREUR 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 = {compte | CURRENT_USER}]
    EVENT [IF NOT EXISTS] [base.]nom
    ON SCHEDULE {AT instant | EVERY n unité [STARTS instant] [ENDS instant]}
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'texte']
    DO corps;

ALTER [DEFINER = compte] EVENT [base.]nom
    [ON SCHEDULE …] [ON COMPLETION [NOT] PRESERVE] [RENAME TO nouveau]
    [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'texte'] [DO corps];
DROP EVENT [IF EXISTS] [base.]nom;
SHOW EVENTS [FROM base] [LIKE 'motif' | WHERE condition];
SHOW CREATE EVENT nom;

Un événement exécute son corps à l'instant AT, ou toutes les n unités (SECOND, MINUTE, HOUR, DAY, WEEK, MONTH…) entre STARTS et ENDS, tant que le planificateur tourne : variable event_scheduler (ON, OFF, ou DISABLED au démarrage par --event-scheduler), modifiable par SET GLOBAL event_scheduler = ON. Un événement terminé (instant AT passé, ou ENDS atteint) est supprimé ; avec ON COMPLETION PRESERVE, il reste, à l'état DISABLED. DISABLE ON SLAVE l'affiche 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      |
+--------------+------------+----------------+----------------+---------+---------------+

Un ON SCHEDULE donné par ALTER EVENT sans STARTS part de l'instant de l'instruction. Erreurs : événement déjà présent 1537 (note avec IF NOT EXISTS), inconnu 1539 (note avec IF EXISTS), OR REPLACE avec IF NOT EXISTS 1221.

Routines : paramètres à valeur par défaut#

Un paramètre IN ou INOUT d'une procédure ou d'une fonction peut porter une valeur par défaut ; un appel qui omet les derniers arguments en prend les valeurs (un paramètre sans valeur par défaut ne peut suivre un paramètre qui en a une) :

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);                                  -- paramètres suivants : leurs valeurs par défaut

Routines : boucles FOR#

Le compteur d'une boucle FOR i IN a .. b est un entier propre à la boucle : il part de a et avance de 1 tant qu'il ne dépasse pas b, évaluée une fois. Avec REVERSE, les bornes restent écrites dans l'ordre croissant : le compteur part de b et recule de 1 tant qu'il n'est pas inférieur à a, évaluée une fois. Une borne NULL, ou a supérieure à b : aucun tour. LEAVE et ITERATE s'emploient avec l'étiquette de la boucle.

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

Une boucle FOR ligne IN … parcourt les lignes d'un curseur déclaré ou d'une requête entre parenthèses ; elle ouvre le curseur et le ferme à sa sortie. Les colonnes de la ligne courante se lisent par ligne.colonne :

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

Un champ que la ligne n'a pas rend l'erreur 4082 ; deux colonnes de même nom dans la requête, l'erreur 1060 (donner un alias). Un champ s'affecte par SET ligne.colonne = …, et la ligne entière peut recevoir une ligne par SELECT … INTO ligne ou FETCH curseur INTO ligne (valeurs converties au type des champs) ; citée seule dans une expression (ligne + 1), elle rend l'erreur 1241.

Un curseur peut prendre des paramètres : DECLARE nom CURSOR (paramètre type, …) FOR requête. Les paramètres sont des variables visibles de la seule requête du curseur (un paramètre masque, dans cette requête, une variable de même nom). Leurs valeurs sont données à l'ouverture, par OPEN nom (arguments) ou par FOR ligne IN nom (arguments) DO ; les arguments sont évalués à ce moment et convertis au type des paramètres :

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

Un nombre d'arguments différent de celui des paramètres (arguments donnés à un curseur sans paramètre, ou OPEN nom sans les arguments attendus) est refusé à la création de la routine par l'erreur 4080 ; deux paramètres de même nom, par l'erreur 1330. Un curseur sans paramètre s'ouvre par OPEN nom ou OPEN nom ().

Routines : GET DIAGNOSTICS#

Dans un gestionnaire, GET DIAGNOSTICS lit l'erreur en cours de traitement. Hors d'une routine, il lit dans des variables utilisateur les conditions laissées par l'instruction précédente (celles de SHOW WARNINGS, erreur comprise), sans les effacer :

CREATE TABLE gd (s VARCHAR(3));
SET sql_mode = '';
INSERT INTO gd VALUES ('trop long'), ('ok');
GET DIAGNOSTICS @n = NUMBER, @lignes = ROW_COUNT;      -- conditions, lignes modifiées
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 |
+----+---------+-------+----------------------------------------+-------+

Un numéro de condition hors limites ne fait pas échouer l'instruction : la condition 1758 s'ajoute à la liste et les variables gardent leur valeur. Il en va de même dans une routine : dans un gestionnaire, la seule condition lisible est l'erreur traitée (NUMBER vaut 1, et 0 hors d'un gestionnaire) ; CONDITION 2 ajoute 1758, et GET STACKED DIAGNOSTICS hors d'un gestionnaire actif ajoute 1887 (« GET STACKED DIAGNOSTICS when handler not active »), sans faire échouer la routine.

MIRAJ_ERRNO désigne l'élément numéro d'erreur de SIGNAL … SET et de GET DIAGNOSTICS CONDITION (le nom historique de cet élément est aussi accepté). Sur une instance en langage PostgreSQL, routines et déclencheurs s'écrivent en PL/pgSQL : voir le 30.13.

Routines : fonctions dans un WHERE (performances)#

Une fonction stockée appelée avec une colonne en argument s'exécute en principe ligne à ligne : la condition WHERE f(col) = … ne peut alors ni utiliser un index ni être poussée vers la lecture de la table. Pour les fonctions les plus simples, MIRAJ remplace l'appel par l'expression de son RETURN avant de choisir le plan, sans rien changer au résultat :

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);   -- lu comme WHERE statut = 1 : index t_statut

L'appel est intégré seulement si toutes ces conditions sont réunies (sinon il s'exécute ligne à ligne, comme avant) :

• il est écrit dans le WHERE d'un SELECT (sous-requêtes et unions comprises), d'un UPDATE ou d'un DELETE d'une seule table ; un appel de la liste du SELECT, d'une condition ON, de GROUP BY, HAVING ou ORDER BY n'est pas intégré ;
• le corps est un seul RETURN expression (ou une fonction LANGUAGE sql réduite à un SELECT d'une expression, sur une instance en langage PostgreSQL), sans variable, sous-requête, lecture de table ni appel d'une autre fonction stockée ;
• l'expression n'emploie que des comparaisons, AND / OR / NOT, IS NULL, IN, BETWEEN, LIKE, CASE, IF, IFNULL, COALESCE, +, -, * entre entiers, ABS, LOWER, UPPER, CONCAT, LTRIM, RTRIM, LEFT, RIGHT, CHAR_LENGTH, LENGTH, YEAR, MONTH, DAY, sur des valeurs de même famille (ni division, ni conversion de type) ;
• chaque argument est une colonne d'une table du bloc, de même type que le paramètre (un VARCHAR peut être plus court), ou un entier littéral ; un appel dont tous les arguments sont constants reste calculé une seule fois, avant la requête ;
• le résultat tient sans conversion dans le type RETURNS (un RETURNS TINYINT d'une somme d'entiers INT n'est pas intégré) ; un résultat texte n'est intégré que comparé à un littéral ou sous IS NULL, un BOOLEAN que comme condition (WHERE f(x), AND f(x), NOT f(x)) ;
• la fonction a été créée avec le même sql_mode que la session, sous l'interclassement général, et elle est SQL SECURITY INVOKER ou appartient au compte de la session ; le droit EXECUTE est contrôlé comme pour un appel ordinaire.

La fonction est relue à chaque instruction : une fonction remplacée ou supprimée est prise en compte aussitôt. EXPLAIN montre la condition intégrée. L'intégration se désactive par SET optimizer_switch = 'function_inlining=off' (même résultat, appel ligne à ligne).

Voir aussi#

• Types de données : détail des types, colonnes générées, AUTO_INCREMENT, contraintes CHECK.
• Recherche vectorielle : index vectoriels.
• Tables temporelles : WITH SYSTEM VERSIONING, périodes, PARTITION BY SYSTEM_TIME.
• Interclassements : COLLATE des bases, tables et colonnes.