Maîtriser l'auto_increment lors d'insert multiples et on duplicate key update

Bonjour,

je crée une table à l'image de celle prise en exemple ici : https://riptutorial.com/mysql/exampl...st-insert-id--.

Code:

---

1 2 3 4 5 6 7

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;

---

Maintenant je voudrais remplir ma table avec des inserts multiples du style:

Code:

---

1 2 3 4 5 6 7

INSERT INTO iodku (name, misc) VALUES ("bubu",34), ("toto", 70), ("jd", 20), ("sandy",28), ("mag", 53), ("nani",49);

---

Demain, je veux insérer une nouvelle liste comme ci-dessus, sans pour autant me souvenir que j'ai déjà inséré telle ou telle valeur et je ne veux pas la dupliquer,
d'où l'attribut UNIQUE de la colonne name. (Mes connaissances sont limitées, je débute quasiment tant ça remonte à loin). Je vais insérer :

Code:

---

1 2 3 4 5 6 7 8

INSERT INTO iodku (name, misc) VALUES ("val",34), ("turlututu", 70), ("eva", 20), ("sandy",44), ("blurp", 50), ("jd", 50), ("vince",49);

---

Partant du principe que la requête (insert des lignes valides avec NON MODIFICATION des lignes existantes) doit aboutir.
En utilisant ON DUPLICATE KEY UPDATE :

Code:

---

1 2 3 4 5 6 7 8 9 10 11

INSERT INTO iodku (name, misc) VALUES ("val",34), ("turlututu", 70), ("eva", 20), ("sandy",44), ("blurp", 50), ("jd", 50), ("vince",49) ON DUPLICATE KEY UPDATE name = name, misc = misc;

---

Façon d'écrire que je ne veux pas qu'une valeur déjà existante soit modifiée, mais que l'insert pour cette valeur soit abandonné, sans que l'auto_increment
soit incrémenté, pour obtenir une table composée de

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

1 bubu 34 2 toto 70 3 jd 20 4 sandy 28 5 mag 53 6 nani 49 7 val 34 8 turlututu 70 9 eva 20 10 blurp 50 11 vince 49

---

Avec l'auto_increment = 12 (prochain id disponible pour un nouvel insert).

Comment faire concrètement ?

D'ailleurs INSERT IGNORE INTO auto incremente aussi lors des warnings.

MySQL n'autorise pas qu'un trigger modifie la table sur la laquelle il est assigné.
Cela est possible avec d'autre SGBD

Passez par une procédure ou une fonction pour reprogrammer un équivalent à un INSERT REPLACE comme ceci

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

DELIMITER @@ CREATE PROCEDURE insertion( name_ VARCHAR(99), misc_ integer ) BEGIN   DECLARE doublon VARCHAR(99);   SELECT name INTO doublon FROM iodku WHERE name = name_;   IF doublon IS NULL THEN INSERT INTO iodku (name, misc) VALUES (name_, misc_); ELSE UPDATE iodku SET misc = misc_ WHERE name = name_; END IF;   END@@ DELIMITER ;

---

Test

Code:

---

CALL insertion('bubu', 300);

---

Merci pour la réponse cependant je ne la comprends pas.
Si vous m'aviez dit "MySql n'autorise pas l'auto incrémentation a ne se déclencher que quand une ligne est concrètement insérée, (en d'autres termes n'autorise pas à suivre pas à pas la valeur renvoyée pas last_insert_id())" j'aurais beaucoup mieux compris.
Je m'étonne que dans la réponse et même dans le code il n'y ait aucune allusion à l'auto_increment: car en fait, c'est mon problème (là où j'en suis).

Ça me dépasse que l'incrémentation se déclenche en cas de warning (ignore) ou update (même en ne modifiant rien), et du coup en cas de simple accès (via un appel à insert) à l'enregistrement. Enfin merci quand même. De toutes façons je reste avec mysql, et les procédures j'y suis pas encore, Pourtant ça me paraît une idée tellement basique d'incrémenter qu'en cas d'insertion réelle d'un nouvel enregistrement que ... les doigts m'en tombent.

Si votre inquiétude est que l'auto-incrément ne donne pas forcément l'id maxi + 1, cessez de vous inquiéter !
Un identifiant auto-incrémenté est une donnée technique, sans signification. Que Jean Dupont soit identifié 1, 12, ou 3825, on s'en fiche, du moment que cet identifiant est correctement propagé via le mécanisme des clés étrangères.

Et vouloir rétablir une liste continue d'identifiants est une très mauvaise idée totalement inutile !

Citation:

---

Si votre inquiétude est que l'auto-incrément ne donne pas forcément l'id maxi + 1, cessez de vous inquiéter !

---

:P enfin un début de solution compréhensible

Citation:

---

Un identifiant auto-incrémenté est une donnée technique, sans signification.

---

Je commence à le concevoir.

Citation:

---

Que Jean Dupont soit identifié 1, 12, ou 3825, on s'en fiche

---

C'est un fait :ptdr:

En effet, buter sur un truc rien que parce qu'on ne conçoit pas que ça puisse être plus esthétique, c'est pas une solution, et puis c'est l'ordi qui traite, pas ma mémoire heureusement. Merci CinéPhil je vais abandonner l'idée, un peu chagrin mais bon ... Si c'était possible, vous le sauriez. Maintenant, pourquoi c'est pas possible, seuls les créateurs peuvent le dire.

J'ai quand même "poussé un peu plus loin" :calim2: en tentant ceci, qui me satisfait à moitié :

Code:

---

1 2 3

CREATE TABLE IF NOT EXISTS iodku_1 AS SELECT name, misc FROM iodku; TRUNCATE TABLE iodku; INSERT INTO iodku (name, misc) SELECT name, misc FROM iodku_1;

---

Qui a pour effet de bien réindexer dans un ordre continu la colonne id auto_incrementée.

Problème: la valeur de l'auto_increment n'est pas la bonne, comme si le "défaut" avait été transféré dans la table copiée, qui pourtant ne possède pas de colonne id. C'est mystérieux. Mais j'ai ma table iodku avec tout les "index" continus, au final après manip.

Autre problème: pour l'instant cette table iodku n'est liée à rien, mais quand ça sera le cas que va t'il se passer ? Est ce que dans une table contenant une référence à un enregistrement iodku (par exemple une table prise_de_tete contenant une colonne id_iodku), si id de Dupont a changé dans iodku, il se mettra à jour automatiquement dans prise_de_tete ? Simple question.

Salut à tous.

C'est ce genre de résultat que vous désirez obtenir ?

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136

-------------- START TRANSACTION --------------   -------------- set session collation_connection = "latin1_general_ci" --------------   -------------- DROP DATABASE IF EXISTS `base` --------------   -------------- CREATE DATABASE IF NOT EXISTS `base` DEFAULT CHARACTER SET `latin1` DEFAULT COLLATE `latin1_general_ci` --------------   -------------- DROP TABLE IF EXISTS `test` --------------   -------------- CREATE TABLE `test` ( `id` integer unsigned NOT NULL Primary key, `name` varchar(255) NOT NULL, `misc` integer unsigned NOT NULL, unique index (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED --------------   -------------- DROP TRIGGER IF EXISTS `increment` --------------   -------------- CREATE TRIGGER `increment` BEFORE INSERT ON `test` FOR EACH ROW BEGIN SET NEW.id = (select coalesce(max(id)+1,1) from `test`); END --------------   -------------- INSERT IGNORE INTO `test` (`name`,`misc`) VALUES ('bubu', 34) --------------   -------------- INSERT IGNORE INTO `test` (`name`,`misc`) VALUES ('toto', 70) --------------   -------------- INSERT IGNORE INTO `test` (`name`,`misc`) VALUES ('jd', 20) --------------   -------------- INSERT IGNORE INTO `test` (`name`,`misc`) VALUES ('sandy',28) --------------   -------------- INSERT IGNORE INTO `test` (`name`,`misc`) VALUES ('mag', 53) --------------   -------------- INSERT IGNORE INTO `test` (`name`,`misc`) VALUES ('nani', 49) --------------   -------------- select * from `test` --------------   +----+-------+------+ | id | name | misc | +----+-------+------+ | 1 | bubu | 34 | | 2 | toto | 70 | | 3 | jd | 20 | | 4 | sandy | 28 | | 5 | mag | 53 | | 6 | nani | 49 | +----+-------+------+ -------------- INSERT IGNORE INTO `test` (`name`,`misc`) VALUES ('val', 34) --------------   -------------- INSERT IGNORE INTO `test` (`name`,`misc`) VALUES ('turlututu', 70) --------------   -------------- INSERT IGNORE INTO `test` (`name`,`misc`) VALUES ('eva', 20) --------------   -------------- INSERT IGNORE INTO `test` (`name`,`misc`) VALUES ('sandy', 44) --------------   -------------- INSERT IGNORE INTO `test` (`name`,`misc`) VALUES ('blurp', 50) --------------   -------------- INSERT IGNORE INTO `test` (`name`,`misc`) VALUES ('jd', 50) --------------   -------------- INSERT IGNORE INTO `test` (`name`,`misc`) VALUES ('vince', 49) --------------   -------------- select * from `test` --------------   +----+-----------+------+ | id | name | misc | +----+-----------+------+ | 1 | bubu | 34 | | 2 | toto | 70 | | 3 | jd | 20 | | 4 | sandy | 28 | | 5 | mag | 53 | | 6 | nani | 49 | | 7 | val | 34 | | 8 | turlututu | 70 | | 9 | eva | 20 | | 10 | blurp | 50 | | 11 | vince | 49 | +----+-----------+------+ -------------- COMMIT --------------   Appuyez sur une touche pour continuer...

---

Plusieurs remarques :

1) la colonne 'id' est normalement une colonne technique qui ne sert qu'en interne à mysql et n'a pas vocation à jouer un rôle fonctionnel.
La bonne déclaration est : "`id integer unsigned NOT NULL auto_increment Primary key,

2) qu'il y ait des trous dans la numérotation de la colonne 'id' n'a aucune importance.
Si le problème concerne la valeur maximale, ne vous inquiétez pas , il vous faudra une vie entière sans jamais l'atteindre.
Mais si vous êtes obnubilez par cela, autant remplacer "integer" par "bigint".

3) pour résoudre votre problème de trous, j'ai utilisé un trigger qui va rechercher la plus grande valeur de la colonne 'id', déjà stockée en table.
Ca fonctionne, mais c'est problématique à cause du temps nécessaire pour effectuer cette recherche.
Autrement dit, je vous déconseille de procéder ainsi.

4) une autre solution consiste à créer une table qui va contenir la prochaine valeur que vous désirez mettre dans la colonne 'id'.
Vous l'incrémenter après l'insertion. Sauf que vous ne résolvez pas votre problème car c'est le mécanisme de mysql qui provoque les trous.

Code:

---

1 2 3 4 5 6 7 8

CREATE TRIGGER `increment` BEFORE INSERT ON `test` FOR EACH ROW BEGIN SET NEW.id = (select val from `increment`);   UPDATE `increment` SET val = val + 1; END

---

5) le mieux est encore d'utiliser l'auto incrément de MySql et de ne pas se poser des questions métaphysiques !
Ou si vous préférez, ne réinventez pas ce qui existe déjà dans MySql.

6) supprimer les messages d'informations, d'anomalies (warnings) ou d'erreurs n'est pas judicieux.
Ils ont une raison d'être pour le bon fonctionnement de votre script.

7) on peut utiliser un "unique index" pour éviter le doublon d'une valeur lors de l'insertion.
C'est une pratique qui consiste à demander à MySql de faire le boulot.

La solution qui consiste à ignorer le doublon lors de l'insertion est déconseillée car elle provoque une erreur. Ce qui n'est pas bien !

Les bonnes solutions sont :

--> On peut mettre à jour la colonne, qui est le doublon. Ce qui revient à ne rien faire.

Code:

---

1 2	insert into `test` (`name`,`misc`) values ('sandy', 44) on duplicate key update `name` = values(`name`);

---

--> Ou encore mettre à jour les autres colonnes :

Code:

---

1 2	insert into `test` (`name`,`misc`) values ('sandy', 44) on duplicate key update `misc` = values(`misc`);

---

@+

Okay Artemus24, merci à vous deux, maintenant c'est limpide : je sais comment aborder ce non problème, et avec moultes explications en prime.
J'attends juste un avis sur mon message concernant la demie non solution de passer par une table temporaire, un truncate, et un recopiage, puis je passe le fil en résolu.
La réponse est que la tambouille interne de mysql ne doit pas métaphysiquement me préoccuper :mouarf:
Et que déroger va me fatiguer autant que la machine qui exécute les mauvaises solutions. Thanks.

Citation:

---

J'attends juste un avis sur mon message concernant la demie non solution de passer par une table temporaire, un truncate, et un recopiage

---

Mais pourquoi faire ?

Le seul cas qui pourrait être "utile" (et encore, je mets les guillemets) est si vous passez une base de développement dans laquelle vous avez fait des tests et donc inséré puis supprimé des valeurs, en base de production.

Quant à l'autre question que vous posez :

Citation:

---

Autre problème: pour l'instant cette table iodku n'est liée à rien, mais quand ça sera le cas que va t'il se passer ? Est ce que dans une table contenant une référence à un enregistrement iodku (par exemple une table prise_de_tete contenant une colonne id_iodku), si id de Dupont a changé dans iodku, il se mettra à jour automatiquement dans prise_de_tete ?

---

Si vous utilisez le mécanisme des clés étrangères (et vous avez grand intérêt à le faire), oui, ça se mettra automatiquement à jour lors d'une requête UPDATE :

Code:

---

1 2 3

UPDATE une_table SET id = 12 WHERE id = 15

---

Si cet id est référencé dans la clé étrangère d'une autre table, la clé étrangère se mettra à jour.

Par contre, votre opération TRUNCATE supprime les données de la table, ce qui entraînera la suppression des lignes correspondantes dans les autres tables comportant une clé étrangère référençant la table objet du TRUNCATE.

Tout ceci, bien entendu, si vos clés étrangères sont munies des consignes ON DELETE CASCADE ON UPDATE CASCADE.

Imaginez qu'il y ait un programme qui utilise certaines valeurs en dur parce que, en principe, la table utilisée ne doit pas changer (table de référence des catégories par exemple où la catégorie par défaut porte l'identifiant X). Si vous changez l'identifiant de la catégorie par défaut en BDD, votre programme n'affichera plus la même catégorie puisque l'ancien identifiant était utilisé en dur.

Bref, encore une fois : ne touchez pas aux identifiants auto-incrémentés !

Citation:

---

Envoyé par CinePhil

Mais pourquoi faire ?
Le seul cas qui pourrait être "utile" (et encore, je mets les guillemets) est si vous passez une base de développement dans laquelle vous avez fait des tests et donc inséré puis supprimé des valeurs, en base de production.

---

Oui je suis en phase de test :aie: mais peut-être pas au sens où vous l'entendez.

Citation:

---

Envoyé par CinePhil

Bref, encore une fois : ne touchez pas aux identifiants auto-incrémentés !

---

Entendu.

Merci à vous trois (oui tout à l'heure j'oubliais la première réponse au message initial, injustement car c'est toujours sympa d'aider).
En ce qui me concerne maintenant c'est plus clair que tous les topics que j'ai pu lire sur le sujet depuis 24h que j'essaie de comprendre où est la solution.
Dans mon cas particulier, je vais insérer dans ma table tout ce dont j'ai besoin sans me soucier de l'auto_incrément et de l'id. Comme cette table ne sera soumise qu'à de minimes modifications par la suite, je me fais "fort" de balancer les données sous une forme ou sous une autre dans un fichier texte, puis à coup de sed, de awk et de pipe, mettre en forme une requête insert, et copier/coller la dite requête dans la table "définitive" (nouvellement créée pour recevoir les data).
Ainsi, je n'aurais plus perdu de temps, tout en apprenant ce qu'il est possible, ou pas (et utile, ou pas).
Merci beaucoup. 8-):roll::zoubi:

Citation:

---

Envoyé par Dens1

Merci pour la réponse cependant je ne la comprends pas.
Je m'étonne que dans la réponse et même dans le code il n'y ait aucune allusion à l'auto_increment: car en fait, c'est mon problème (là où j'en suis).

---

(1) Non, c'était sous entendu, même si je ne mentionne pas le terme auto_increment.
c'est bien pour cela que je vous suggère la procédure stockée pour faire votre insertion.
J'ai d'ailleurs testé avec vos propres données et ça marche.

(2) En général je ne fait pas usage d'auto_increment sauf exception. Je créée mes propres identifiants avec PHP, PyTHON ou Java ou côté BD.
Artemus24 vous propose un bel exemple côté BD.

Citation:

---

Ça me dépasse que l'incrémentation se déclenche en cas de warning (ignore) ou update (même en ne modifiant rien)....

---

Pour ce qui est du comportement de la commande INSERT INTO ... ON DUPLICATE KEY UPDATE ...
La documentation 8.x confirme une incrémentation sur une colonne avec auto_increment.
Mais après test, je n'ai pas d'incrémentation. Étrange.

https://dev.mysql.com/doc/refman/8.0...duplicate.html

Oui c'est bizarre, en tous cas merci pour vos précisions manzeki.
Oui si mon problème se représente sous une forme un peu différente à l'avenir je reviendrai jeter un oeil à cette petite mine d'information.

Quel tutoriel me conseilleriez vous pour aborder (en profondeur aussi) mysql, rapidement efficace au passage ?

Citation:

---

Quel tutoriel me conseilleriez vous pour aborder (en profondeur aussi) mysql, rapidement efficace au passage ?

---

Regardez le site DVP de SQLPro. C'est pour l'essentiel du SQL pur et ça fonctionnera dans la majorité des cas et vous évitera de prendre de mauvaises habitudes vec MySQL/MariaDB.

Merci bien :)

Salut à tous.

Merci ! :D Bonne fête de fin d'année à vous aussi.

Cordialement.
Artemus24.
@+

Tout ou presque a été dit. On peut ajouter que si on souhaite simplement disposer d'un compteur unique et dont les valeurs sont contigües, on peut utiliser la fonction ROW_NUMBER() et ainsi s'affranchir des aléas de numérotation des colonnes de type AUTO_INCREMENT.

Seul bémol : MySQL ne connait les fonctions OLAP que depuis la V8

Exemple d'utilisation :

Code:

---

1 2 3 4 5 6 7 8 9 10

with tab0(CIDT, CVAL) as (select 1, 'asterix' union all select 2, 'obelix' union all select 4, 'idefix' union all select 5, 'panoramix' ) select CIDT , CVAL , Row_number() over(order by CIDT) as numr from tab0

---

Résultat :
Pièce jointe 588646

Hum, attention :

Citation:

---

Envoyé par manzeki

(2) En général je ne fait pas usage d'auto_increment sauf exception. Je créée mes propres identifiants avec PHP, PyTHON ou Java ou côté BD.
Artemus24 vous propose un bel exemple côté BD.

---

Si c'est l'application qui crée les identifiants, ce n'est applicable qu'en environnement mono-utilisateur et mono-thread, en environnement multi-thread, c'est bien coté BD qu'il faut gérer les identifiants.
Tout l'intérêt des IDENTITY (auto_increment pour MySQL) est de n'avoir pas à se préoccuper de la gestion de l'unicité, c'est le moteur du SGBD qui s'en charge peu importe le nombre de threads actifs :).

Imaginons que je veuille vraiment mes id contigus pour une raison qui tiendrait + de la marotte qu'autre chose (je relance car je suis tombé par hasard sur un truc intéressant).
Avant de tester la procédure ci-dessous, j'aimerais savoir ce qui, si exécutée telle quelle, va se passer dans les tables dont les colonnes utilise l'id qui va se trouver modifié du fait de la procédure. Par conséquent, connaître les précautions à prendre pour éviter un "désastre".

Citation:

---

6.38 Comment puis-je réaffecter les valeurs auto-incrémentées ?

Certains utilisateurs préfèrent que leurs valeurs AUTO_INCREMENT soient consécutives ; ce n’est pas toujours le cas après la suppression de lignes.

Voici les étapes pour y parvenir. Ce sont des étapes manuelles parce qu’elles comportent une vérification manuelle à un moment donné.

Vérifiez que vous disposez d’un accès exclusif à la table devant être réorganisée
Sur votre colonne de primary key par exemple id), supprimez le paramètre AUTO_INCREMENT
Supprimer votre clé primaire dans Structure > index
Créer une nouvelle colonne future_id comme clé primaire, AUTO_INCREMENT
Parcourez votre table et vérifiez que les nouveaux incréments correspondent à votre attente
Supprimez votre ancienne colonne id
Renommez la colonne future_id en id
Déplacer la nouvelle colonne id via Structure > Déplacer des colonnes

---

https://docs.phpmyadmin.net/fr/latest/faq.html

Bonjour,
Que ce passe t'il pour les lignes d'une table "enfant" ?
Si par exemple l'id d'un produit passe de 3 à 2, et du suivant de 4 à 3, que deviennent les images qui ont pour id produit 3 ?
Elles se retrouvent associées au mauvais article ?

Que deviennent les clé étrangères si on supprime la colonne vers lesquelles elles "pointent" ?

Tatayo.

Très mauvaise idée !
Quid des clés étrangères ? :aie:

Si des clés étrangères font référence à cette table, la suppression de l'actuelle colonne clé primaire sera impossible sans modifier toues les clés étrangères.
Et si par hasard une autre table UTILISE l'identifiant incrémenté de cette table à bidouiller SANS déclaration de clé étrangère, l'association entre les deux tables est irrémédiablement perdue.

Si vous tenez vraiment à avoir un truc ordonné, créez une colonne spécialement pour ça.

Par exemple, mes tables de référence ont toujours cette structure (exemple) :
tr_objet_fichier_ofc (ofc_id, ofc_code, ofc_libelle, ofc_ordre)

Toutes les colonnes sont munies d'une contrainte d'unicité et la dernière sert à définir l'ordre d'affichage dans les listes présentées à l'utilisateur. Mais que les ofc_id se succèdent ou pas, ON S'EN FOUT UNE BONNE FOIS POUR TOUTES !

Exactement, cette stratégie n'a de sens que si la table vit sa vie toute seule, sans lien d'intégrité avec d'autres tables.

Donc quand on lit

Citation:

---

Certains utilisateurs préfèrent que leurs valeurs AUTO_INCREMENT soient consécutives ; ce n’est pas toujours le cas après la suppression de lignes.

---

Il faut expliquer à ces utilisateurs en quoi leur préférence n'a aucun intérêt, c'est du domaine du blocage intellectuel, ça ne fera pas plus joli dans la BDD. Les "trous", on ne les voit que si on les cherche, autrement dit, si on n'a vraiment rien d'autre à faire...:weird:

Dans le même ordre d'idées, quand une maison est détruite dans une rue, est-ce qu'on renumérote toutes les maisons de la rue ?... :aie::mrgreen:

En plus, dans le mode opératoire communiqué, il manque une étape : après "compactage" pour supprimer les "trous", il faut aller modifier la valeur du prochain incrément (auto_increment_offset), sinon on va redémarrer en laissant un nouveau trou, unique, mais encore plus important !