6. Langage DML

Ce chapitre décrit les instructions de manipulation des données (DML) : INSERT, UPDATE, DELETE, 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 de ce chapitre :

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

6.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 ...]]

REPLACE INTO table [(colonne, ...)]
    VALUES (valeur, ...) [, (valeur, ...) ...]
    | requête_select

Insertion d'une ligne ou de plusieurs lignes#

INSERT INTO clients (nom, ville) VALUES ('Alice', 'Oran');

INSERT INTO clients (nom, ville, solde) VALUES
    ('Bob', 'Alger', 50.00),
    ('Chloé', 'Oran', 0),
    ('David', 'Constantine', NULL);

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 ligne peut être vide (VALUES ()) quand la table n'a que des colonnes à valeur par défaut ou nullables. Une valeur peut être DEFAULT, qui vaut explicitement la valeur par défaut de la colonne :

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

INSERT ... SELECT#

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

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;

INSERT IGNORE transforme en avertissement les erreurs qui, sinon, feraient échouer la ligne (valeur trop longue, NOT NULL violé, doublon de clé) : la ligne fautive est sautée, les autres sont insérées.

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, ou NULL explicite) :

INSERT INTO clients (nom) VALUES ('Grace');
SELECT LAST_INSERT_ID();   -- valeur attribuée à la ligne qui vient d'être insérée

Donner explicitement une valeur plus grande que le compteur courant le fait avancer d'autant ; les insertions suivantes repartent au-delà de cette valeur.

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.

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.

REPLACE#

REPLACE INTO insère une ligne comme INSERT, mais si elle entre en conflit avec une ligne existante sur une 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) :

REPLACE INTO clients (id, nom, ville) VALUES (1, 'Alice', 'Alger');

REPLACE accepte aussi une source SELECT :

REPLACE INTO clients (id, nom) SELECT id + 100, nom FROM clients WHERE ville = 'Oran';

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 nouveau déclenchement DELETE/INSERT pour elle) et seules les colonnes citées dans ON DUPLICATE KEY UPDATE sont modifiées.

INSERT INTO clients (id, nom, solde) VALUES (1, 'Alice', 100)
ON DUPLICATE KEY UPDATE solde = solde + VALUES(solde);

VALUES(colonne) désigne, dans la clause ON DUPLICATE KEY UPDATE, la valeur que la ligne en conflit aurait reçue à l'insertion. Une ligne insérée avec VALUES ... AS alias peut aussi être désignée par son alias :

INSERT INTO clients (id, nom, solde) VALUES (1, 'Alice', 100) AS nouvelle
ON DUPLICATE KEY UPDATE solde = solde + nouvelle.solde;

Erreurs typiques#

6.2 UPDATE#

Synopsis#

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

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

UPDATE mono-table#

UPDATE clients SET solde = solde + 20 WHERE ville = 'Oran';

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 = 0 WHERE solde < 0 ORDER BY id LIMIT 1;

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

UPDATE accepte les mêmes types de jointure qu'un SELECT (voir chapitre 7) : JOIN / INNER JOIN, CROSS JOIN, 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 = 42;

Avec USING, quand les deux tables ont une colonne de même nom :

UPDATE clients c
LEFT JOIN commandes o USING (id)
SET c.solde = c.solde + 1
WHERE o.id IS NULL;   -- clients sans commande n° 1..N (selon la jointure)

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

UPDATE clients c
CROSS 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;

Restrictions propres à l'UPDATE multi-tables (vérifiées par le moteur) :

• SET ne peut affecter 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).
• ORDER BY et LIMIT ne sont pas admis dès qu'il y a plusieurs tables.
• La table modifiée ne peut pas être une vue non modifiable.

Erreurs typiques#

6.3 DELETE#

Synopsis#

DELETE FROM table [WHERE condition] [ORDER BY ...] [LIMIT n]

DELETE table1 [, table2 ...] FROM table1 [JOIN ...] [WHERE condition]
DELETE FROM table1 USING table1 [JOIN ...] [WHERE condition]

DELETE simple#

DELETE FROM commandes WHERE montant IS NULL;
DELETE FROM clients WHERE solde = 0 ORDER BY id LIMIT 1;
DELETE FROM commandes;   -- vide la table (une ligne à la fois, contrairement à TRUNCATE)

DELETE multi-tables#

Deux formes équivalentes suppriment des lignes d'une jointure, en se servant des autres tables jointes uniquement pour filtrer. Seule la première table de la liste de lecture peut être vidée ; nommer comme cible une table jointe qui n'est pas la première rend l'erreur 1235 (multi-table DELETE of a joined table) :

-- table(s) à vider nommées avant FROM
DELETE c FROM clients c
JOIN commandes o ON o.client_id = c.id
WHERE o.montant < 0;

-- table(s) à vider nommées après USING
DELETE FROM c USING clients c
JOIN commandes o ON o.client_id = c.id
WHERE o.montant < 0;

ORDER BY et LIMIT ne sont pas admis dans la forme multi-tables. Une table nommée devant FROM (ou après USING) qui ne fait pas partie de la jointure est une erreur (1109, table inconnue) ; une table de la jointure qui n'est pas listée comme cible n'a pas ses lignes supprimées. Les déclencheurs DELETE et les clés étrangères s'appliquent aux lignes supprimées.

Erreurs typiques#

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

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

6.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 3;      -- 'Chloé'
DEALLOCATE PREPARE lire;
EXECUTE lire USING 1;      -- erreur 1243 : instruction préparée inconnue

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, 1;
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#

6.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, 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

• 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.
• 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 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), colonne NOT NULL sans DEFAULT omise (1364), date invalide et NULL dans une colonne date NOT NULL (pas de « date zéro »). 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.

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

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 d'un INSERT sans IGNORE sont contrôlées après la dernière ligne (une ligne peut donc référencer une ligne insérée plus loin).

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 :

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 id, nom, ville, solde FROM clients WHERE id = 1;   -- solde revenu à 0.00

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 des lignes insérées sur l'état final de l'instruction. 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) n'existe que dans cette clause (ailleurs : « fonction inconnue »). L'alias de ligne (VALUES (...) AS nouvelle ou AS nouvelle (a, b, c)) est la forme actuelle 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.
• Une sous-requête dans la clause est lue une fois avant toute écriture ; corrélée à la ligne : erreur 1235.
• 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 ; EXPLAIN INSERT ... ON DUPLICATE KEY UPDATE : 1235.
• 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 vérifié (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 clients (id, nom, solde) VALUES (1, 'Alice', 100) ON DUPLICATE KEY UPDATE solde = solde;
-- OK, 0 ligne(s) affectée(s)      (rien ne change)

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.

6.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 (comportement CLIENT_FOUND_ROWS) :

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 et options acceptés#

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

LOW_PRIORITY et QUICK sont sans effet. UPDATE IGNORE et DELETE IGNORE sont acceptés mais sans effet : une erreur (doublon 1062, clé étrangère 1451...) reste une erreur dès la première ligne fautive, contrairement à INSERT IGNORE.

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

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.

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.

DELETE sans condition#

DELETE FROM table sans WHERE, ORDER BY ni LIMIT supprime toutes les lignes et conserve le compteur AUTO_INCREMENT (contrairement à TRUNCATE, qui le remet à 1). 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.

DELETE FROM u;               -- OK, 3 ligne(s) affectée(s)

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.

DELETE FROM clients WHERE id = 1;
-- 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#

Exemples vérifiés (DELETE multi-tables, forme USING et forme abrégée) :

DELETE o FROM commandes o JOIN clients c ON c.id = o.client_id WHERE c.ville = 'Alger';
-- OK, 3 ligne(s) affectée(s)          -- supprime les commandes, pas les clients
DELETE FROM o USING commandes o JOIN clients c ON c.id = o.client_id WHERE c.ville = 'Oran';
DELETE c FROM commandes o JOIN clients c ON c.id = o.client_id WHERE c.ville = 'Alger';
-- ERREUR 1235 (42000) : This version of Miraj doesn't yet support 'multi-table DELETE of a joined table'
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

Pour écrire dans la seconde table, 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, active par défaut) ; deux transactions qui mettent ainsi à jour la même ligne valident alors toutes deux au lieu d'échouer avec 1213. Voir Transactions et concurrence.

6.8 LOAD DATA INFILE#

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) et LOAD XML ne sont pas pris en charge (1235).

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 (pas de message Records: ... Deleted: ... Skipped: ...).

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

LOAD DATA INFILE 'p.txt' INTO TABLE p FIELDS TERMINATED BY ';'
    (id, @nom, @prix, @ignore)
    SET nom = UPPER(@nom), prix = REPLACE(@prix, ',', '.'), note = DEFAULT;

Sans dossier secure_file_priv configuré :

ERREUR 1290 (HY000) : The server is running with the --secure-file-priv option so it cannot execute this statement

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.

6.9 RETURNING et autres syntaxes non prises en charge#

Exemple :

DELETE FROM t 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' ...

6.10 Récapitulatif des erreurs DML#