6. DML Language

This chapter describes the data manipulation statements (DML): INSERT, UPDATE, DELETE, parameterized queries and prepared statements (PREPARE / EXECUTE / DEALLOCATE PREPARE).

The examples rely on two tables used throughout this chapter:

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

Inserting One Row or Several Rows#

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

The SET form assigns columns one by one, with no separate column list or value list:

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

A row can be empty (VALUES ()) when the table has only columns with default values or nullable columns. A value can be DEFAULT, which explicitly means the column's default value:

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

INSERT ... SELECT#

The inserted rows come from the result of a query rather than from a VALUES list:

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 turns into warnings the errors that would otherwise make the row fail (value too long, NOT NULL violated, duplicate key): the offending row is skipped, the others are inserted.

AUTO_INCREMENT#

An AUTO_INCREMENT column automatically receives the next value when the insertion does not give it one (column absent from the list, or explicit NULL):

INSERT INTO clients (nom) VALUES ('Grace');
SELECT LAST_INSERT_ID();   -- value assigned to the row that was just inserted

Explicitly giving a value greater than the current counter advances it accordingly; subsequent insertions restart beyond that value.

DEFAULT and Generated Columns#

A column omitted from the column list receives its default value (the definition's DEFAULT, or NULL if the column is nullable without a DEFAULT). A DEFAULT can be an expression, evaluated row by row and able to reference the other columns of the same row.

A generated column (GENERATED ALWAYS AS (expr), VIRTUAL or STORED/PERSISTENT) is never populated by the insertion: an explicit value given for a generated column is ignored (it is not rejected), and the column takes the value of its expression.

REPLACE#

REPLACE INTO inserts a row like INSERT, but if it conflicts with an existing row on a primary key or a UNIQUE constraint, the conflicting row is first deleted before the insertion (DELETE and INSERT triggers are executed as for these statements):

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

REPLACE also accepts a SELECT source:

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

ON DUPLICATE KEY UPDATE#

An alternative to REPLACE that updates the conflicting row instead of replacing it: the existing row keeps its identity (no deletion, no new DELETE/INSERT trigger firing for it) and only the columns named in ON DUPLICATE KEY UPDATE are modified.

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

VALUES(column) designates, in the ON DUPLICATE KEY UPDATE clause, the value that the conflicting row would have received on insertion. A row inserted with VALUES ... AS alias can also be referenced by its alias:

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

Typical Errors#

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]

Single-Table UPDATE#

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

ORDER BY and LIMIT, on a single-table UPDATE, choose which of the rows selected by WHERE are actually modified:

UPDATE clients SET solde = 0 WHERE solde < 0 ORDER BY id LIMIT 1;

Multi-Table UPDATE (Update with a Join)#

UPDATE accepts the same join types as a SELECT (see chapter 7): JOIN / INNER JOIN, CROSS JOIN, LEFT [OUTER] JOIN, RIGHT [OUTER] JOIN, with ON or USING, aliases, and a derived table (subquery) as the joined table. Only one table is actually modified by the statement: the first table of the join (the one following UPDATE). The other tables of the join serve only to compute the new values and to filter rows.

UPDATE clients c
JOIN commandes o ON o.client_id = c.id
SET c.solde = c.solde - o.montant
WHERE o.id = 42;

With USING, when both tables have a column of the same name:

UPDATE clients c
LEFT JOIN commandes o USING (id)
SET c.solde = c.solde + 1
WHERE o.id IS NULL;   -- customers without order no. 1..N (depending on the join)

With a derived table as the source of the values:

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 specific to multi-table UPDATE (checked by the engine):

• SET can only assign columns of the first table (the one following UPDATE); assigning a column of another table of the join, or of several tables, fails with error 1235 (multi-table UPDATE writing a joined table / writing several tables).
• ORDER BY and LIMIT are not allowed as soon as there are several tables.
• The modified table cannot be a non-updatable view.

Typical Errors#

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]

Simple DELETE#

DELETE FROM commandes WHERE montant IS NULL;
DELETE FROM clients WHERE solde = 0 ORDER BY id LIMIT 1;
DELETE FROM commandes;   -- empties the table (one row at a time, unlike TRUNCATE)

Multi-Table DELETE#

Two equivalent forms delete rows from a join, using the other joined tables only for filtering. Only the first table of the read list can be emptied; naming as target a joined table that is not the first one returns error 1235 (multi-table DELETE of a joined table):

-- table(s) to empty named before FROM
DELETE c FROM clients c
JOIN commandes o ON o.client_id = c.id
WHERE o.montant < 0;

-- table(s) to empty named after USING
DELETE FROM c USING clients c
JOIN commandes o ON o.client_id = c.id
WHERE o.montant < 0;

ORDER BY and LIMIT are not allowed in the multi-table form. A table named before FROM (or after USING) that is not part of the join is an error (1109, unknown table); a table of the join that is not listed as a target does not have its rows deleted. DELETE triggers and foreign keys apply to the deleted rows.

Typical Errors#

6.4 Parameterized Queries#

Two parameter styles are accepted in the SQL text, in place of a literal value:

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

The two styles are not mixed within a single statement. Parameters can appear anywhere an expression is expected: WHERE, SET, VALUES, LIMIT, etc. This is the recommended form for any value that comes from the user or the application (via the API, a network connector or miraj-cli), rather than building the SQL text by concatenation: bound values are never interpreted as SQL, which prevents injections and avoids manually reformatting literals (dates, strings to escape, numbers).

EXECUTE ... USING (below) binds values to the ? parameters of a prepared statement, in order.

6.5 PREPARE, EXECUTE, DEALLOCATE PREPARE#

A prepared statement separates the parsing of the SQL text (done once by PREPARE) from its execution (done as many times as needed by EXECUTE), with ? parameters bound at each execution. This is the dynamic form of SQL, useful when the statement text is built by code (variable table name, generated query) rather than written once and for all.

Synopsis#

PREPARE nom FROM expression

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

{DEALLOCATE | DROP} PREPARE nom

• The PREPARE expression is the text of the statement to prepare: a string literal, a user variable (@sql), or any expression that produces a string, evaluated when PREPARE runs. The text must contain exactly one statement (a trailing ; is tolerated).
• nom is case-insensitive and designates the prepared statement for the subsequent EXECUTE and DEALLOCATE PREPARE, within the same session.
• Preparing again under a name already in use silently replaces the previous prepared statement.
• EXECUTE nom USING ... binds the USING values, in order, to the ? parameters of the prepared text; their number must exactly match that of the parameters.
• EXECUTE IMMEDIATE expression prepares, executes then discards a statement in a single step, without giving it a name.
• DEALLOCATE PREPARE nom (or DROP PREPARE nom, synonyms) releases the prepared statement; subsequent EXECUTE under that name fail.

Lifecycle#

1. PREPARE parses the text and stores it under a name, for the current session.
2. EXECUTE (one or more times) binds the parameters and executes the already parsed statement.
3. DEALLOCATE PREPARE releases the preparation; it is also implicitly lost at the end of the session.

Complete Example#

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;      -- error 1243: unknown prepared statement

Dynamically built text (variable table name), a typical use case in a 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, for a single-use statement:

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

Typical Errors#

6.6 Additions: INSERT, REPLACE, ON DUPLICATE KEY UPDATE#

The examples in this section and the following ones reuse the clients and commandes tables from the start of the chapter, with in addition a generated column nom_maj VARCHAR(50) AS (UPPER(nom)) STORED in clients and FOREIGN KEY (client_id) REFERENCES clients (id) in commandes.

Full Recognized Syntax#

{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 only

• INTO is optional. VALUE is a synonym of VALUES. () as a column list (INSERT INTO t () VALUES ()) inserts a row made entirely of defaults.
• LOW_PRIORITY, DELAYED and HIGH_PRIORITY are accepted and have no effect.
• IGNORE does not exist for REPLACE (REPLACE IGNORE: error 1064), nor does REPLACE ... ON DUPLICATE KEY UPDATE.
• The target table cannot carry an alias (INSERT INTO clients AS c ...: 1064).
• The SET colonne = expr form does not accept the DEFAULT keyword as a value (INSERT INTO clients SET solde = DEFAULT: error 1064); omitting the column has the same effect. DEFAULT remains allowed in VALUES (...) and in UPDATE ... SET col = DEFAULT.
• INSERT INTO t PARTITION (p) ...: Cluster edition only (9002 elsewhere).

Example: Insertion, Generated Column, Default Value#

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

INSERT INTO clients (nom, nom_maj) VALUES ('Dan', 'IGNORE');   -- value of nom_maj ignored
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');
-- ERROR 1136 (21S01): Column count doesn't match value count at row 1
INSERT INTO clients (nom, inconnue) VALUES ('a', 'b');
-- ERROR 1054 (42S22): Unknown column 'inconnue' in 'field list'
INSERT INTO clients (nom) VALUES (NULL);
-- ERROR 1048 (23000): Column 'nom' cannot be null
INSERT INTO clients (ville) VALUES ('x');
-- ERROR 1364 (HY000): Field 'nom' doesn't have a default value

INSERT IGNORE: Exactly What Is Turned into a Warning#

With VALUES, SET and SELECT, IGNORE converts into a warning (row skipped or value corrected):

The following remain errors even with IGNORE: number of values (1136), unknown column (1054), unknown table (1146), NOT NULL column without DEFAULT omitted (1364), invalid date and NULL in a NOT NULL date column (no "zero date"). A skipped row does not consume an AUTO_INCREMENT number. Foreign keys are checked row by row, in order: a row that references a row inserted later in the same statement is skipped.

INSERT IGNORE INTO commandes (client_id, montant) VALUES (99, 5), (3, 7);
-- OK, 1 row(s) affected
-- Warning: Cannot add or update a child row: a foreign key constraint fails (...)

Without IGNORE, a multi-row INSERT statement is all or nothing: the first error cancels all the rows of the statement, including the batches already produced by an INSERT ... SELECT. The foreign keys of an INSERT without IGNORE are checked after the last row (a row can therefore reference a row inserted later).

INSERT ... SELECT#

• The query can be written with or without parentheses, and can start with WITH.
• The result is inserted in batches of 4,096 rows; if the target table is also read by the query (including in a subquery or a derived table), the query is first read in full before the first insertion:

  INSERT INTO clients (nom) SELECT nom FROM clients;   -- OK: doubles the number of rows

• Source tables are locked for reading, the target table is locked for writing during the whole read.
• IGNORE, ON DUPLICATE KEY UPDATE and REPLACE work with SELECT (ON DUPLICATE KEY UPDATE: no row alias AS alias after a SELECT).

REPLACE: Precise Behavior#

For each row, any existing row in conflict on the primary key or on a UNIQUE key (one per key) is deleted, then the new row is inserted — never an in-place update. Columns not named therefore take their default value:

REPLACE INTO clients (id, nom, ville) VALUES (1, 'Alice', 'Alger');
-- OK, 2 row(s) affected      (1 deleted + 1 inserted)
SELECT id, nom, ville, solde FROM clients WHERE id = 1;   -- solde back to 0.00

Affected rows = deleted rows + inserted rows (child rows deleted or modified by ON DELETE do not count). Under RESTRICT / NO ACTION, a referenced parent row returns 1451 and the whole statement is cancelled. Conversions are checked before any write; the foreign keys of the inserted rows against the final state of the statement. REPLACE always runs in a transaction. Triggers: BEFORE INSERT, then BEFORE / AFTER DELETE around each replaced row, then AFTER INSERT.

ON DUPLICATE KEY UPDATE: Precise Behavior#

• The first conflicting row is updated: primary key first, then UNIQUE keys in definition order (rows written earlier by the same statement included). Assignments are evaluated left to right.
• Affected rows: 1 per inserted row, 2 per existing row actually modified, 0 per existing row left as is; the total is the sum.
• VALUES(col) exists only in this clause (elsewhere: "unknown function"). The row alias (VALUES (...) AS nouvelle or AS nouvelle (a, b, c)) is the recommended current form.
• An updated row does not consume an AUTO_INCREMENT value; LAST_INSERT_ID() returns the first generated value, otherwise the AUTO_INCREMENT value of the last conflicting row.
• A subquery in the clause is read once before any write; correlated to the row: error 1235.
• With IGNORE, errors 1062, 1452, 1451 and 1048 of the update part become warnings and the row is left unchanged. ON DUPLICATE KEY UPDATE always runs in a transaction; EXPLAIN INSERT ... ON DUPLICATE KEY UPDATE: 1235.
• ON UPDATE CURRENT_TIMESTAMP columns are timestamped as in an UPDATE; STORED generated columns and CHECK constraints are recomputed / checked on the modified row.

Verified example (table t2 (a INT PRIMARY KEY, b INT) containing (1,1) and (2,2)):

INSERT INTO t2 VALUES (1,10),(3,30),(2,20) ON DUPLICATE KEY UPDATE b = VALUES(b);
-- OK, 5 row(s) affected      (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 row(s) affected      (nothing changes)

AUTO_INCREMENT and LAST_INSERT_ID()#

On a multi-row INSERT, LAST_INSERT_ID() returns the first value generated by the statement. A row skipped by IGNORE, or updated by ON DUPLICATE KEY UPDATE, does not consume a number.

6.7 Additions: UPDATE and DELETE#

Affected Rows#

The number of rows affected by an UPDATE is that of the rows matched by the WHERE, whether they change or not (CLIENT_FOUND_ROWS behavior):

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 row(s) affected
UPDATE u SET v = 0 ORDER BY id DESC LIMIT 1;    -- OK, 1 row(s) affected

Accepted Modifiers and Options#

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

LOW_PRIORITY and QUICK have no effect. UPDATE IGNORE and DELETE IGNORE are accepted but have no effect: an error (duplicate 1062, foreign key 1451...) remains an error from the first faulty row, unlike INSERT IGNORE.

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

SET: Expressions, DEFAULT, Subqueries#

UPDATE clients SET solde = DEFAULT WHERE id = 2;            -- column's default value
UPDATE u SET v = (SELECT COUNT(*) FROM u);                  -- subquery, read before writing
DELETE FROM clients WHERE id IN (SELECT id FROM clients WHERE solde < 0);

The values and target rows are computed before the first write: a subquery that reads the modified table sees the state from before the statement. The ORDER BY of a single-table UPDATE or DELETE can contain subqueries.

Generated columns: never assignable (explicit value ignored, except DEFAULT); STORED columns recomputed; ON UPDATE CURRENT_TIMESTAMP applied when the row actually changes. CHECK constraints are checked on the complete row (error 4025). BEFORE / AFTER UPDATE triggers run for all matched rows, even unchanged ones.

DELETE Without a Condition#

DELETE FROM table without WHERE, ORDER BY or LIMIT deletes all rows and keeps the AUTO_INCREMENT counter (unlike TRUNCATE, which resets it to 1). Outside a transaction, with no trigger or foreign key involved, it is executed as a single block (without a row-by-row log). DELETE triggers run row by row, including for an unconditional DELETE.

DELETE FROM u;               -- OK, 3 row(s) affected

Foreign Keys#

The ON DELETE / ON UPDATE actions apply (see DDL): CASCADE and SET NULL modify the child rows without counting them in the affected rows; RESTRICT / NO ACTION refuse with 1451. Child rows modified by cascade fire neither triggers nor the ON UPDATE CURRENT_TIMESTAMP timestamp.

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

Multi-Table UPDATE / DELETE: Rules Recap#

Verified examples (multi-table DELETE, USING form and short form):

DELETE o FROM commandes o JOIN clients c ON c.id = o.client_id WHERE c.ville = 'Alger';
-- OK, 3 row(s) affected          -- deletes the orders, not the customers
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';
-- ERROR 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;
-- ERROR 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;
-- ERROR 1221 (HY000): Incorrect usage of UPDATE and LIMIT

To write to the second table, reverse the order of the join (the table to modify first).

Deferred Update#

An UPDATE of a single row designated by primary key or UNIQUE can be re-evaluated at COMMIT on the last committed value (deferred_update variable, enabled by default); two transactions that update the same row this way then both commit instead of failing with 1213. See Transactions and Concurrency.

6.8 LOAD DATA INFILE#

Syntax#

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file'
    [REPLACE | IGNORE]
    INTO TABLE [base.]table
    [PARTITION (p, ...)]                                  -- Cluster edition
    [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 [, ...]]

Default values: fields separated by a tab, lines terminated by \n, escape \ (\N represents NULL), no enclosing character.

Server File and LOCAL File#

The file is kept in memory for the duration of the statement. Character sets: utf8mb4 (default), utf8mb3, latin1, ascii, binary; a binary column receives the file's bytes as is. Delimiter and escape: at most one character (error 1083). The fixed-width format (empty separator and delimiter) and LOAD XML are not supported (1235).

Behavior#

LOAD DATA behaves like an INSERT: same privileges (INSERT), same triggers, foreign keys, generated columns, AUTO_INCREMENT and integration into the transaction (can be rolled back by ROLLBACK). The statement returns the number of rows loaded (no Records: ... Deleted: ... Skipped: ... message).

• IGNORE n LINES skips the first n lines (header).
• Column list: absent columns receive their default value; @variable captures a field without loading it (usable in SET, and kept in the session: the variable holds the field from the last row).
• SET col = expr assigns a column from the variables and the columns already loaded; col = DEFAULT applies the default value.
• A \N field (or the unenclosed word NULL) is NULL; a NULL between delimiters ("NULL") is text.
• Duplicates: error 1062 (everything cancelled), IGNORE (row skipped, warning), REPLACE (conflicting row replaced).
• Row too short (1261) or too long (1262), NULL in a NOT NULL column (1048, or implicit value with 1263), empty field in an integer column (1366): errors in strict mode, warnings with IGNORE, with LOCAL or outside strict mode.
• A LOAD DATA statement is refused in a routine (1314).

Runnable Example#

File clients.csv in the secure_file_priv folder (CRLF line endings):

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 row(s) affected

SELECT * FROM cl;

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

With variables and SET (file p.txt, fields separated by ;):

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

Without a configured secure_file_priv folder:

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

The reverse export, SELECT ... INTO OUTFILE 'file' [FIELDS ...] [LINES ...] and SELECT ... INTO DUMPFILE 'file', writes to the same folder; an existing file is never replaced (error 1086). See SELECT Queries.

6.9 RETURNING and Other Unsupported Syntaxes#

Example:

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 DML Error Recap#