BIGINT auto incrémentation full

Version imprimable

29/06/2018, 06h20
nniikkoo

BIGINT auto incrémentation full

Bonjour,

Pouvez-vous me dire svp comment le système gère l'auto incrémentation lorsqu'il arrive à la fin que cela soit un bigint, un int,un tinyint, etc.

Par exemple pour une colonne tinyint avec PK AI UNSIGNED
si j'ai supprimé plus de 60 lignes et que j'arrive à la ligne 255, comment le système gère la ligne suivante ? le système plante ?

Pour ceux qui vont me dire de passer sur un type de colonne supérieur :roll:, je vous pose exactement la même question avec un bigint :mrgreen:
si j'ai supprimé plus de xxx lignes et que j'arrive à la dernière ligne possible en auto incrémentation, comment le système gère la ligne suivante ?

Merci pour vos réponses.
29/06/2018, 09h09
escartefigue

Contrairement à d'autres SGBD, MySQL ne permet pas de choisir le comportement quand la limite de numérotation est atteinte.
En l'occurrence, l'insertion de la nouvelle ligne se plante

C'est la raison pour laquelle le choix du type d'identifiant (smallint, int, bigint) est important, il faut tenir compte du taux de croissance des différentes tables sur la durée

Certains SGBD permettent de choisir entre plantage et retour à la valeur la plus petite. Ce qui permet de boucler sous réserve bien sur que les lignes correspondantes aux id à réutiliser aient été supprimées ;)

Extrait du manuel de référence MySQL 8.0 :
Use the smallest integer data type for the AUTO_INCREMENT column that is large enough to hold the maximum sequence value you will need. When the column reaches the upper limit of the data type, the next attempt to generate a sequence number fails. Use the UNSIGNED attribute if possible to allow a greater range.
29/06/2018, 09h16
nniikkoo

Merci pour les précisions.
Du coup il est possible de faire évoluer dans le temps un INT en BIGINT ci besoin ? Ou est il préférable d'y songer à la création de la table ?
29/06/2018, 09h36
escartefigue

Faire évoluer le type d'une colonne auto_incrémentée est compliqué : ces colonnes sont le plus souvent des PK, elles sont donc propagées dans les tables liées via les contraintes de type REFERENCE, et les impacts sont donc lourds. C'est bien plus simple de prendre le bon type de données dès le début.

Salut à tous.

Citation:

Envoyé par nniikkoo

Pouvez-vous me dire svp comment le système gère l'auto incrémentation lorsqu'il arrive à la fin que cela soit un bigint, un int,un tinyint, etc.

Le mieux est de faire le test :

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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`   tinyint unsigned  not null auto_increment primary key,
  `lib`  varchar(255)      not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`id`,`lib`) VALUES (252,'un')
--------------
 
--------------
INSERT INTO `test`      (`lib`) VALUES  ('deux')
--------------
 
--------------
INSERT INTO `test`      (`lib`) VALUES  ('trois')
--------------
 
--------------
INSERT INTO `test`      (`lib`) VALUES  ('quatre')
--------------
 
ERROR 1062 (23000) at line 36: Duplicata du champ '255' pour la clef 'PRIMARY'
--------------
INSERT INTO `test`      (`lib`) VALUES  ('cinq')
--------------
 
ERROR 1062 (23000) at line 37: Duplicata du champ '255' pour la clef 'PRIMARY'
--------------
select * from `test`
--------------
 
+-----+-------+
| id  | lib   |
+-----+-------+
| 252 | un    |
| 253 | deux  |
| 255 | trois |
+-----+-------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Citation:

Envoyé par nniikkoo

comment le système gère la ligne suivante ? le système plante ?

Le serveur mysql ne va pas planter, mais cela provoque une erreur comme on peut le voir ci-dessus.

Citation:

Envoyé par nniikkoo

Du coup il est possible de faire évoluer dans le temps un INT en BIGINT ci besoin ?

Comment gérer la colonne auto incrémenté ?
Admettons que c'est juste un identifiant technique qui ne sert pas comme clef étrangère.
Dans ce cas là, vous pouvez bidouiller la colonne en faisant ainsi :

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
--------------
select * from `test`
--------------
 
+-----+-------+
| id  | lib   |
+-----+-------+
| 252 | un    |
| 253 | deux  |
| 255 | trois |
+-----+-------+
--------------
commit
--------------
 
--------------
describe `test`
--------------
 
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| lib   | varchar(255)        | NO   |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
--------------
alter table `test` drop column `id`
--------------
 
--------------
alter table `test` add column `id` integer unsigned not null auto_increment primary key first
--------------
 
--------------
describe `test`
--------------
 
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| lib   | varchar(255)     | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
--------------
commit
--------------
 
--------------
INSERT INTO `test`      (`lib`) VALUES  ('quatre')
--------------
 
--------------
INSERT INTO `test`      (`lib`) VALUES  ('cinq')
--------------
 
--------------
select * from `test`
--------------
 
+----+--------+
| id | lib    |
+----+--------+
|  1 | un     |
|  2 | deux   |
|  3 | trois  |
|  4 | quatre |
|  5 | cinq   |
+----+--------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

29/06/2018, 10h23
skuatamad

Citation:

Envoyé par Artemus24

Dans ce cas là, vous pouvez bidouiller la colonne en faisant ainsi :

Pas besoin de supprimer la colonne pour la recréer, il faut utiliser modify :
ALTER TABLE Syntax
29/06/2018, 10h57
Artemus24

Salut skuatamad .

Oui, on peut juste modifier la colonne et non la détruire comme je l'ai fait.
Il existe une autre solution qui consiste à renuméroter la colonne, sachant qu'il y a des trous.

Mais bon, quand on se pose ce genre de question, c'est qu'il y a une mauvaise modélisation dès le départ.
Surtout si la colonne auto incrémenté est en bigint unsigned.
Je rappelle que cela fait 2^64 - 1, soit : 18 446 744 073 709 551 615.
En admettant que vous insérez toutes les secondes, 1000 lignes, cela fait 584 542 046 années.
Votre ordinateur sera au rebut bien avant cette durée.

@+