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

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

CREATE DATABASE#

CREATE DATABASE [IF NOT EXISTS] nom_base;
CREATE SCHEMA [IF NOT EXISTS] nom_base;

SCHEMA est un synonyme de DATABASE. Les éventuelles options de jeu de caractères (CHARACTER SET, COLLATE) sont acceptées à l'analyse mais sans effet : les tables de la base prennent l'interclassement général tant qu'elles n'en déclarent pas un autre (voir Interclassements).

CREATE DATABASE IF NOT EXISTS gestium;

DROP DATABASE#

DROP DATABASE [IF EXISTS] nom_base;
DROP SCHEMA [IF EXISTS] nom_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 ...]
    [, PRIMARY KEY (colonne [, ...])]
    [, [CONSTRAINT [nom]] UNIQUE [KEY | INDEX] [nom] (colonne [, ...])]
    [, {INDEX | KEY} [nom] (colonne [, ...])]
    [, [CONSTRAINT [nom]] FOREIGN KEY [nom] (colonne [, ...])
         REFERENCES [base.]table_ref (colonne [, ...])
         [ON DELETE action] [ON UPDATE action]]
    [, [CONSTRAINT [nom]] CHECK (expression) [[NOT] ENFORCED]]
) [AUTO_INCREMENT = n] [ENGINE = ...] [CHARSET = ...] [COLLATE = ...] [COMMENT = '...'];

Où définition_colonne s'écrit :

colonne type
    [NOT NULL | NULL]
    [DEFAULT valeur | DEFAULT (expression) | DEFAULT fonction(...)]
    [AUTO_INCREMENT]
    [UNIQUE [KEY]] [PRIMARY KEY]
    [COMMENT 'texte']
    [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]]

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

Le fichier readme.txt livré avec des versions antérieures indique encore les index secondaires non uniques comme non disponibles. C'est désormais inexact : KEY / INDEX dans CREATE TABLE, CREATE INDEX et ALTER TABLE ... ADD INDEX créent bien un index secondaire, nombre illimité par table.

Ces index sont des index par hachage (comme les index de clé primaire et UNIQUE), reconstruits à l'ouverture de la base. Ils ne servent qu'une égalité portant sur la totalité de leurs colonnes (entiers, décimaux, chaînes, dates ; jamais les flottants) ; ils ne sont jamais utilisés pour IN, un intervalle, un préfixe de leurs colonnes, ni pour un ORDER BY. Une colonne générée VIRTUAL peut y figurer (voir Types de données). Les options USING, COMMENT, VISIBLE / INVISIBLE sont acceptées sans effet. Un index UNIQUE identique à un index d'unicité déjà présent est ignoré.

Clés étrangères#

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

La table référencée doit se trouver dans la même base (sinon : fonctionnalité non prise en charge, erreur 1235).

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), clé primaire, contraintes UNIQUE, index secondaires, contraintes CHECK et partitionnement éventuel. Ne sont pas recopiés : le compteur AUTO_INCREMENT (repart de zéro) et les clés étrangères. La nouvelle table est vide.

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;

Le mot AS est facultatif ; la requête peut être écrite entre parenthèses ((SELECT ...)) ou non, et porter sur n'importe quelle source (table, vue, jointure, UNION, sous-requête, agrégat, LIMIT).

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 le type qui porte les valeurs (une chaîne calculée, ou venue d'un UNION ou d'une vue qui en fait une, devient LONGTEXT ; un entier devient INT ou BIGINT selon sa largeur) ;
• la nullabilité du résultat.

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

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).
• 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] table [, table2 ...] [RESTRICT | CASCADE];

RESTRICT et CASCADE sont acceptés à l'analyse sans changer le comportement (il n'y a pas de suppression en cascade des tables qui la référencent : voir l'erreur 3730 ci-dessus). 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.

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

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 ; CONVERT TO CHARACTER SET utf8mb4 [COLLATE nom] convertit toutes les colonnes texte (hors JSON). 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). Ajouter une colonne date/heure NOT NULL sans DEFAULT à une table non vide rend l'erreur 1366 (pas de date zéro).

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;

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

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.

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

Options sans effet#

[DEFAULT] CHARSET / CHARACTER SET seul, COMMENT, ALGORITHM = ..., LOCK = ... sont acceptées mais n'ont aucun effet sur le stockage. 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) : ORDER BY, RENAME INDEX, ADD FULLTEXT / ADD SPATIAL. Le mot-clé INVISIBLE (ou VISIBLE) d'une définition de colonne est accepté mais sans effet : la colonne reste visible (SELECT * la renvoie).

CREATE [UNIQUE] INDEX et DROP INDEX#

CREATE INDEX idx_nom ON client (nom);
CREATE UNIQUE INDEX uq_code ON client (code);
DROP INDEX 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 deux formes acceptent les options ALGORITHM = ... et LOCK = ... sans effet. DROP INDEX nom ON table équivaut à ALTER TABLE table DROP INDEX nom. CREATE INDEX IF NOT EXISTS ne fait rien (note 1061) si un index de ce nom existe déjà ; USING BTREE / USING HASH sont acceptés sans effet avant ou après le nom de la table.

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 19. 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];

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). 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, à cinq exceptions près :

• WITH SYSTEM VERSIONING garde l'historique des lignes (voir le chapitre 11) ;
• 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 donc jamais COMMENT de 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.

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 sans nom reçoit CONSTRAINT_<n> ; les noms de CHECK sont uniques dans toute la base (erreur 3822 en cas de doublon).

Une violation de CHECK rend l'erreur 4025 :

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

Écart à connaître : 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) au lieu de rendre l'erreur 1068 ; deux clauses PRIMARY KEY de table, ou deux colonnes PRIMARY KEY, rendent bien 1068.

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

-- 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 19. Recherche vectorielle.
• ADD SYSTEM VERSIONING et DROP SYSTEM VERSIONING ajoutent ou retirent l'historique des lignes ; les autres clauses d'une table versionnée s'appliquent aussi à son historique (voir le chapitre 11).
• 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

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 écrit pour MariaDB 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).

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 (« Moteur de stockage MIRAJ utilisé pour la table … »). 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 dump MariaDB 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 16, §16.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.

PARTITION BY SYSTEM_TIME (tables versionnées) n'est pas pris en charge (erreur 1235).

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 (comme MariaDB) : START au minimum (au maximum si le pas est négatif), INCREMENT BY 1, MINVALUE 1, MAXVALUE 9223372036854775806, CACHE 1000, NOCYCLE.

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

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. Erreurs : 4084, 4085, 4087, 4089, 4091 (chapitre 27).

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

Les trois instructions rendent le même résultat que MariaDB : 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).

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.
• 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 page déchirée par une coupure pendant son écriture est d'abord restaurée d'après la double écriture de la base (doublewrite.mrw) à l'ouverture, avant toute lecture : la table reste lisible, et le constat figure dans les notes de reprise de la base.

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, § 11.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 une erreur (« is not BASE TABLE ») ; les tables information_schema et les tables système reçoivent une note « doesn't support ». Les messages de détail (info, warning, error) sont en français. Les écarts avec MariaDB sont listés dans docs/LIMITES.md.

Voir aussi#

• Types de données : détail des types, colonnes générées, AUTO_INCREMENT, contraintes CHECK.