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

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.

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#

ENGINE, [DEFAULT] CHARSET / CHARACTER SET, COLLATE, COMMENT, CONVERT TO CHARACTER SET, ALGORITHM = ..., LOCK = ... sont acceptées mais n'ont aucun effet sur le 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 et WITH CHECK OPTION sont acceptés à l'analyse mais sans effet. 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#

Une vue est toujours en lecture seule :

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, à deux exceptions près :

• AUTO_INCREMENT = n fixe le premier numéro attribué (0 est ignoré) ;
• PARTITION BY ... définit le partitionnement (voir plus bas).

SHOW CREATE TABLE ne rend donc jamais ENGINE, CHARSET ni COMMENT de 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 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.
• 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.
• 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.

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

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]
        [(SUBPARTITION nom [options] [, ...])]

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

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 :

Voir aussi#

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