MySQL => INSÉRER

Syntaxe

INSERT [LOW_PRIORITY | RETARDÉ | HIGH_PRIORITY] [IGNORE] [INTO] nom_de_table [PARTITION (nom_partition, ...)] [(nom_colonne, ...)] {VALUES | VALUE} ({expr | DEFAULT}, ...), (...), ... [ON DUPLICATE KEY UPDATE nom_colonne = expr [, nom_colonne = expr] ...]
INSERT [LOW_PRIORITY | RETARDÉ | HIGH_PRIORITY] [IGNORE] [INTO] nom_de_table [PARTITION (nom_partition, ...)] SET nom_col = {expr | DEFAULT}, ... [ON DUPLICATE KEY UPDATE nom_colonne = expr [, nom_colonne = expr] ...]
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (nom_partition, ...)] [(nom_colonne, ...)] SELECT ... [ON DUPLICATE KEY UPDATE nom_colonne = expr [, nom_colonne = expr] ...]
Une expression expr peut faire référence à n'importe quelle colonne définie précédemment dans une liste de valeurs. Par exemple, vous pouvez le faire car la valeur de col2 fait référence à col1, qui a déjà été affecté:
INSERT INTO tbl_name (col1, col2) VALUES (15, col1 * 2);
Les instructions INSERT qui utilisent la syntaxe VALUES peuvent insérer plusieurs lignes. Pour ce faire, incluez plusieurs listes de valeurs de colonne, chacune entre parenthèses et séparées par des virgules. Exemple:
INSERT INTO tbl_name (a, b, c) VALUES (1,2,3), (4,5,6), (7,8,9);
La liste de valeurs pour chaque ligne doit figurer entre parenthèses. L'instruction suivante est illégale car le nombre de valeurs dans la liste ne correspond pas au nombre de noms de colonnes:
INSERT INTO nom_de_table (a, b, c) VALUES (1,2,3,4,5,6,7,8,9);
INSERT ... SELECT Syntaxe
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (nom_partition, ...)] [(nom_colonne, ...)] SELECT ... [ON DUPLICATE KEY UPDATE nom_colonne = expr, ...]
Avec INSERT ... SELECT, vous pouvez rapidement insérer plusieurs lignes dans une table à partir d'une ou plusieurs tables. Par exemple:
INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id> 100;

Remarques

Insert de base

INSERT INTO `table_name` (`field_one`, `field_two`) VALUES ('value_one', 'value_two');

Dans cet exemple trivial, table_name est l'endroit où les données doivent être ajoutées, field_one et field_two sont des champs pour définir les données, et value_one et value_two sont les données à faire respectivement avec field_one et field_two .

Il est recommandé de répertorier les champs dans lesquels vous insérez des données dans votre code, comme si la table était modifiée et que de nouvelles colonnes étaient ajoutées, votre insertion serait rompue si elles ne s'y trouvaient pas.

INSERT, ON DUPLICATE MISE À JOUR DE LA CLÉ

INSERT INTO `table_name` 
    (`index_field`, `other_field_1`, `other_field_2`) 
    VALUES 
    ('index_value', 'insert_value', 'other_value') 
    ON DUPLICATE KEY UPDATE 
        `other_field_1` = 'update_value',
        `other_field_2` = VALUES(`other_field_2`);

Cela INSERT dans table_name les valeurs spécifiées, mais si la clé unique existe déjà, il mettra à jour le other_field_1 d'avoir une nouvelle valeur.
Parfois, lors de la mise à jour sur une clé dupliquée, il est utile d'utiliser VALUES() pour accéder à la valeur d'origine transmise à INSERT au lieu de définir directement la valeur. De cette façon, vous pouvez définir différentes valeurs en utilisant INSERT et UPDATE . Voir l'exemple ci-dessus où other_field_1 est défini sur insert_value sur INSERT ou update_value sur UPDATE alors que other_field_2 est toujours défini sur other_value .

Pour que l'insertion sur la mise à jour des clés en double (IODKU) fonctionne, il faut que le schéma contienne une clé unique qui signalera un conflit en double. Cette clé unique peut être une clé primaire ou non. Il peut s'agir d'une clé unique sur une seule colonne ou d'une multi-colonne (clé composite).

Insertion de plusieurs lignes

INSERT INTO `my_table` (`field_1`, `field_2`) VALUES 
    ('data_1', 'data_2'),
    ('data_1', 'data_3'),
    ('data_4', 'data_5');

C'est un moyen facile d'ajouter plusieurs lignes à la fois avec une seule instruction INSERT .

Ce type d’insertion par lots est beaucoup plus rapide que l’insertion de lignes une par une. En règle générale, l'insertion de 100 lignes dans une seule insertion de lot est 10 fois plus rapide que si vous les insérez toutes individuellement.

Ignorer les lignes existantes

Lors de l'importation de jeux de données volumineux, il peut être préférable, dans certaines circonstances, d'ignorer les lignes entraînant l'échec de la requête en raison d'une restriction de colonne, par exemple, les clés primaires dupliquées. Cela peut être fait en utilisant INSERT IGNORE .

Prenons l'exemple suivant:

SELECT * FROM `people`;
--- Produces:
+----+------+
| id | name |
+----+------+
|  1 | john |
|  2 | anna |
+----+------+


INSERT IGNORE INTO `people` (`id`, `name`) VALUES
    ('2', 'anna'), --- Without the IGNORE keyword, this record would produce an error
    ('3', 'mike');

SELECT * FROM `people`;
--- Produces:
+----+--------+
| id |  name  |
+----+--------+
|  1 |  john  |
|  2 |  anna  |
|  3 |  mike  |
+----+--------+

La chose importante à retenir est que INSERT IGNORE ignorera également les autres erreurs en silence. Voici ce que disent les documentations officielles de Mysql:

Les conversions de données qui déclencheraient des erreurs annulent l'instruction si IGNORE n'est pas> spécifié. Avec IGNORE, les valeurs non valides sont ajustées aux valeurs les plus proches et> insérées; des avertissements sont produits mais l'instruction ne s'interrompt pas.

Remarque: - La section ci-dessous est ajoutée pour des raisons d'exhaustivité, mais n'est pas considérée comme la meilleure pratique (cela échouerait, par exemple, si une autre colonne était ajoutée dans la table).

Si vous spécifiez la valeur de la colonne correspondante pour toutes les colonnes de la table, vous pouvez ignorer la liste de colonnes dans l'instruction INSERT comme suit:

INSERT INTO `my_table` VALUES 
        ('data_1', 'data_2'),
        ('data_1', 'data_3'),
        ('data_4', 'data_5');

INSERT SELECT (Insérer des données d'une autre table)

C'est la méthode de base pour insérer des données d'une autre table avec l'instruction SELECT.

INSERT INTO `tableA` (`field_one`, `field_two`) 
   SELECT `tableB`.`field_one`, `tableB`.`field_two` 
   FROM `tableB` 
   WHERE `tableB`.clmn <> 'someValue'
   ORDER BY `tableB`.`sorting_clmn`;

Vous pouvez SELECT * FROM , mais tableA et tableB devez avoir correspondre le nombre de colonnes et types de données correspondant.

Les colonnes avec AUTO_INCREMENT sont traitées comme dans la clause INSERT with VALUES .

Cette syntaxe facilite le remplissage de tables (temporaires) avec des données provenant d'autres tables, d'autant plus lorsque les données doivent être filtrées sur l'insert.

INSERT avec AUTO_INCREMENT + LAST_INSERT_ID ()

Lorsqu'une table a une PRIMARY KEY AUTO_INCREMENT , normalement, on ne l'insère pas dans cette colonne. Au lieu de cela, spécifiez toutes les autres colonnes, puis demandez quel était le nouvel identifiant.

CREATE TABLE t (
    id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
    this ...,
    that ...,
    PRIMARY KEY(id) );

INSERT INTO t (this, that) VALUES (..., ...);
SELECT LAST_INSERT_ID() INTO @id;
INSERT INTO another_table (..., t_id, ...) VALUES (..., @id, ...);

Notez que LAST_INSERT_ID() est lié à la session, donc même si plusieurs connexions sont insérées dans la même table, chacune avec son propre identifiant.

Votre API client a probablement une autre manière d’obtenir le LAST_INSERT_ID() sans effectuer réellement une commande SELECT et remettre la valeur au client au lieu de la laisser dans une @variable dans MySQL. C'est généralement préférable.

Exemple plus long et plus détaillé

L'utilisation "normale" d'IODKU consiste à déclencher une "clé dupliquée" basée sur une clé UNIQUE , et non sur la touche AUTO_INCREMENT PRIMARY KEY . Ce qui suit illustre ceci. Notez qu'il ne fournit pas l' id dans l'INSERT.

Configuration des exemples à suivre:

CREATE TABLE iodku (
    id INT AUTO_INCREMENT NOT NULL,
    name VARCHAR(99) NOT NULL,
    misc INT NOT NULL,
    PRIMARY KEY(id),
    UNIQUE(name)
) ENGINE=InnoDB;

INSERT INTO iodku (name, misc)
    VALUES
    ('Leslie', 123),
    ('Sally', 456);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
+----+--------+------+
| id | name   | misc |
+----+--------+------+
|  1 | Leslie |  123 |
|  2 | Sally  |  456 |
+----+--------+------+

Le cas où IODKU effectue une "mise à jour" et LAST_INSERT_ID() récupère l' id correspondant:

INSERT INTO iodku (name, misc)
    VALUES
    ('Sally', 3333)            -- should update
    ON DUPLICATE KEY UPDATE    -- `name` will trigger "duplicate key"
    id = LAST_INSERT_ID(id),
    misc = VALUES(misc);
SELECT LAST_INSERT_ID();       -- picking up existing value
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+

Le cas où IODKU effectue un "insert" et LAST_INSERT_ID() récupère le nouvel id :

INSERT INTO iodku (name, misc)
    VALUES
    ('Dana', 789)          -- Should insert
    ON DUPLICATE KEY UPDATE
    id = LAST_INSERT_ID(id),
    misc = VALUES(misc);
SELECT LAST_INSERT_ID();   -- picking up new value
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+

Contenu de la table résultant:

SELECT * FROM iodku;
+----+--------+------+
| id | name   | misc |
+----+--------+------+
|  1 | Leslie |  123 |
|  2 | Sally  | 3333 |  -- IODKU changed this
|  3 | Dana   |  789 |  -- IODKU added this
+----+--------+------+

Identifiants AUTO_INCREMENT perdus

Plusieurs fonctions d'insertion peuvent "graver" des identifiants. Voici un exemple d'utilisation d'InnoDB (les autres moteurs peuvent fonctionner différemment):

CREATE TABLE Burn (
    id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
    name VARCHAR(99) NOT NULL,
    PRIMARY KEY(id),
    UNIQUE(name)
        ) ENGINE=InnoDB;

INSERT IGNORE INTO Burn (name) VALUES ('first'), ('second');
SELECT LAST_INSERT_ID();          -- 1
SELECT * FROM Burn ORDER BY id;
  +----+--------+
  |  1 | first  |
  |  2 | second |
  +----+--------+

INSERT IGNORE INTO Burn (name) VALUES ('second');  -- dup 'IGNOREd', but id=3 is burned
SELECT LAST_INSERT_ID();          -- Still "1" -- can't trust in this situation
SELECT * FROM Burn ORDER BY id;
  +----+--------+
  |  1 | first  |
  |  2 | second |
  +----+--------+

INSERT IGNORE INTO Burn (name) VALUES ('third');
SELECT LAST_INSERT_ID();           -- now "4"
SELECT * FROM Burn ORDER BY id;    -- note that id=3 was skipped over
  +----+--------+
  |  1 | first  |
  |  2 | second |
  |  4 | third  |    -- notice that id=3 has been 'burned'
  +----+--------+

Pensez-y (grosso modo) de cette façon: tout d'abord, l'insert regarde le nombre de lignes pouvant être insérées. Ensuite, récupérez les nombreuses valeurs de auto_increment pour cette table. Enfin, insérez les lignes, en utilisant les identifiants si nécessaire et en gravant tous les restes.

La seule fois où le reste est récupérable est si le système est arrêté et redémarré. Au redémarrage, MAX(id) est effectivement exécuté. Cela peut réutiliser les identifiants qui ont été gravés ou qui ont été libérés par DELETEs des identifiants les plus élevés.

Essentiellement, toute version d' INSERT (y compris REPLACE , qui est DELETE + INSERT ) peut graver des identifiants. Dans InnoDB, la variable globale (pas de session!) innodb_autoinc_lock_mode peut être utilisée pour contrôler une partie de ce qui se passe.

Lors de la "normalisation" de longues chaînes dans un AUTO INCREMENT id , la gravure peut facilement se produire. Cela pourrait entraîner un débordement de la taille de l' INT vous avez choisi.

Modified text is an extract of the original Stack Overflow Documentation

Sous licence CC BY-SA 3.0

Non affilié à Stack Overflow

MySQL
INSÉRER

Recherche…