Discussion :

[Dens1]

Bonjour,

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[manzeki]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

CALL insertion('bubu', 300);

[Dens1]

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.

[CinePhil]

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 !

[Dens1]

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

enfin un début de solution compréhensible

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

Je commence à le concevoir.

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

C'est un fait

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" en tentant ceci, qui me satisfait à moitié :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[Artemus24]

Salut à tous.

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

@+

[Dens1]

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
Et que déroger va me fatiguer autant que la machine qui exécute les mauvaises solutions. Thanks.

[CinePhil]

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 :

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 !

[Dens1]

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 mais peut-être pas au sens où vous l'entendez.

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.

[manzeki]

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.

Ç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

[Artemus24]

Salut à tous.

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

Cordialement.
Artemus24.
@+

[escartefigue]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Artemus24]

Salut à tous.

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.

Non, ce n'est pas vrai car cela dépend comment a été déclaré les clef étrangères.

Voici un exemple de renumérotation d'une table mère, avec une table fille ayant une clef étrangère :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `mere`
--------------
 
--------------
CREATE TABLE `mere`
( `id`    integer unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `val`   varchar(10)      NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `mere` (`id`, `val`) VALUES
(25, 'bleu'),(75, 'rouge'),(255, 'vert'),(480, 'jaune')
--------------
 
--------------
select * from mere
--------------
 
+-----+-------+
| id  | val   |
+-----+-------+
|  25 | bleu  |
|  75 | rouge |
| 255 | vert  |
| 480 | jaune |
+-----+-------+
--------------
DROP TABLE IF EXISTS `fille`
--------------
 
--------------
CREATE TABLE `fille` (
  `id`      integer unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `clef`    integer unsigned NOT NULL,
  `nuance`  varchar(10)      NOT NULL,
  CONSTRAINT `fk_fille_clef` FOREIGN KEY (`clef`) REFERENCES `mere` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `fille` (`clef`, `nuance`) VALUES
(25, 'marine'),    (75, 'carmin'),(255, 'pomme'),    (480, 'citron'),
(25, 'nuit'),      (75, 'vif'),   (255, 'émeraude'), (480, 'pâle'),
(25, 'turquoise'), (25, 'cyan'),  (255, 'olive')
--------------
 
--------------
select * from fille
--------------
 
+----+------+-----------+
| id | clef | nuance    |
+----+------+-----------+
|  1 |   25 | marine    |
|  2 |   75 | carmin    |
|  3 |  255 | pomme     |
|  4 |  480 | citron    |
|  5 |   25 | nuit      |
|  6 |   75 | vif       |
|  7 |  255 | émeraude  |
|  8 |  480 | pâle      |
|  9 |   25 | turquoise |
| 10 |   25 | cyan      |
| 11 |  255 | olive     |
+----+------+-----------+
--------------
select p.id     as 'Mère ID',
       p.val    as 'Mère VAL',
       f.id     as 'Fille ID',
       f.nuance as 'Fille NUANCE'
from       mere as p
inner join fille as f
on f.clef = p.id
--------------
 
+---------+----------+----------+--------------+
| Mère ID | Mère VAL | Fille ID | Fille NUANCE |
+---------+----------+----------+--------------+
|      25 | bleu     |        1 | marine       |
|      25 | bleu     |        5 | nuit         |
|      25 | bleu     |        9 | turquoise    |
|      25 | bleu     |       10 | cyan         |
|      75 | rouge    |        2 | carmin       |
|      75 | rouge    |        6 | vif          |
|     255 | vert     |        3 | pomme        |
|     255 | vert     |        7 | émeraude     |
|     255 | vert     |       11 | olive        |
|     480 | jaune    |        4 | citron       |
|     480 | jaune    |        8 | pâle         |
+---------+----------+----------+--------------+
--------------
update `mere`, (select @x:=0) as x set id = (@x:=@x+1)
--------------
 
--------------
select * from mere
--------------
 
+----+-------+
| id | val   |
+----+-------+
|  1 | bleu  |
|  2 | rouge |
|  3 | vert  |
|  4 | jaune |
+----+-------+
--------------
select * from fille
--------------
 
+----+------+-----------+
| id | clef | nuance    |
+----+------+-----------+
|  1 |    1 | marine    |
|  2 |    2 | carmin    |
|  3 |    3 | pomme     |
|  4 |    4 | citron    |
|  5 |    1 | nuit      |
|  6 |    2 | vif       |
|  7 |    3 | émeraude  |
|  8 |    4 | pâle      |
|  9 |    1 | turquoise |
| 10 |    1 | cyan      |
| 11 |    3 | olive     |
+----+------+-----------+
--------------
select      m.id     as 'Mère ID',
            m.val    as 'Mère VAL',
            f.id     as 'Fille ID',
            f.nuance as 'Fille NUANCE'
      from  mere  as m
inner join  fille as f
        on  f.clef = m.id
--------------
 
+---------+----------+----------+--------------+
| Mère ID | Mère VAL | Fille ID | Fille NUANCE |
+---------+----------+----------+--------------+
|       1 | bleu     |        1 | marine       |
|       1 | bleu     |        5 | nuit         |
|       1 | bleu     |        9 | turquoise    |
|       1 | bleu     |       10 | cyan         |
|       2 | rouge    |        2 | carmin       |
|       2 | rouge    |        6 | vif          |
|       3 | vert     |        3 | pomme        |
|       3 | vert     |        7 | émeraude     |
|       3 | vert     |       11 | olive        |
|       4 | jaune    |        4 | citron       |
|       4 | jaune    |        8 | pâle         |
+---------+----------+----------+--------------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

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 !

Non, car en détruisant la colonne "id" et en la recréant, comme dans mon exemple ci-dessus, l'auto incrément aura la dernière valeur +1 de la table.

Mais que les ofc_id se succèdent ou pas, ON S'EN FOUT UNE BONNE FOIS POUR TOUTES !

Je suis d'accord car la colonne "id" est une colonne technique et n'a pas d'autre fonction que de servir comme lien pour les clefs étrangères.

@+

[escartefigue]

Non, ce n'est pas vrai car cela dépend comment a été déclaré les clef étrangères.

Sauf que ce mode opératoire ne correspond pas à ce qui est proposé : vous n'avez pas détruit la PK, donc l'update cascade reste applicable !
Dans le mode opératoire proposé on supprime la PK et par la même l'option CASCADE

[Artemus24]

Salut à tous.

Désolé Escartefigue, je suis pas mal fatigué en ce moment et il m'arrive de m'embrouiller dans mes réponses.

En effet, si l'on supprime la primary key, automatiquement, tous les lignes des tables filles seront à leur tour supprimées.
Ce n'est donc pas la bonne approche si l'on a comme contrainte l'option CASCADE pour les foreign key.

Il vaut mieux utiliser un update comme je l'ai fait dans mon exemple.
Sauf que cette approche ne va pas remettre l'auto incrément à la dernière valeur de la primary key.
Il y aura bien un trou lors de la prochaine insertion.

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 ?

Votre comparaison est excellente !!!

Je me pose encore une fois l'intérêt de cette discussion au sujet des trous dans une clef primaire technique.
En quoi cela dérange nos chers néophytes d'avoir des trous dans la numérotation ?
Il y a une forme d'obsession de type compulsive à vouloir boucher ces trous.

@+

[Dens1]

Je me pose encore une fois l'intérêt de cette discussion au sujet des trous dans une clef primaire technique.
En quoi cela dérange nos chers néophytes d'avoir des trous dans la numérotation ?
Il y a une forme d'obsession de type compulsive à vouloir boucher ces trous.@+

De mémoire, en fait, non. Puisqu'on supprime le premier auto-incrément pour en créer un nouveau dans une autre colonne, le second redémarre à 1.

C'est ce que j'avais compris.

[QUOTE=CinePhil]Par contre, on peut ajouter que si on fait une insertion en masse (avec un LOAD DATA INFILE par exemple), MySQL/MariaDB peut lui même créer un trou dans la série d'auto-incrément. Je ne sais plus pourquoi mais je l'ai déjà constaté. Donc bidouiller la suite des auto_increment ne sert à rien ! /QUOTE]
LOAD DATA INFILE ça me plaît bien ça, et puis je commence à regarder les clés étrangères de près et .... Il est temps de plonger dans le tuto cité page 1. Nager c'est une chose, couler, ça m'ennuierait.
Cette discussion sur les trous fait avancer mine de rien, merci à vous (Rassurez-vous j'ai bien compris l'aspect purement technique de la colonne auto_incrément).