8. Langage DML

Ce chapitre décrit les instructions de manipulation des données (DML) : INSERT, REPLACE, INSERT … ON DUPLICATE KEY UPDATE (insertion ou mise à jour), UPDATE, DELETE, TRUNCATE, le chargement de fichiers (LOAD DATA, LOAD XML), la clause RETURNING, les requêtes paramétrées et les instructions préparées (PREPARE / EXECUTE / DEALLOCATE PREPARE).

Les exemples s'appuient sur deux tables utilisées tout au long des sections 8.1 à 8.3 ; chaque exemple part de l'état laissé par les précédents, et les résultats affichés sont ceux de miraj-cli :

CREATE TABLE clients (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    nom     VARCHAR(50) NOT NULL,
    ville   VARCHAR(50),
    solde   DECIMAL(10,2) DEFAULT 0
);

CREATE TABLE commandes (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    montant  DECIMAL(10,2) NOT NULL,
    date_cmd DATE
);

8.1 INSERT#

Synopsis#

INSERT [IGNORE] INTO table [(colonne, ...)]
    VALUES (valeur, ...) [, (valeur, ...) ...]
    | SET colonne = valeur [, colonne = valeur ...]
    | requête_select
    [ON DUPLICATE KEY UPDATE colonne = valeur [, colonne = valeur ...]]
    [RETURNING élément [, élément ...]]                     -- 6.9

REPLACE INTO table [(colonne, ...)]
    VALUES (valeur, ...) [, (valeur, ...) ...]
    | SET colonne = valeur [, colonne = valeur ...]
    | requête_select
    [RETURNING élément [, élément ...]]

La syntaxe complète (mots facultatifs, alias de ligne) est donnée au §8.6.

Insertion d'une ligne ou de plusieurs lignes#

INSERT INTO clients (nom, ville) VALUES ('Alice', 'Oran');
-- OK, 1 ligne(s) affectée(s)

INSERT INTO clients (nom, ville, solde) VALUES
    ('Bob', 'Alger', 50.00),
    ('Chloé', 'Oran', 0),
    ('David', 'Constantine', NULL);
-- OK, 3 ligne(s) affectée(s)

La forme SET affecte les colonnes une par une, sans liste de colonnes ni liste de valeurs séparée :

INSERT INTO clients SET nom = 'Émile', solde = 10;

Une valeur peut être DEFAULT, qui vaut explicitement la valeur par défaut de la colonne :

INSERT INTO clients (nom, solde) VALUES ('Fatima', DEFAULT);
SELECT * FROM clients;

+----+--------+-------------+-------+
| id | nom    | ville       | solde |
+----+--------+-------------+-------+
|  1 | Alice  | Oran        |  0.00 |
|  2 | Bob    | Alger       | 50.00 |
|  3 | Chloé  | Oran        |  0.00 |
|  4 | David  | Constantine |  NULL |
|  5 | Émile  | NULL        | 10.00 |
|  6 | Fatima | NULL        |  0.00 |
+----+--------+-------------+-------+

Alice et Fatima ont reçu la valeur par défaut de solde (0.00) ; David a un NULL explicite. Une ligne peut être vide (VALUES ()) quand toutes les colonnes ont une valeur par défaut ou sont nullables. Une valeur peut être une expression quelconque, sous-requête comprise (VALUES ((SELECT MAX(id) FROM t) + 1)).

INSERT ... SELECT#

Les lignes insérées viennent du résultat d'une requête plutôt que d'une liste VALUES :

INSERT INTO commandes (client_id, montant, date_cmd) VALUES
    (1, 80.00, '2026-10-01'), (1, 45.50, '2026-10-03'),
    (2, 20.00, '2026-10-05'), (3, 150.00, '2026-10-06');

CREATE TABLE gros_clients (client_id INT, total DECIMAL(10,2));

INSERT INTO gros_clients
SELECT client_id, SUM(montant)
FROM commandes
GROUP BY client_id
HAVING SUM(montant) > 100;
-- OK, 2 ligne(s) affectée(s)

SELECT * FROM gros_clients;

+-----------+--------+
| client_id | total  |
+-----------+--------+
|         1 | 125.50 |
|         3 | 150.00 |
+-----------+--------+

INSERT IGNORE transforme en avertissement les erreurs qui, sinon, feraient échouer la ligne (doublon de clé, ligne sans parent, valeur trop longue, NULL dans une colonne NOT NULL) : la ligne fautive est écartée ou sa valeur corrigée, les autres sont insérées (détail au §8.6).

AUTO_INCREMENT#

Une colonne AUTO_INCREMENT reçoit automatiquement la valeur suivante quand l'insertion ne lui donne pas de valeur (colonne absente de la liste, NULL ou DEFAULT explicite). LAST_INSERT_ID() rend, dans la session, la valeur attribuée par la dernière insertion ; pour un INSERT de plusieurs lignes, la première valeur attribuée :

INSERT INTO clients (nom) VALUES ('Grace');
SELECT LAST_INSERT_ID();                         -- 7
INSERT INTO clients (nom) VALUES ('Hugo'), ('Inès');
SELECT LAST_INSERT_ID();                         -- 8 (Hugo ; Inès a reçu 9)

Donner explicitement une valeur plus grande que le compteur courant le fait avancer : après INSERT INTO t (id, nom) VALUES (10, 'x'), la ligne suivante sans valeur reçoit 11.

DEFAULT et colonnes générées#

Une colonne omise de la liste des colonnes reçoit sa valeur par défaut (DEFAULT de la définition, ou NULL si la colonne est nullable sans DEFAULT). Un DEFAULT peut être une expression, évaluée ligne par ligne et pouvant citer les autres colonnes de la même ligne, ou une séquence (DEFAULT NEXTVAL(seq), §8.7).

Une colonne générée (GENERATED ALWAYS AS (expr), VIRTUAL ou STORED/PERSISTENT) n'est jamais renseignée par l'insertion : une valeur explicite donnée pour une colonne générée est ignorée (elle n'est pas refusée), et la colonne prend la valeur de son expression (exemple au §8.6).

REPLACE#

REPLACE INTO insère une ligne comme INSERT, mais si elle entre en conflit avec une ligne existante sur la clé primaire ou une contrainte UNIQUE, la ligne en conflit est d'abord supprimée avant l'insertion (déclencheurs DELETE et INSERT exécutés comme pour ces instructions). Les colonnes non citées reprennent leur valeur par défaut :

REPLACE INTO clients (id, nom, ville) VALUES (1, 'Alice', 'Alger');
-- OK, 2 ligne(s) affectée(s)        (1 supprimée + 1 insérée)
SELECT * FROM clients WHERE id = 1;

+----+-------+-------+-------+
| id | nom   | ville | solde |
+----+-------+-------+-------+
|  1 | Alice | Alger |  0.00 |
+----+-------+-------+-------+

REPLACE accepte aussi les formes SET et SELECT :

CREATE TABLE archive (id INT PRIMARY KEY, nom VARCHAR(50));
REPLACE INTO archive (id, nom) SELECT id, nom FROM clients WHERE ville = 'Alger';
-- OK, 2 ligne(s) affectée(s)        (Alice et Bob insérés)
REPLACE INTO archive SET id = 2, nom = 'Bob B.';
-- OK, 2 ligne(s) affectée(s)        (ancienne ligne 2 supprimée, nouvelle insérée)

ON DUPLICATE KEY UPDATE#

Une alternative à REPLACE qui met à jour la ligne en conflit au lieu de la remplacer : la ligne existante garde son identité (pas de suppression, pas de déclencheur DELETE) et seules les colonnes citées dans ON DUPLICATE KEY UPDATE sont modifiées. Une ligne sans conflit est insérée normalement.

INSERT INTO clients (id, nom, solde) VALUES (2, 'Bob', 100)
ON DUPLICATE KEY UPDATE solde = solde + VALUES(solde);
-- OK, 2 ligne(s) affectée(s)        (ligne existante modifiée : solde 50.00 -> 150.00)

VALUES(colonne) désigne, dans la clause ON DUPLICATE KEY UPDATE, la valeur que la ligne en conflit aurait reçue à l'insertion. La ligne proposée peut aussi recevoir un alias (VALUES ... AS alias), forme recommandée :

INSERT INTO clients (id, nom, solde) VALUES (2, 'Bob', 100) AS nouvelle
ON DUPLICATE KEY UPDATE solde = solde + nouvelle.solde;
SELECT id, nom, solde FROM clients WHERE id = 2;

+----+-----+--------+
| id | nom | solde  |
+----+-----+--------+
|  2 | Bob | 250.00 |
+----+-----+--------+

Erreurs typiques#

8.2 UPDATE#

Synopsis#

UPDATE [LOW_PRIORITY] [IGNORE] table [[AS] alias]
    SET colonne = valeur | DEFAULT [, colonne = valeur ...]
    [WHERE condition]
    [ORDER BY ...] [LIMIT n]

UPDATE [LOW_PRIORITY] [IGNORE] table1 [[AS] alias1]
    {[INNER] JOIN | CROSS JOIN | LEFT [OUTER] JOIN | RIGHT [OUTER] JOIN | ,} table2 [[AS] alias2]
        [ON condition | USING (colonne, ...)]
    [...autres jointures...]
    SET colonne = valeur [, colonne = valeur ...]
    [WHERE condition]

La colonne affectée peut être qualifiée par sa table ou par sa base et sa table (SET c.solde = …, SET ventes.clients.solde = …).

UPDATE mono-table#

UPDATE clients SET solde = solde + 20 WHERE ville = 'Oran';
-- OK, 1 ligne(s) affectée(s)        (Chloé : 0.00 -> 20.00)

ORDER BY et LIMIT, sur un UPDATE d'une seule table, choisissent quelles lignes parmi celles retenues par WHERE sont effectivement modifiées :

UPDATE clients SET solde = -5 WHERE id IN (5, 6);
UPDATE clients SET solde = 0 WHERE solde < 0 ORDER BY id LIMIT 1;
-- OK, 1 ligne(s) affectée(s)
SELECT id, nom, solde FROM clients WHERE id IN (5, 6);

+----+--------+-------+
| id | nom    | solde |
+----+--------+-------+
|  5 | Émile  |  0.00 |
|  6 | Fatima | -5.00 |
+----+--------+-------+

UPDATE multi-tables (mise à jour avec jointure)#

UPDATE accepte les mêmes types de jointure qu'un SELECT (voir chapitre 9) : JOIN / INNER JOIN, CROSS JOIN, la virgule, LEFT [OUTER] JOIN, RIGHT [OUTER] JOIN, avec ON ou USING, des alias, et une table dérivée (sous-requête) comme table jointe. Une seule table est réellement modifiée par l'instruction : la première de la jointure (celle qui suit UPDATE). Les autres tables de la jointure ne servent qu'à calculer les nouvelles valeurs et à filtrer les lignes.

UPDATE clients c
JOIN commandes o ON o.client_id = c.id
SET c.solde = c.solde - o.montant
WHERE o.id = 3;
-- OK, 1 ligne(s) affectée(s)        (Bob : 250.00 -> 230.00)

Avec une jointure externe, pour toucher les lignes sans correspondance (ici, les clients sans commande) :

UPDATE clients c
LEFT JOIN commandes o ON o.client_id = c.id
SET c.solde = c.solde + 1
WHERE o.id IS NULL;
-- OK, 6 ligne(s) affectée(s)

Avec une table dérivée comme source des valeurs :

UPDATE clients c
JOIN (
    SELECT client_id, SUM(montant) AS total
    FROM commandes
    GROUP BY client_id
) t ON t.client_id = c.id
SET c.solde = c.solde - t.total;
-- OK, 3 ligne(s) affectée(s)

SELECT * FROM clients ORDER BY id;

+----+--------+-------------+---------+
| id | nom    | ville       | solde   |
+----+--------+-------------+---------+
|  1 | Alice  | Alger       | -125.50 |
|  2 | Bob    | Alger       |  210.00 |
|  3 | Chloé  | Oran        | -130.00 |
|  4 | David  | Constantine |    NULL |
|  5 | Émile  | NULL        |    1.00 |
|  6 | Fatima | NULL        |   -4.00 |
|  7 | Grace  | NULL        |    1.00 |
|  8 | Hugo   | NULL        |    1.00 |
|  9 | Inès   | NULL        |    1.00 |
+----+--------+-------------+---------+

Règles propres à l'UPDATE multi-tables :

• SET n'affecte que des colonnes de la première table (celle qui suit UPDATE) ; affecter une colonne d'une autre table de la jointure, ou de plusieurs tables, échoue avec l'erreur 1235 (multi-table UPDATE writing a joined table / writing several tables). Pour écrire dans l'autre table, inverser l'ordre de la jointure.
• ORDER BY et LIMIT ne sont pas admis dès qu'il y a plusieurs tables (1221).
• Une ligne de la table modifiée qui correspond à plusieurs lignes de la jointure n'est modifiée qu'une fois, avec la première correspondance.
• La table modifiée peut être une vue modifiable (voir « Vues modifiables », chapitre 7) ; une vue non modifiable est refusée (1288).

Erreurs typiques#

8.3 DELETE#

Synopsis#

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table [[AS] alias]
    [WHERE condition] [ORDER BY ...] [LIMIT n] [RETURNING élément, ...]   -- 6.9

DELETE [IGNORE] cible [, cible ...] FROM table1 [jointure ...] [WHERE condition]
DELETE [IGNORE] FROM cible [, cible ...] USING table1 [jointure ...] [WHERE condition]

DELETE simple#

INSERT INTO commandes (client_id, montant, date_cmd) VALUES
    (4, 12.00, '2026-10-07'), (7, 0.00, '2026-10-08'), (99, 5.00, '2026-10-08');

DELETE FROM commandes WHERE montant = 0;
-- OK, 1 ligne(s) affectée(s)
DELETE FROM clients WHERE solde IS NULL ORDER BY id LIMIT 1;
-- OK, 1 ligne(s) affectée(s)        (David)

DELETE FROM table sans condition vide la table ; voir §8.7 pour la comparaison avec TRUNCATE.

DELETE multi-tables#

Les deux formes suppriment des lignes d'une jointure : les cibles sont nommées avant FROM (première forme) ou après FROM et avant USING (seconde forme), par leur nom ou leur alias ; la jointure qui suit FROM (ou USING) choisit les lignes. Toute table de la jointure peut être une cible, et plusieurs à la fois ; une table de la jointure qui n'est pas citée comme cible ne sert qu'à filtrer.

-- les commandes dont le client n'existe plus (anti-jointure)
DELETE o FROM commandes o
LEFT JOIN clients c ON c.id = o.client_id
WHERE c.id IS NULL;
-- OK, 2 ligne(s) affectée(s)        (commandes des clients 4 et 99)

-- un client d'Oran et ses commandes, en une instruction
DELETE c, o FROM clients c
JOIN commandes o ON o.client_id = c.id
WHERE c.ville = 'Oran';
-- OK, 2 ligne(s) affectée(s)        (Chloé et sa commande 4)

-- même forme avec USING : la cible est la ligne de gros_clients
DELETE FROM g USING gros_clients g JOIN clients c ON c.id = g.client_id WHERE c.nom = 'Alice';
-- OK, 1 ligne(s) affectée(s)

Règles :

• Toutes les correspondances de la jointure sont relevées avant la première suppression : une ligne supprimée ne change pas la jointure en cours.
• Une ligne qui correspond plusieurs fois (client à plusieurs commandes) n'est supprimée et comptée qu'une fois ; deux alias d'une même table ne font qu'une cible. Les lignes affectées sont la somme des lignes supprimées de toutes les cibles.
• Les cibles sont vidées dans l'ordre de la jointure, chacune avec ses déclencheurs DELETE et ses clés étrangères, comme un DELETE ordinaire ; une erreur (par exemple 1451, ligne parente encore référencée par une table qui n'est pas vidée) annule toute l'instruction.
• Une cible absente de la jointure est une erreur (1109, table inconnue).
• ORDER BY et LIMIT ne sont pas admis dans la forme multi-tables (1221), ni RETURNING.
• Privilège DELETE sur chaque cible, SELECT sur les tables lues.

Erreurs typiques#

8.4 Requêtes paramétrées#

Deux styles de paramètres sont acceptés dans le texte SQL, à la place d'une valeur littérale :

SELECT * FROM clients WHERE ville = ? AND solde > ?;
SELECT * FROM clients WHERE ville = :ville AND solde > :seuil;

Les deux styles ne sont pas mélangés dans une même instruction. Les paramètres peuvent apparaître partout où une expression est attendue : WHERE, SET, VALUES, LIMIT, etc. C'est la forme recommandée pour toute valeur qui vient de l'utilisateur ou de l'application (via l'API, un connecteur réseau ou miraj-cli), plutôt que de composer le texte SQL par concaténation : les valeurs liées ne sont jamais interprétées comme du SQL, ce qui évite les injections et évite de reformater les littéraux (dates, chaînes à échapper, nombres) à la main. Une instruction dont un paramètre n'a pas reçu de valeur échoue (1210, Incorrect arguments to parameter 1).

EXECUTE ... USING (ci-dessous) lie des valeurs aux paramètres ? d'une instruction préparée, dans l'ordre.

8.5 PREPARE, EXECUTE, DEALLOCATE PREPARE#

Une instruction préparée sépare l'analyse du texte SQL (faite une fois par PREPARE) de son exécution (faite autant de fois que nécessaire par EXECUTE), avec des paramètres ? liés à chaque exécution. C'est la forme dynamique du SQL, utile quand le texte de l'instruction est construit par le code (nom de table variable, requête générée) plutôt qu'écrit une fois pour toutes.

Synopsis#

PREPARE nom FROM expression

EXECUTE nom [USING expression [, expression ...]]
EXECUTE IMMEDIATE expression [USING expression [, expression ...]]

{DEALLOCATE | DROP} PREPARE nom

• expression de PREPARE est le texte de l'instruction à préparer : un littéral chaîne, une variable utilisateur (@sql), ou toute expression qui produit une chaîne, évaluée à l'exécution de PREPARE. Le texte doit contenir exactement une instruction (un ; final est toléré).
• nom n'est pas sensible à la casse et désigne l'instruction préparée pour les EXECUTE et DEALLOCATE PREPARE suivants, dans la même session.
• Préparer de nouveau sous un nom déjà utilisé remplace silencieusement l'instruction préparée précédente.
• EXECUTE nom USING ... lie les valeurs de USING, dans l'ordre, aux paramètres ? du texte préparé ; leur nombre doit correspondre exactement à celui des paramètres.
• EXECUTE IMMEDIATE expression prépare, exécute puis oublie une instruction en une seule étape, sans lui donner de nom.
• DEALLOCATE PREPARE nom (ou DROP PREPARE nom, synonymes) libère l'instruction préparée ; les EXECUTE suivants sous ce nom échouent.

Cycle de vie#

1. PREPARE analyse le texte et le range sous un nom, pour la session courante.
2. EXECUTE (une ou plusieurs fois) lie les paramètres et exécute l'instruction déjà analysée.
3. DEALLOCATE PREPARE libère la préparation ; elle est aussi implicitement perdue à la fin de la session.

Exemple complet#

PREPARE lire FROM 'SELECT nom FROM clients WHERE id = ?';
EXECUTE lire USING 1;      -- Alice
EXECUTE lire USING 2;      -- Bob
DEALLOCATE PREPARE lire;
EXECUTE lire USING 1;
-- ERREUR 1243 (HY000) : Unknown prepared statement handler (lire) given to EXECUTE

Texte construit dynamiquement (nom de table variable), un cas d'usage typique dans une routine :

SET @table = 'clients';
SET @sql = CONCAT('UPDATE ', @table, ' SET solde = solde * (1 - ?) WHERE id = ?');
PREPARE remise FROM @sql;
EXECUTE remise USING 0.10, 2;      -- OK, 1 ligne(s) affectée(s) (Bob : 210.00 -> 189.00)
DEALLOCATE PREPARE remise;

EXECUTE IMMEDIATE, pour une instruction à usage unique :

EXECUTE IMMEDIATE CONCAT('SELECT COUNT(*) FROM ', @table);
EXECUTE IMMEDIATE 'INSERT INTO clients (nom) VALUES (?)' USING 'Hicham';

Erreurs typiques#

8.6 Compléments : INSERT, REPLACE, ON DUPLICATE KEY UPDATE#

Les exemples de cette section et des suivantes reprennent les tables clients et commandes du début du chapitre, recréées vides, avec en plus une colonne générée nom_maj VARCHAR(50) AS (UPPER(nom)) STORED dans clients et FOREIGN KEY (client_id) REFERENCES clients (id) dans commandes.

Syntaxe complète reconnue#

{INSERT | REPLACE} [LOW_PRIORITY | DELAYED] [HIGH_PRIORITY] [IGNORE] [INTO] [base.]table
    [(colonne [, ...])]
    { {VALUES | VALUE} (expr | DEFAULT [, ...]) [, (...) ...] [AS alias [(colonne, ...)]]
    | SET colonne = expr [, ...]
    | (requête_select) | requête_select }
    [ON DUPLICATE KEY UPDATE colonne = expr [, ...]]      -- INSERT seulement
    [RETURNING élément [, ...]]                           -- 6.9

• INTO est facultatif. VALUE est un synonyme de VALUES. () comme liste de colonnes (INSERT INTO t () VALUES ()) insère une ligne toute par défaut.
• LOW_PRIORITY, DELAYED et HIGH_PRIORITY sont acceptés et sans effet.
• IGNORE n'existe pas pour REPLACE (REPLACE IGNORE : erreur 1064), ni REPLACE ... ON DUPLICATE KEY UPDATE.
• La table cible ne peut pas porter d'alias (INSERT INTO clients AS c ... : 1064).
• La forme SET colonne = expr n'accepte pas le mot-clé DEFAULT comme valeur (INSERT INTO clients SET solde = DEFAULT : erreur 1064) ; omettre la colonne a le même effet. DEFAULT reste admis dans VALUES (...) et dans UPDATE ... SET col = DEFAULT.
• La requête d'un INSERT ... SELECT peut commencer par WITH (INSERT INTO t WITH x AS (…) SELECT …) ; le WITH ne se place pas devant INSERT.
• INSERT INTO t PARTITION (p) ... : édition Cluster seulement (9002 ailleurs).

Exemple : insertion, colonne générée, valeur par défaut#

INSERT INTO clients (nom, ville) VALUES ('Alice', 'Oran'), ('Bob', 'Alger'), ('Chloé', 'Oran');
-- OK, 3 ligne(s) affectée(s)

INSERT INTO clients (nom, nom_maj) VALUES ('Dan', 'IGNORE');   -- valeur de nom_maj ignorée
SELECT id, nom, nom_maj FROM clients;

+----+-------+---------+
| id | nom   | nom_maj |
+----+-------+---------+
|  1 | Alice | ALICE   |
|  2 | Bob   | BOB     |
|  3 | Chloé | CHLOÉ   |
|  4 | Dan   | DAN     |
+----+-------+---------+

INSERT INTO clients (nom, ville) VALUES ('a');
-- ERREUR 1136 (21S01) : Column count doesn't match value count at row 1
INSERT INTO clients (nom, inconnue) VALUES ('a', 'b');
-- ERREUR 1054 (42S22) : Unknown column 'inconnue' in 'field list'
INSERT INTO clients (nom) VALUES (NULL);
-- ERREUR 1048 (23000) : Column 'nom' cannot be null
INSERT INTO clients (ville) VALUES ('x');
-- ERREUR 1364 (HY000) : Field 'nom' doesn't have a default value
INSERT INTO clients (nom) VALUES (REPEAT('x', 60));
-- ERREUR 1406 (22001) : Data too long for column 'nom' at row 1

Ces refus sont ceux du mode strict (STRICT_TRANS_TABLES, par défaut). Hors mode strict (sql_mode sans STRICT_TRANS_TABLES ni STRICT_ALL_TABLES), un NULL écrit explicitement dans une colonne NOT NULL par INSERT, REPLACE ou ON DUPLICATE KEY UPDATE reçoit la valeur implicite du type (0, chaîne vide, première valeur d'un ENUM), jamais le DEFAULT déclaré, avec l'avertissement 1048, et cela même pour une instruction d'une seule ligne (la référence garde dans ce cas l'erreur 1048) :

SET sql_mode = '';
INSERT INTO clients (nom) VALUES (NULL);   -- ligne insérée avec nom = '', avertissement 1048

INSERT IGNORE : exactement ce qui est transformé en avertissement#

Avec VALUES, SET et SELECT, IGNORE convertit en avertissement (ligne écartée ou valeur corrigée) :

Restent des erreurs même avec IGNORE : nombre de valeurs (1136), colonne inconnue (1054), table inconnue (1146), date invalide et NULL dans une colonne date NOT NULL (pas de « date zéro »).

L'avertissement 1364 d'une colonne NOT NULL sans DEFAULT absente de la liste de colonnes (avec IGNORE, hors mode strict, et pour LOAD DATA même en mode strict) est donné une fois par instruction et par colonne, avant les autres avertissements, quel que soit le nombre de lignes (même aucune) ; chaque ligne reçoit la valeur implicite du type (un ENUM NOT NULL sa première valeur, sans avertissement). Une colonne qu'un déclencheur BEFORE INSERT cite par NEW.col compte comme renseignée : ni erreur ni avertissement 1364, même en mode strict. Une ligne écartée ne consomme pas de numéro AUTO_INCREMENT. Les clés étrangères sont contrôlées ligne par ligne, dans l'ordre : une ligne qui référence une ligne insérée plus loin dans la même instruction est écartée, les suivantes sont jugées sur l'état qu'elle laisse.

INSERT IGNORE INTO commandes (client_id, montant) VALUES (99, 5), (3, 7);
-- OK, 1 ligne(s) affectée(s)
-- Avertissement : Cannot add or update a child row: a foreign key constraint fails
--   (`d`.`commandes`, CONSTRAINT `commandes_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`))

INSERT IGNORE INTO clients (id, nom) VALUES (1, 'Doublon'), (10, 'Nouveau');
-- OK, 1 ligne(s) affectée(s)
-- Avertissement : Duplicate entry '1' for key 'PRIMARY'

INSERT IGNORE INTO clients (nom) VALUES (NULL);
-- OK, 1 ligne(s) affectée(s)          (nom = chaîne vide)
-- Avertissement : Column 'nom' cannot be null

Sans IGNORE, une instruction INSERT multi-lignes est tout ou rien : la première erreur annule toutes les lignes de l'instruction, y compris les lots déjà produits par un INSERT ... SELECT. Les clés étrangères sont contrôlées ligne par ligne, dans l'ordre des lignes : une ligne peut référencer une ligne insérée avant elle par la même instruction (ou elle-même), jamais une ligne insérée plus loin (erreur 1452, toute l'instruction est annulée) :

CREATE TABLE n (id INT PRIMARY KEY, sup INT, FOREIGN KEY (sup) REFERENCES n (id));
INSERT INTO n VALUES (1, NULL), (2, 1);   -- OK, 2 ligne(s) affectée(s)
INSERT INTO n VALUES (4, 3), (3, NULL);   -- ERREUR 1452 : la ligne 3 n'existe pas encore
SELECT * FROM n;                          -- (1, NULL) et (2, 1) seulement

INSERT ... SELECT#

• La requête peut être écrite avec ou sans parenthèses, et commencer par WITH.
• Le résultat est inséré par lots de 4 096 lignes ; si la table cible est aussi lue par la requête (y compris dans une sous-requête ou une table dérivée), la requête est d'abord lue en entier avant la première insertion :

  INSERT INTO clients (nom) SELECT nom FROM clients;   -- OK : double le nombre de lignes

• Tables sources verrouillées en lecture, table cible verrouillée en écriture pendant toute la lecture.
• IGNORE, ON DUPLICATE KEY UPDATE et REPLACE fonctionnent avec SELECT (ON DUPLICATE KEY UPDATE : pas d'alias de ligne AS alias après un SELECT).

REPLACE : comportement précis#

Pour chaque ligne, toute ligne existante en conflit sur la clé primaire ou sur une clé UNIQUE (une par clé) est supprimée, puis la nouvelle ligne est insérée — jamais de mise à jour sur place. Les colonnes non citées reprennent donc leur valeur par défaut (exemple au §8.1).

Lignes affectées = lignes supprimées + lignes insérées (les lignes enfants supprimées ou modifiées par ON DELETE ne comptent pas). Sous RESTRICT / NO ACTION, une ligne parente référencée rend 1451 et toute l'instruction est annulée. Les conversions sont contrôlées avant toute écriture ; les clés étrangères de chaque ligne insérée dès son écriture, sur l'état laissé par les lignes précédentes. REPLACE s'exécute toujours dans une transaction. Déclencheurs : BEFORE INSERT, puis BEFORE / AFTER DELETE autour de chaque ligne remplacée, puis AFTER INSERT.

ON DUPLICATE KEY UPDATE : comportement précis#

• La première ligne en conflit est mise à jour : clé primaire d'abord, puis clés UNIQUE dans l'ordre de définition (lignes écrites plus tôt par la même instruction comprises). Les affectations sont évaluées de gauche à droite.
• Lignes affectées : 1 par ligne insérée, 2 par ligne existante réellement modifiée, 0 par ligne existante laissée telle quelle ; le total est la somme.
• VALUES(col) rend la valeur proposée pour col dans cette clause (y compris dans une sous-requête de la clause) ; ailleurs, VALUES(col) vaut NULL. VALUE(col) en est un synonyme (table qualifiée comprise : VALUE(t2.b)), à une différence près : dans une sous-requête de la clause, il vaut NULL quand col désigne une colonne d'une table de la sous-requête. Une colonne inconnue ou d'une autre table rend 1054 ; VALUES(col) et VALUE(col) sont refusés dans un DEFAULT, un CHECK et une colonne générée. L'alias de ligne (VALUES (...) AS nouvelle ou AS nouvelle (a, b, c), qui renomme aussi les colonnes) est la forme recommandée.
• Une ligne mise à jour ne consomme pas de valeur AUTO_INCREMENT ; LAST_INSERT_ID() rend la première valeur générée, sinon la valeur AUTO_INCREMENT de la dernière ligne en conflit.
• La clause peut contenir des sous-requêtes, corrélées ou non à la ligne en conflit (b = (SELECT v + 100 FROM u WHERE u.id = t2.a)).
• Avec IGNORE, les erreurs 1062, 1452, 1451 et 1048 de la partie mise à jour deviennent des avertissements et la ligne reste inchangée. ON DUPLICATE KEY UPDATE s'exécute toujours dans une transaction.
• Les colonnes ON UPDATE CURRENT_TIMESTAMP sont horodatées comme dans un UPDATE ; les colonnes générées STORED et les contraintes CHECK sont recalculées / vérifiées sur la ligne modifiée.

Exemple (table t2 (a INT PRIMARY KEY, b INT) contenant (1,1) et (2,2)) :

INSERT INTO t2 VALUES (1,10),(3,30),(2,20) ON DUPLICATE KEY UPDATE b = VALUES(b);
-- OK, 5 ligne(s) affectée(s)      (2 + 1 + 2)
SELECT * FROM t2 ORDER BY a;       -- (1,10) (2,20) (3,30)

INSERT INTO t2 VALUES (1,10) ON DUPLICATE KEY UPDATE b = b;
-- OK, 0 ligne(s) affectée(s)      (rien ne change)

INSERT INTO t2 VALUES (1, 5) AS nv (x, y) ON DUPLICATE KEY UPDATE b = b + nv.y;
-- OK, 2 ligne(s) affectée(s)      (b : 10 -> 15)

AUTO_INCREMENT et LAST_INSERT_ID()#

Sur un INSERT multi-lignes, LAST_INSERT_ID() rend la première valeur générée de l'instruction. Une ligne écartée par IGNORE, ou mise à jour par ON DUPLICATE KEY UPDATE, ne consomme pas de numéro.

8.7 Compléments : UPDATE et DELETE#

Lignes affectées#

Le nombre de lignes affectées par un UPDATE est celui des lignes retenues par le WHERE, qu'elles changent ou non :

CREATE TABLE u (id INT PRIMARY KEY, v INT);
INSERT INTO u VALUES (1,1),(2,2),(3,3);
UPDATE u SET v = v WHERE id <= 2;               -- OK, 2 ligne(s) affectée(s)
UPDATE u SET v = 0 ORDER BY id DESC LIMIT 1;    -- OK, 1 ligne(s) affectée(s)

Modificateurs : LOW_PRIORITY, QUICK, IGNORE#

UPDATE [LOW_PRIORITY] [IGNORE] table ...
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] ...

LOW_PRIORITY et QUICK sont acceptés et sans effet. Sans IGNORE, la première ligne fautive fait échouer toute l'instruction :

UPDATE u SET id = 1 WHERE id = 3;
-- ERREUR 1062 (23000) : Duplicate entry '1' for key 'PRIMARY'

UPDATE IGNORE : une ligne dont l'écriture viole une clé primaire ou UNIQUE (1062), une clé étrangère (1451 / 1452), une contrainte CHECK ou le partitionnement reste telle quelle, avec un avertissement ; les conversions se font comme hors mode strict (valeur ramenée ou implicite, 1048, 1264, 1265, 1366 en avertissement). Les lignes laissées ne déclenchent pas AFTER UPDATE et ne comptent pas dans les lignes affectées. Les lignes sont écrites une à une, chacune voyant les précédentes :

DELETE FROM u;
INSERT INTO u VALUES (1,1),(2,2),(3,3);
UPDATE IGNORE u SET id = id + 1;
-- OK, 1 ligne(s) affectée(s)
-- Avertissement : Duplicate entry '2' for key 'PRIMARY'
-- Avertissement : Duplicate entry '3' for key 'PRIMARY'
SELECT * FROM u;                     -- (1,1) (2,2) (4,3) : seule la ligne 3 a pu passer à 4

UPDATE IGNORE u SET v = 'abc' WHERE id = 1;
-- OK, 1 ligne(s) affectée(s)          (v = 0)
-- Avertissement : Incorrect integer value: 'abc' for column `d`.`u`.`v` at row 1

DELETE IGNORE : une ligne dont la suppression échouerait sur une clé étrangère (1451, cascades comprises : la ligne parente et ses enfants restent ensemble) est laissée avec un avertissement, sans AFTER DELETE ; les autres lignes sont supprimées. Valable pour le DELETE simple comme multi-tables.

-- clients 2 et 4 ; seul le client 2 a des commandes
DELETE IGNORE FROM clients WHERE id IN (2, 4);
-- OK, 1 ligne(s) affectée(s)
-- Avertissement : Cannot delete or update a parent row: a foreign key constraint fails (...)

SET : expressions, DEFAULT, sous-requêtes#

UPDATE clients SET solde = DEFAULT WHERE id = 2;            -- valeur par défaut de la colonne
UPDATE u SET v = (SELECT COUNT(*) FROM u);                  -- sous-requête, lue avant l'écriture
DELETE FROM clients WHERE id IN (SELECT id FROM clients WHERE solde < 0);

Les valeurs et lignes visées sont calculées avant la première écriture : une sous-requête qui lit la table modifiée voit l'état d'avant l'instruction. ORDER BY d'un UPDATE ou d'un DELETE d'une seule table peut contenir des sous-requêtes.

Une colonne affectée peut être qualifiée par la table et la base (UPDATE ventes.cible SET ventes.cible.n = 1, UPDATE cible c SET ventes.c.n = 1), dans un UPDATE à une ou plusieurs tables comme dans ON DUPLICATE KEY UPDATE : le qualificatif doit désigner la table modifiée (par son alias s'il y en a un, base comprise), sinon l'erreur 1054 cite le nom écrit en entier (« Unknown column 'autre.cible.n' in 'field list' »).

Colonnes générées : jamais affectables (valeur explicite ignorée, sauf DEFAULT) ; colonnes STORED recalculées ; ON UPDATE CURRENT_TIMESTAMP appliquée quand la ligne change réellement. Contraintes CHECK vérifiées sur la ligne complète (erreur 4025). Déclencheurs BEFORE / AFTER UPDATE exécutés pour toutes les lignes retenues, même inchangées. Hors mode strict, un NULL affecté à une colonne NOT NULL devient la valeur implicite du type, avec l'avertissement 1048.

DELETE sans condition et TRUNCATE#

DELETE FROM table sans WHERE, ORDER BY ni LIMIT supprime toutes les lignes et conserve le compteur AUTO_INCREMENT. Hors transaction, sans déclencheur ni clé étrangère concernée, il est exécuté d'un seul bloc (sans journal ligne à ligne). Les déclencheurs DELETE s'exécutent ligne par ligne, y compris pour un DELETE sans condition.

TRUNCATE [TABLE] table vide aussi la table, mais en une seule opération de définition (voir TRUNCATE TABLE, chapitre 7) :

CREATE TABLE j (id INT AUTO_INCREMENT PRIMARY KEY, t VARCHAR(5));
INSERT INTO j (t) VALUES ('a'), ('b');
DELETE FROM j;                              -- OK, 2 ligne(s) affectée(s)
INSERT INTO j (t) VALUES ('c');             -- id = 3
TRUNCATE TABLE j;
INSERT INTO j (t) VALUES ('d');             -- id = 1

Clés étrangères#

Les actions ON DELETE / ON UPDATE s'appliquent (voir DDL) : CASCADE et SET NULL modifient les lignes enfants sans les compter dans les lignes affectées ; RESTRICT / NO ACTION refusent avec 1451. Les lignes enfants modifiées par cascade ne déclenchent ni déclencheur, ni horodatage ON UPDATE CURRENT_TIMESTAMP.

Les contraintes sont vérifiées immédiatement, ligne par ligne, dans l'ordre où l'instruction traite ses lignes (ordre de lecture de la table, ou ORDER BY), et non sur l'état final : une ligne parente ne peut pas quitter sa clé tant qu'une ligne encore présente la référence, même si l'instruction l'aurait retirée plus loin. Cela compte surtout pour une table qui se référence elle-même :

CREATE TABLE arbre (id INT PRIMARY KEY, parent INT, FOREIGN KEY (parent) REFERENCES arbre (id));
INSERT INTO arbre VALUES (1, NULL), (2, 1), (3, 2);
DELETE FROM arbre WHERE id >= 2;                   -- ERREUR 1451 : la ligne 3 référence encore la 2
UPDATE arbre SET id = id + 10;                     -- ERREUR 1451 : la ligne 2 référence encore la 1
DELETE FROM arbre WHERE id >= 2 ORDER BY id DESC;  -- OK, 2 ligne(s) affectée(s) : les feuilles d'abord

Une ligne modifiée voit les lignes déjà traitées avec leurs nouvelles valeurs (elle-même comprise) et les suivantes avec leurs anciennes. Les actions CASCADE et SET NULL d'une instruction sont planifiées sur l'instruction entière avant toute écriture. SET foreign_key_checks = 0 suspend contrôles et actions pour la session.

DELETE FROM clients WHERE id = 2;
-- ERREUR 1451 (23000) : Cannot delete or update a parent row: a foreign key constraint fails
--   (`d`.`commandes`, CONSTRAINT `commandes_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`))

UPDATE / DELETE multi-tables : rappel des règles#

UPDATE clients c JOIN commandes o ON o.client_id = c.id SET o.montant = 0;
-- ERREUR 1235 (42000) : This version of Miraj doesn't yet support 'multi-table UPDATE writing a joined table'
UPDATE clients c JOIN commandes o ON o.client_id = c.id SET c.solde = 0 LIMIT 1;
-- ERREUR 1221 (HY000) : Incorrect usage of UPDATE and LIMIT
DELETE o FROM commandes o JOIN clients c ON c.id = o.client_id WHERE c.id = 2 ORDER BY o.id;
-- ERREUR 1221 (HY000) : Incorrect usage of DELETE and ORDER BY
DELETE x FROM commandes o JOIN clients c ON c.id = o.client_id;
-- ERREUR 1109 (42S02) : Unknown table 'x' in MULTI DELETE

Pour écrire dans la seconde table d'un UPDATE, inverser l'ordre de la jointure (la table à modifier en premier).

Mise à jour différée#

Un UPDATE d'une seule ligne désignée par clé primaire ou UNIQUE peut être réévalué au COMMIT sur la dernière valeur validée (variable deferred_update, ON par défaut) ; deux transactions qui mettent ainsi à jour la même ligne valident alors toutes deux au lieu d'échouer avec 1213. UPDATE IGNORE n'est jamais différé. Voir Transactions et concurrence.

Valeurs tirées d'une séquence#

Une séquence (chapitre 7, « Séquences ») peut fournir la valeur d'une colonne, soit par un DEFAULT sur la colonne, soit directement dans l'instruction :

CREATE SEQUENCE seq_facture START WITH 1000;
CREATE TABLE facture (n BIGINT DEFAULT NEXTVAL(seq_facture) PRIMARY KEY, client INT);
INSERT INTO facture (client) VALUES (7), (8);                             -- n = 1000, 1001
INSERT INTO facture (n, client) VALUES (NEXT VALUE FOR seq_facture, 9);   -- n = 1002
SELECT LASTVAL(seq_facture);                                              -- 1002, dernière valeur de la session

Une valeur servie n'est jamais rendue : un ROLLBACK laisse un trou dans la numérotation. À la différence de AUTO_INCREMENT, une séquence peut alimenter plusieurs tables. INSERT, UPDATE, DELETE et TRUNCATE visant directement une séquence rendent l'erreur 4093 ; pour la repositionner, utiliser SETVAL ou ALTER SEQUENCE … RESTART. SELECT * FROM seq_facture lit son état (next_not_cached_value, increment…).

Tables versionnées et périodes applicatives#

Sur une table WITH SYSTEM VERSIONING (chapitre 12), UPDATE, DELETE, REPLACE et INSERT … ON DUPLICATE KEY UPDATE gardent l'ancienne image de la ligne avec sa date de fin ; les écritures s'utilisent exactement comme sur une table ordinaire, et un ROLLBACK défait aussi l'historique. DELETE HISTORY FROM table [BEFORE SYSTEM_TIME instant] supprime des versions terminées, sans toucher aux lignes courantes. Les colonnes row_start et row_end sont écrites par MIRAJ : toute valeur donnée par l'instruction est écrasée.

Sur une table à période applicative (PERIOD FOR), UPDATE table FOR PORTION OF période FROM a TO b SET … et DELETE FROM table FOR PORTION OF période FROM a TO b … ne modifient ou ne suppriment que la partie de chaque ligne comprise dans l'intervalle, les morceaux hors de l'intervalle étant réinsérés (chapitre 23, §23.7).

8.8 LOAD DATA INFILE et LOAD XML#

Syntaxe#

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'fichier'
    [REPLACE | IGNORE]
    INTO TABLE [base.]table
    [PARTITION (p, ...)]                                  -- édition Cluster
    [CHARACTER SET jeu]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'chaîne']
        [[OPTIONALLY] ENCLOSED BY 'caractère']
        [ESCAPED BY 'caractère']]
    [LINES [STARTING BY 'chaîne'] [TERMINATED BY 'chaîne']]
    [IGNORE n {LINES | ROWS}]
    [(colonne | @variable [, ...])]
    [SET colonne = expression | DEFAULT [, ...]]

Valeurs par défaut : champs séparés par une tabulation, lignes terminées par \n, échappement \ (\N représente NULL), sans délimiteur.

Fichier du serveur et fichier LOCAL#

Le fichier est gardé en mémoire le temps de l'instruction. Jeux de caractères : utf8mb4 (défaut), utf8mb3, latin1, ascii, binary ; une colonne binaire reçoit les octets du fichier tels quels. Délimiteur et échappement : un caractère au plus (erreur 1083). Le format à largeur fixe (séparateur et délimiteur vides) n'est pas pris en charge (1235). LOAD XML est décrit plus bas.

Comportement#

LOAD DATA se comporte comme un INSERT : mêmes privilèges (INSERT), mêmes déclencheurs, clés étrangères, colonnes générées, AUTO_INCREMENT et intégration dans la transaction (annulable par ROLLBACK). L'instruction rend le nombre de lignes chargées et, hors REPLACE, le message Records: n Deleted: 0 Skipped: n Warnings: n du paquet OK.

• IGNORE n LINES saute les n premières lignes (en-tête).
• Liste de colonnes : les colonnes absentes reçoivent leur valeur par défaut ; @variable capte un champ sans le charger (utilisable dans SET, et gardée dans la session : la variable contient le champ de la dernière ligne).
• SET col = expr affecte une colonne à partir des variables et des colonnes déjà chargées ; col = DEFAULT applique la valeur par défaut.
• Un champ \N (ou le mot NULL non délimité) vaut NULL ; un NULL entre délimiteurs ("NULL") est le texte.
• Doublons : erreur 1062 (tout annulé), IGNORE (ligne écartée, avertissement), REPLACE (ligne en conflit remplacée).
• Ligne trop courte (1261, un avertissement ou une erreur par colonne manquante) ou trop longue (1262), NULL dans une colonne NOT NULL (1048, ou valeur implicite avec 1263), champ vide dans une colonne entière (1366) : erreurs en mode strict, avertissements avec IGNORE, avec LOCAL ou hors mode strict. Les colonnes sans champ d'une ligne trop courte reçoivent leur valeur par défaut quand elle est constante, sinon la valeur implicite du type.
• Une instruction LOAD DATA est refusée dans une routine (1314).

Exemple exécutable#

Fichier clients.csv du dossier secure_file_priv (fins de ligne CRLF) :

nom,solde
"Benali, Amine",31
"Le ""Grand""",12.5
Hicham,\N

CREATE TABLE cl (id INT AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(30), solde DECIMAL(8,2) DEFAULT 0);

LOAD DATA INFILE 'clients.csv' INTO TABLE cl
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n'
    IGNORE 1 LINES
    (nom, solde);
-- OK, 3 ligne(s) affectée(s)

SELECT * FROM cl;

+----+---------------+-------+
| id | nom           | solde |
+----+---------------+-------+
|  1 | Benali, Amine | 31.00 |
|  2 | Le "Grand"    | 12.50 |
|  3 | Hicham        |  NULL |
+----+---------------+-------+

Avec variables et SET (fichier p.txt, champs séparés par ;, décimales à virgule) :

1;amine;3,50;x
2;sara;12,00;y

CREATE TABLE p (id INT PRIMARY KEY, nom VARCHAR(20), prix DECIMAL(8,2), note VARCHAR(10) DEFAULT 'n/a');

LOAD DATA INFILE 'p.txt' INTO TABLE p FIELDS TERMINATED BY ';'
    (id, @nom, @prix, @ignore)
    SET nom = UPPER(@nom), prix = REPLACE(@prix, ',', '.'), note = DEFAULT;
-- OK, 2 ligne(s) affectée(s)

SELECT * FROM p;

+----+-------+-------+------+
| id | nom   | prix  | note |
+----+-------+-------+------+
|  1 | AMINE |  3.50 | n/a  |
|  2 | SARA  | 12.00 | n/a  |
+----+-------+-------+------+

SELECT @ignore rend ensuite y, le champ de la dernière ligne. Recharger le même fichier :

LOAD DATA INFILE 'p.txt' INTO TABLE p FIELDS TERMINATED BY ';' (id, @nom, @prix, @ignore);
-- ERREUR 1062 (23000) : Duplicate entry '1' for key 'PRIMARY'     (rien n'est chargé)
LOAD DATA INFILE 'p.txt' IGNORE INTO TABLE p FIELDS TERMINATED BY ';' (id, @nom, @prix, @ignore);
-- deux avertissements 1062, lignes existantes inchangées
LOAD DATA INFILE 'p.txt' REPLACE INTO TABLE p FIELDS TERMINATED BY ';' (id, @nom, @prix, @ignore) SET nom = @nom;
-- OK, 4 ligne(s) affectée(s)      (2 supprimées + 2 insérées ; prix NULL, note 'n/a')

Sans dossier secure_file_priv configuré, ou fichier absent :

ERREUR 1290 (HY000) : The server is running with the --secure-file-priv option so it cannot execute this statement
ERREUR 29 (HY000) : File 'absent.txt' not found (Errcode: 2 "No such file or directory")

LOAD XML#

LOAD XML [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'fichier'
    [REPLACE | IGNORE]
    INTO TABLE [base.]table [PARTITION (p, ...)]
    [CHARACTER SET jeu]
    [ROWS IDENTIFIED BY '<balise>']
    [IGNORE n {LINES | ROWS}]
    [(colonne | @variable, ...)]
    [SET colonne = expression | DEFAULT, ...]

Chaque élément <balise> du document (<row> par défaut, à n'importe quelle profondeur) est une ligne. Ses valeurs sont nommées et peuvent prendre trois formes, mélangeables dans une même ligne :

• Chaque destination (toutes les colonnes de la table, ou la liste (colonne | @variable, ...)) prend la valeur de même nom (sans tenir compte de la casse) ; une valeur sans destination est ignorée ; une colonne sans valeur dans la ligne garde sa valeur par défaut (pas de 1261/1262).
• xsi:nil="true" vaut NULL ; un élément vide (<nom/>, <nom></nom>) vaut la chaîne vide.
• IGNORE n ROWS (ou LINES) saute les n premières lignes XML.
• Entités < > & " ', références é / é, sections <![CDATA[...]]>, commentaires, prologue <?xml ...?> et <!DOCTYPE ...> (ignoré) sont compris. Le fichier est lu dans le CHARACTER SET de l'instruction (UTF-8 par défaut), l'encodage du prologue est ignoré.
• Fichier du serveur ou LOCAL, droits, REPLACE / IGNORE, clause SET, conversions, déclencheurs, avertissements et message Records: ... : comme LOAD DATA. Refusé dans une routine (1314).
• Document mal formé (balise non fermée ou croisée, entité inconnue, attribut sans guillemets...) : erreur 9085 avec le numéro de ligne, rien n'est chargé.

CREATE TABLE px (id INT PRIMARY KEY, nom VARCHAR(20), ville VARCHAR(20) DEFAULT 'Alger');

-- p.xml :
-- <resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
--   <row id="1" nom="Amine"/>
--   <row><id>2</id><nom>Sara</nom><ville>Oran</ville></row>
--   <row><field name="id">3</field><field name="nom" xsi:nil="true" /></row>
-- </resultset>
LOAD XML INFILE 'p.xml' INTO TABLE px;
-- OK, 3 ligne(s) affectée(s)   Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

SELECT * FROM px;

+----+-------+-------+
| id | nom   | ville |
+----+-------+-------+
|  1 | Amine | Alger |
|  2 | Sara  | Oran  |
|  3 | NULL  | Alger |
+----+-------+-------+

L'export inverse, SELECT ... INTO OUTFILE 'fichier' [FIELDS ...] [LINES ...] et SELECT ... INTO DUMPFILE 'fichier', écrit dans le même dossier ; un fichier existant n'est jamais remplacé (erreur 1086). Voir Requêtes SELECT, §9.23.

8.9 RETURNING et autres syntaxes non prises en charge#

INSERT, REPLACE et DELETE ... RETURNING#

INSERT [IGNORE] INTO table ... [ON DUPLICATE KEY UPDATE ...] RETURNING élément [, élément ...]
REPLACE INTO table ... RETURNING élément [, élément ...]
DELETE FROM table [WHERE ...] [ORDER BY ...] [LIMIT n] RETURNING élément [, élément ...]

L'écriture rend, sous forme de jeu de résultats, une ligne par ligne écrite, dans l'ordre des écritures. Toutes les formes de source sont admises (VALUES, SET, SELECT). Un élément est celui d'une liste de SELECT : *, table.*, colonne, expression, appel de fonction, avec ou sans alias (AS facultatif) ; une colonne sans alias est nommée par le texte de son expression.

Valeurs rendues :

• INSERT / REPLACE : valeurs effectivement écrites — valeur d'AUTO_INCREMENT attribuée, DEFAULT, colonnes générées, changements d'un déclencheur BEFORE INSERT ;
• ON DUPLICATE KEY UPDATE : la ligne insérée, ou la ligne en conflit telle qu'après sa mise à jour (rendue aussi quand la mise à jour ne change rien) ;
• INSERT IGNORE : seules les lignes réellement insérées (une ligne écartée par un doublon n'est pas rendue) ;
• DELETE : chaque ligne supprimée, valeurs d'avant la suppression (WHERE, ORDER BY et LIMIT choisissent les lignes comme d'ordinaire).

Exemples (table r (id INT AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(20), solde DECIMAL(8,2) DEFAULT 0), vide au départ) :

INSERT INTO r (nom) VALUES ('Ali'), ('Lina') RETURNING id, nom, solde;

+----+------+-------+
| id | nom  | solde |
+----+------+-------+
|  1 | Ali  |  0.00 |
|  2 | Lina |  0.00 |
+----+------+-------+

INSERT INTO r (id, nom) VALUES (1, 'Ali B.')
ON DUPLICATE KEY UPDATE nom = VALUES(nom)
RETURNING id, UPPER(nom) AS nom_maj;                 -- 1 | ALI B.

INSERT IGNORE INTO r (id, nom) VALUES (1, 'x'), (3, 'Sami') RETURNING id, nom;   -- 3 | Sami

DELETE FROM r WHERE id >= 2 ORDER BY id LIMIT 10 RETURNING id, nom;

+----+------+
| id | nom  |
+----+------+
|  2 | Lina |
|  3 | Sami |
+----+------+

Le jeu de résultats est transmis comme celui d'un SELECT, en protocole texte comme en requête préparée (COM_STMT_PREPARE en décrit les colonnes, COM_STMT_EXECUTE rend les lignes en protocole binaire) ; dans une procédure stockée, il remonte au client comme celui d'un SELECT du corps. LAST_INSERT_ID() et ROW_COUNT() sont tenus à jour comme sans la clause. Dans une transaction, un ROLLBACK efface les lignes déjà rendues.

Droits : en plus du droit d'écrire, SELECT sur la table, ou sur chacune des colonnes rendues (toutes celles de la table pour *) ; à défaut, 1142 ou 1143.

Refusé :

Non pris en charge par MIRAJ : au travers d'une vue et sur une table partitionnée de l'édition Cluster (1235). Une table à versionnement système est admise (DELETE rend la ligne retirée, sa version historique est gardée) ; une séquence refuse DELETE comme sans la clause (4093).

RETURNING est un mot réservé : une table, une colonne ou un alias de ce nom s'écrit entre backquotes (`returning`).

Autres syntaxes non prises en charge#

Exemple :

UPDATE t SET n = 0 WHERE id > 40 RETURNING id;
-- ERREUR 1064 (42000) : You have an error in your SQL syntax; check the manual that corresponds
--   to your Miraj server version for the right syntax to use near 'RETURNING id' ...

8.10 Récapitulatif des erreurs DML#