Réinitialiser les auto-increment de mes tables contenant des données.

Version imprimable

16/12/2020, 10h50
Trebor

Réinitialiser les auto-increment de mes tables contenant des données.

Bonjour à tous,

J'ai plusieurs tables ayant cette structure (je parle de la structure, pas de ce qu'il y a dedans)

id_auto (auto_increment)
nom
num_stagiaire

Ce sont des tables où les données bougent régulièrement mais la table n'est jamais vide...

Pour "maintenir" mes tables au propre, j'ai souhaité réinitialiser mon "auto_increment" (j'arrivais à 350 avec 5 noms dans la table)... et je viens de m'apercevoir que je ne savais pas le faire avec des données à l'intérieur.

J'ai cherché mais tout ce que j'ai trouvé ne correspond pas.

L'idée serait d'indiquer à MySQL de reprendre chaque ligne de la table et de modifier l'auto-increment en partant de 1. Qu'importe l'ordre au final, quoi que ça peut me servir plus tard.

Merci d'avance pour votre aide.
16/12/2020, 11h05
tatayo

Bonjour,
Je ne vois pas le lien entre le numéro courant de cet auto-incrément et sa "propreté".
Quelle est l'importance que l'identifiant du vingtième client soit 492 ?
Ce n'est ni propre, ni sale.
L'identifiant ne doit pas avoir de signification propre, donc je ne vois pas trop le besoin ici.

Tatayo.
16/12/2020, 11h15
Trebor

Merci de ta réponse.

C'est moi qui ai un problème avec ça, c'est un TOC...

Effectivement, ça ne change rien mais à la vitesse ou je change les données à l'intérieur, je vais vite atteindre les 10000.

De plus, je souhaiterais me servir de ce système dans une autre table qui ne contient que 10 lignes ou l'auto_increment est utilisé comme identifiant cette fois-ci.
16/12/2020, 12h06
SQLpro

10000 en combien de temps ?
Sachant que la limite est dans le type INTEGER de 2 milliards, si vous insérez 10000 ligne par seconde (nuit et jour), il vous restera 200000 secondes soit 55 jours.
Si vous optez pour le type BIGINT soit une limite de 9223372036854775807, si vous insérez 10000 par milliseconde (si MySQmerde était capable de cette vitesse d'insertion, ça se saurait...), soit 10000000 lignes par seconde, il vous restera 10*675*199 jours soit 29 227 ans...

A +
16/12/2020, 13h52
Trebor

En à peine 1 mois, ma base de donnée est en phase de test avant qu'on la passe en version opérationnelle, ensuite, cela sera un peu plus long mais cela va aller assez vite.

Comme je le disais, je vais me servir de ce système de "10 lignes" dans une autre table, c'est pour cela que j'en ai également besoin.

Les différentes informations que j'ai glanés sur le net me laisse penser qu'il n'y a pas de commande dédiée à cette opération.

J'ai vu qu'il était possible d'envisager le faire en PHP mais je n'arrive pas à visualiser les différentes opérations que je vais avoir à mettre en place.

Salut à tous.

Voici un exemple où l'on tri la colonne 'mess' avec renumérotation de la colonne 'id' :

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
--------------
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 auto_increment Primary key,
   `mess`    varchar(255)       NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
--------------
 
--------------
INSERT INTO `test` (`id`,`mess`) VALUES
  ( 25, 'trois'),
  ( 50, 'un'),
  (100, 'cinq'),
  (200, 'deux'),
  (500, 'quatre')
--------------
 
--------------
select * from  `test`
--------------
 
+-----+--------+
| id  | mess   |
+-----+--------+
|  25 | trois  |
|  50 | un     |
| 100 | cinq   |
| 200 | deux   |
| 500 | quatre |
+-----+--------+
--------------
ALTER TABLE `test` DROP COLUMN `id`
--------------
 
--------------
describe `test`
--------------
 
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| mess  | varchar(255) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
--------------
ALTER TABLE `test` ORDER BY `mess`
--------------
 
--------------
select * from  `test`
--------------
 
+--------+
| mess   |
+--------+
| cinq   |
| deux   |
| quatre |
| trois  |
| un     |
+--------+
--------------
ALTER TABLE `test` ADD COLUMN `id` integer unsigned auto_increment not null primary key first
--------------
 
--------------
describe `test`
--------------
 
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int unsigned | NO   | PRI | NULL    | auto_increment |
| mess  | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
--------------
select * from  `test`
--------------
 
+----+--------+
| id | mess   |
+----+--------+
|  1 | cinq   |
|  2 | deux   |
|  3 | quatre |
|  4 | trois  |
|  5 | un     |
+----+--------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Je vous communique votre demande, mais a priori, cela ne sert strictement à rien.
Ce n'est pas bien grave d'avoir des trous dans la numérotation d'un colonne technique.

En utilisant "integer unsigned", vous avez la possibilité d'avoir plus de 4 milliard de lignes (4 294 967 295).
Vous serez mort bien avant d'atteindre cette limite.

Et si cela ne vous suffit pas, pourquoi ne pas mettre "bigint unsigned", avec 2⁶⁴ - 1 (18 446 744 073 709 551 615).

--> https://dev.mysql.com/doc/refman/8.0...ger-types.html

@+

17/12/2020, 13h02
escartefigue

Bonjour,

Le plus souvent, les colonnes IDENTITY (auto_increment) sont utilisée comme PK.
Ce faisant, on retrouve leurs valeurs dans d'autres tables en tant que FK.

Donc, sauf si la table en question est orpheline, pas question de renuméroter les identifiants sans propager cette renumérotation dans les tables liées.
Vu les volumes annoncés, c'est une opération qui peut être extrêmement lourde.

La méthode la plus simple et la plus sécurisée est donc bien d'utiliser un bigint dans chaque table concernée.
18/12/2020, 11h43
Trebor

Citation:

Envoyé par SQLpro

10000 en combien de temps ?
Sachant que la limite est dans le type INTEGER de 2 milliards, si vous insérez 10000 ligne par seconde (nuit et jour), il vous restera 200000 secondes soit 55 jours.
Si vous optez pour le type BIGINT soit une limite de 9223372036854775807, si vous insérez 10000 par milliseconde (si MySQmerde était capable de cette vitesse d'insertion, ça se saurait...), soit 10000000 lignes par seconde, il vous restera 10*675*199 jours soit 29 227 ans...

A +

D'accord, merci de votre réponse. Mais sinon, est-il possible de réinitialiser une colonne de type INTEGER avec une option auto_increment en laissant à l'intérieur les données déjà existantes en ne renouvelant de ce fait que les valeurs de la colonne INTEGER ?
18/12/2020, 11h49
Trebor

Citation:

Envoyé par Artemus24

Je vous communique votre demande, mais a priori, cela ne sert strictement à rien.
Ce n'est pas bien grave d'avoir des trous dans la numérotation d'un colonne technique.

Merci,

Je vais tester ça et je vous tiens au courant.

J'ai bien compris le principe, les risques mais dans ce cas précis, les tables sont orphelines, l'ID est un identifiant technique mais j'envisage de m'en servir comme "compteur" de ligne. (je ne voyais pas quel terme employer à la place)

Encore merci de votre réponse qui va m'être bien utile.
18/12/2020, 17h27
CinePhil

Vouloir réinitialiser un auto-incrément est une très mauvaise idée pour les raisons évoquées par escartefigue.

Le seul cas envisageable est lorsque que vous passez votre BDD de test en production mais alors il faut le faire proprement en recréant la BDD vierge à partir d'un script SQL et charger les données de base dans le bon ordre (d'abord les tables mères sans clés étrangères puis les tables filles avec clés étrangères).

Et pour compter les lignes, utilisez une requête SELECT COUNT(*) .... Pas besoin d'une colonne de comptage qui a toutes les chance de ne plus être à jour dès qu'il y aura eu un enchaînement d'insertions et de suppressions.

Ah ! Et autre chose maintenant que je relis votre message d'origine :

Citation:

J'ai plusieurs tables ayant cette structure (je parle de la structure, pas de ce qu'il y a dedans)

id_auto (auto_increment)
nom
num_stagiaire

Si vous avez vraiment plusieurs tables qui ont ces colonnes là, c'est que votre BDD est mal modélisée !
Commencez par modéliser correctement votre base de données !

Salut à tous.

Entièrement d'accord avec Escartefigue.

L'exemple que j'ai donné concerne bien la renumérotation d'une table orpheline.

Il a été aussi soulevé le temps d'exécution de cette renumérotation.
Cela peut prendre plusieurs heures selon la taille de la table.
Ce n'est donc pas une manipulation anodine et sans conséquence.

Voici un autre exemple avec une table mère et une table fille qui sont reliée par une clef étrangère.
La modification de la clef primaire de la table mère est faite d'une manière totalement différente.
Sachant que je ne peux pas retrouver les mêmes valeurs que je vais insérer dans la colonne "id", je peux me permettre de le faire.

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

Comme on peut le constater, la propagation s'est faite. Pourquoi ?
A cause de cette déclaration (ce que j'ai mis en rouge) :

Code:

CONSTRAINT `fk_fille_clef` FOREIGN KEY (`clef`) REFERENCES `mere` (`id`) ON DELETE CASCADE ON UPDATE CASCADE