Continuer une numérotation avec un pas de 1 sans utiliser le auto_increment

Invité · 16/10/2019, 10h42

Bonjour,

Je dispose d'une BDD de 350 millions de lignes pour 70 champs (+/- 85 Go de données). La BDD contient des relevé stats issus d'ACP/AFC/ACM , identifiés par une id unique . L'id unique est un int(11). L'id max est plus ou moins à 352/354 millions.

Dans une autre table je dispose de 23 millions d'enregistrement pour 50 champs (+/- 5,5 Go de données). Cette fois les relevés stats sont identifiés par un id unique toujours de type auto_increment.

Pour des raisons de traçabilités, je dois injecter les 23 millions de lignes avec les 350. Le tout en continuant la numération en integer avec du +1 de la table des 350 millions et non pas en auto_increment, au risque d'ecraser des anciennes données.

J'insiste bien sur la nécessité du +1 , car je dois faire passer des moulinettes d'études stats par paquets de 1 millions. En cas de trou dans la numérotation le programme plante.

Quelle instruction SQL peut me permettre de continuer avec du +1 ? A l'image du rowid en Oracle SQL ?

Merci de m'aiguiller

**escartefigue** · 16/10/2019, 11h08

Envoyé par tanaka59

Pour des raisons de traçabilités, je dois injecter les 23 millions de lignes avec les 350. Le tout en continuant la numération en integer avec du +1 de la table des 350 millions et non pas en auto_increment, au risque d'ecraser des anciennes données.

Il y a méprise, vu que l'identifiant de la table cible, celle où il y a déjà 350 millions de lignes, est de type auto_incrément, l'ajout de nouvelles lignes va naturellement continuer à incrémenter les identifiants, il n'y aura aucun écrasement. Il suffit de ne pas communiquer la valeur de l'identifiant lors de l'insertion et c'est le SGBD qui attribuera cette valeur.

Envoyé par tanaka59

J'insiste bien sur la nécessité du +1 , car je dois faire passer des moulinettes d'études stats par paquets de 1 millions. En cas de trou dans la numérotation le programme plante.

Là c'est autre chose : l'auto_incrément ne garantit en rien l'absence de trous. Les trous peuvent exister en cas de DELETE d'une part (suppression physique de lignes et donc disparition des identifiants correspondants) et en cas d'insert non commité d'autre part (l'identifiant a été réservé, mais le rollback fait qu'il est perdu).
Il faut donc modifier le traitement pour qu'il accepte les trous de numérotation

Envoyé par tanaka59

Quelle instruction SQL peut me permettre de continuer avec du +1 ? A l'image du rowid en Oracle SQL ?

Si vous avez MySQL V8, vous pouvez utiliser la fonction OLAP SELECT ROW_NUMBER() OVER....

Invité · 16/10/2019, 11h31

Bonjour,

Envoyé par escartefigue

Il y a méprise, vu que l'identifiant de la table cible, celle où il y a déjà 350 millions de lignes, est de type auto_incrément, l'ajout de nouvelles lignes va naturellement continuer à incrémenter les identifiants, il n'y aura aucun écrasement. Il suffit de ne pas communiquer la valeur de l'identifiant lors de l'insertion et c'est le SGBD qui attribuera cette valeur.

Non justement :

> table des 350 millions = id un int(11) qui n'est pas un auto_increment
> table des 23 millions = id un int(11) en auto_increment

L'auto_increment sous MySQL comportant une gros risque de trou dans la raquette je veux me débarrasser de celui ci .

Envoyé par escartefigue

Là c'est autre chose : l'auto_incrément ne garantit en rien l'absence de trous. Les trous peuvent exister en cas de DELETE d'une part (suppression physique de lignes et donc disparition des identifiants correspondants) et en cas d'insert non commité d'autre part (l'identifiant a été réservé, mais le rollback fait qu'il est perdu).
Il faut donc modifier le traitement pour qu'il accepte les trous de numérotation

Justement je ne veux aucun trou . En somme , si id = 100 alors je veux 100 lignes avant . Et non pas un trou de 25 a 50 ... Quand je fais tourner une moulinette sur l'id avec un pas mathématique de 1 c'est le crash assuré ...

Envoyé par escartefigue

Si vous avez MySQL V8, vous pouvez utiliser la fonction OLAP SELECT ROW_NUMBER() OVER....

J'utilise wampserver avec MySQL : http://www.wampserver.com/ . D'après vous ce type de SGBD accepte t il le ROW_NUMBER ?

**escartefigue** · 16/10/2019, 15h32

En ce cas, il faut utiliser une solution à l'ancienne : une table chrono spécifique qui contient une ligne dont l'identifiant fonctionnel est le nom de la table et de la colonne concernées et qui possède un attribut pour stocker la dernière valeur utilisée.
Dans chaque transaction qui insère dans la table des 350 millions, il faut récupérer la dernière valeur du chrono dans la table chrono en verrouillant la ligne, insérer les lignes dans la table des 350 millions, mettre à jour le chrono dans la table chrono et commiter le tout.

**SQLpro** · 16/10/2019, 17h32

Et un simple :

Code :

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

1
2
3
INSERT INTO MaTableCible
SELECT (SELECT MAX(ClefCible) FROM Cible) + ROW_NUMBER() OVER(ORDER BY uneColonne), source.*
FROM   SOURCE

????

A +

**escartefigue** · 17/10/2019, 07h26

Envoyé par SQLpro

Et un simple :

Code :

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

1
2
3
INSERT INTO MaTableCible
SELECT (SELECT MAX(ClefCible) FROM Cible) + ROW_NUMBER() OVER(ORDER BY uneColonne), source.*
FROM   SOURCE

????

A +

Je l'avais proposé plus haut, mais requiert MySQL V8, les versions antérieures ne connaissent pas les fonctions OLAP

**Artemus24** · 16/10/2019, 18h04

Salut tanaka59.

Envoyé par tanaka59

L'id unique est un int(11). L'id max est plus ou moins à 352/354 millions.

Envoyé par tanaka59

Pour des raisons de traçabilités, je dois injecter les 23 millions de lignes avec les 350.

Vous avez largement assez de place pour la numérotation.
354.000.000 + 23.000.000 = 377.000.000

Avec le type integer, le maximum est de 2.147.483.647 si la colonne est signée, sinon 4.294.967.295 si la colonne n'est pas signée.

Envoyé par tanaka59

Le tout en continuant la numération en integer avec du +1 de la table des 350 millions et non pas en auto_increment, au risque d'ecraser des anciennes données.

Dans ce que vous dites, il y a un aberration. Auto incrementé et faire +1, c'est exactement la même.
Vous ne rsiquez pas d'écraser quoi que ce soit car la numérotation se fait à partir de la dernière valeur entrée dans la table.
Pour connaitre la valeur de l'auto_incrément, faites :

Code :

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

1
2
3
SELECT AUTO_INCREMENT as last_id
FROM   INFORMATION_SCHEMA.TABLES
WHERE  table_schema = 'base' and table_name = 'test';

Si vous insérez sans préciser la valeur de la colonne "primary key", elle sera augmenté de +1.

Envoyé par tanaka59

J'insiste bien sur la nécessité du +1 , car je dois faire passer des moulinettes d'études stats par paquets de 1 millions. En cas de trou dans la numérotation le programme plante.

Si vous ne faites que des insertions, mais pas de update et encore moins de delete, la numérotations sera sans trous.

Envoyé par tanaka59

Quelle instruction SQL peut me permettre de continuer avec du +1 ? A l'image du rowid en Oracle SQL ?

Ce n'est pas une instruction, mais le mécanisme de l'auto incrément, qui fonctionne très bien dans MySql.

Soit votre table "test" avec comme colonne "primary key" ceci :

Code :

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

`id` integer unsigned not null auto_increment primary key,

peu importe ce qui suit dans la table. Disons que ce sont des colonnes numérotés.

Lors de l'insertion, vous faites simplemnt :

Code :

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

insert into `test` (`col1`,`col2`,`col3`,...) values ('chaine1', valeur2, valeur3, ...);

Comme vous le constatez, je n'ai pas indiqué le nom de la colonne "id" car elle sera géré automatiquement par MySql.

Voici un exemple illustrant ce que j'affirme :

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
--------------
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`      integer 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` (`lib`) values
  ('un'), ('deux'), ('trois'), ('quatre'), ('cinq')
--------------
 
--------------
select * from test
--------------
 
+----+--------+
| id | lib    |
+----+--------+
|  1 | un     |
|  2 | deux   |
|  3 | trois  |
|  4 | quatre |
|  5 | cinq   |
+----+--------+
--------------
set @rang:=0
--------------
 
--------------
SELECT AUTO_INCREMENT into @rang
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'base' and table_name = 'test'
--------------
 
--------------
select @rang as 'rang'
--------------
 
+------+
| rang |
+------+
|    6 |
+------+
--------------
INSERT INTO `test` (`lib`) values
  ('dix'), ('onze'), ('douze'), ('treize'), ('quatorze'), ('quinze'), ('seize')
--------------
 
--------------
select * from test
--------------
 
+----+----------+
| id | lib      |
+----+----------+
|  1 | un       |
|  2 | deux     |
|  3 | trois    |
|  4 | quatre   |
|  5 | cinq     |
|  6 | dix      |
|  7 | onze     |
|  8 | douze    |
|  9 | treize   |
| 10 | quatorze |
| 11 | quinze   |
| 12 | seize    |
+----+----------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Remarque : Surtout, ne pas utiliser la fonction max(`id`) + 1 pour incrémenter la primary key car vous auriez un gros problème de performance !

Autre remarque : après chaque grappe d'insertion, faire un commit afin de valider vos insertions. Disons toutes les 1.000 lignes !

La question qu vous devez vous posez est comment vont se présenter ces 23 millions de lignes que vous désirez charger dans votre table ?
Est-ce un fichier plat ? Une autre table dans la même base de données ?
Une autre table dans une autre base de données du même serveur MySql ?
Une autre table dans une autre base de données d'un autre serveur MySql ?

@+

**Artemus24** · 16/10/2019, 18h33

Salut à tous.

La question est de savoir si cet identifiant auto incrémenté est utilisé par ailleurs.
Par exemple, dans l'usage d'une foreign key ?

Si la réponse est non, on peut faire une renumérotation de cette colonne afin de supprimer les éventuels trous !

Voici un exemple :

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
--------------
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 `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`    integer unsigned  NOT NULL auto_increment primary key,
  `val`   char(10)              NULL default NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`id`,`val`) values (25, 'un'),(33, 'deux'),(75, 'trois'),(207, 'quatre'),(349, 'cinq'),(711, 'six'),(813, 'sept')
--------------
 
--------------
select * from  `test`
--------------
 
+-----+--------+
| id  | val    |
+-----+--------+
|  25 | un     |
|  33 | deux   |
|  75 | trois  |
| 207 | quatre |
| 349 | cinq   |
| 711 | six    |
| 813 | sept   |
+-----+--------+
--------------
SELECT AUTO_INCREMENT as last_id
FROM   INFORMATION_SCHEMA.TABLES
WHERE  table_schema = 'base' and table_name = 'test'
--------------
 
+---------+
| last_id |
+---------+
|     814 |
+---------+
--------------
commit
--------------
 
--------------
alter table `test` drop column `id`
--------------
 
--------------
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(10) unsigned | NO   | PRI | NULL    | auto_increment |
| val   | char(10)         | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
--------------
commit
--------------
 
--------------
select * from  `test`
--------------
 
+----+--------+
| id | val    |
+----+--------+
|  1 | un     |
|  2 | deux   |
|  3 | trois  |
|  4 | quatre |
|  5 | cinq   |
|  6 | six    |
|  7 | sept   |
+----+--------+
--------------
SELECT AUTO_INCREMENT as last_id
FROM   INFORMATION_SCHEMA.TABLES
WHERE  table_schema = 'base' and table_name = 'test'
--------------
 
+---------+
| last_id |
+---------+
|     814 |
+---------+
--------------
alter table `test` auto_increment = 10
--------------
 
--------------
commit
--------------
 
--------------
insert into `test` (`val`) values ('dix'),('onze'),('douze')
--------------
 
--------------
select * from  `test`
--------------
 
+----+--------+
| id | val    |
+----+--------+
|  1 | un     |
|  2 | deux   |
|  3 | trois  |
|  4 | quatre |
|  5 | cinq   |
|  6 | six    |
|  7 | sept   |
| 10 | dix    |
| 11 | onze   |
| 12 | douze  |
+----+--------+
--------------
COMMIT
--------------
 
 
Appuyez sur une touche pour continuer...

La numérotation est hasardeuse (colonne `id`).
On fait une renumérotation de 1 en 1.
La valeur maximale de l'auto increment n'a pas changé. Mais ce n'est pas grave.

On modifie cette valeur, qui dans cet exemple, sera mis à 10. Puis on poursuit l'insertion.
On remarque que l'insertion des trois dernières lignes se sont faites à partir de 10, comme on le désirait.

Envoyé par Escartefigue

Si vous avez MySQL V8, vous pouvez utiliser la fonction OLAP SELECT ROW_NUMBER() OVER....

Il n'est pas nécessaire de passer en V8 pour réaliser une renumérotation de la "primary key".

Envoyé par Tanaka59

L'auto_increment sous MySQL comportant une gros risque de trou dans la raquette je veux me débarrasser de celui ci .

Ce n'est pas l'auto incrément qui crée des trous. Mais les rollback, les delete, voire aussi des updates sur la "primary key".
Je vous assure qu'il n'y a aucun risque avec la colonne auto incrémenté.

Envoyé par Tanaka59

> table des 350 millions = id un int(11) qui n'est pas un auto_increment
> table des 23 millions = id un int(11) en auto_increment

Que désirez vous obtenir au final ?

> table des 350 millions = id un int(11) unsigned qui soit auto_increment

Avec renumérotation de toutes vos lignes, sans trous !

L'auto incrément fonctionne très bien ! Pourquoi ne pas l'utiliser ?

J'aimerai que Tanaka59 réponde à ma question de mon précédent message.
Comment se présente les 23 millions de données ?

@+

Invité · 16/10/2019, 20h37

Bonsoir

Envoyé par Artemus24

La question que vous devez vous posez est comment vont se présenter ces 23 millions de lignes que vous désirez charger dans votre table ?

J'ai 2 possibilités .

Envoyé par Artemus24

Est-ce un fichier plat ? Une autre table dans la même base de données ?

Ou je pars d'un fichier CSV ou bien je part d'une autre table . Au préalable , je dois harmoniser la structure des colonnes du paquet de 23 millions sur celui des 350.

Envoyé par Artemus24

Une autre table dans une autre base de données du même serveur MySql ?

Si je pars d'une table tout à fait, on est sur la même base et le même schéma.

Envoyé par Artemus24

.

Que désirez vous obtenir au final ?

> table des 350 millions = id un int(11) unsigned qui soit auto_increment

Avec renumérotation de toutes vos lignes, sans trous !

Au départ quand j'ai commencé à faire la base en question , je récupérai des fichiers CSV fait depuis SAS guide. J'utilisai des fonctions / proc sur SAS pour calculer moi même l'id. N'ayant pas à disposition l'outil sas pour le moment c'est un peu plus chiant ... sans les row_number également .

Je vais me pencher dans un premier temps sur un échantillon test au lieu de faire sur toute la base et que tout ... pète

Envoyé par Artemus24

L'auto incrément fonctionne très bien ! Pourquoi ne pas l'utiliser ?

Sur une table plus petite ou je stocke de la donnée. 370911 lignes unique avec un id max à ... roulement de tambour : 431257 . Je n'ai jamais vidé la table, je n'ai jamais rien supprimé ...

d’où mon étonnement.

Envoyé par Artemus24

Comment se présente les 23 millions de données ?

1 colonnes avec un id Auto_increment
+/- 50 à 60 colonnes avec des données numériques ou bien texte

**Artemus24** · 17/10/2019, 15h04

Salut à tous.

@ Tanaka59 : le mieux est de faire cela étape par étape et non en une seule fois.

Envoyé par Tanaka59

Ou je pars d'un fichier CSV ou bien je part d'une autre table . Au préalable , je dois harmoniser la structure des colonnes du paquet de 23 millions sur celui des 350.

On peut très bien charger votre fichier CSV dans une table de travail. Cela ne pose aucune difficulté.
Comme vous dites, vous devez harmoniser la structure des colonnes de vos 23 millions de lignes.
Ce point va dépendre de vos interventions et peut prendre beaucoup de temps.
Admettons que le résultat final sera une table dite définitive et au norme de celle contenant vos 350 millions de lignes.

Envoyé par Tanaka59

Si je pars d'une table tout à fait, on est sur la même base et le même schéma.

C'est ce qu'il y a de plus simple à faire.
Pour vos tests avant d'effectuer l'insertion définitive dans vos tables de productions, le mieux est de créer un environnement de test à l'dientique de ce que vous allez faire.

Envoyé par Tanaka59

Au départ quand j'ai commencé à faire la base en question , je récupérai des fichiers CSV fait depuis SAS guide.

Comme dit plus haut, cela ne pose aucun problème pour charger votre fichier CSV dans une table de travail, à partir de "load data infile".

Envoyé par Tanaka59

J'utilisai des fonctions / proc sur SAS pour calculer moi même l'id.

Ce n'est pas nécessaire car sous MySql, on peut faire commencer l'incrément à une valeur particulière et mettre le pas que l'on veut.
Par exemple commencer à 100 par pas de 5.

Envoyé par Tanaka59

... sans les row_number également .

Bien que j'ai fait jadis du DB2 gros système (voire aussi IDS II sous BULL), je n'ai jamais utilisé ce genre de numérotation.
C'est comme le GOTO en programmation, on peut très bien s'en passer.

Envoyé par Tanaka59

Je vais me pencher dans un premier temps sur un échantillon test au lieu de faire sur toute la base et que tout ... pète

Vous aimez vivre dangereusement !!!

Commencez par créer un environnement de test.
Copier votre base de données de production, enfin ce qui est utile dans votre environnement de test.
Si vous avez la possibilité, faites une sauvegarde de votre environnement de test, au cas où vous auriez besoin de repartir à zéro.

Comme dit précédemment, on peut sur la table des 3250 millions de lignes de faire une renumérotation, s'il existe des trous.
Mais a-t-on le droit de le faire ? A savoir si cet clef primaire ne sert pas comme référence dans votre base.
Par exemple pour des clefs étrangères. Si ce n'est pas le cas, alors on peut se permettre de renuméroter cet clef primaire. Voire mon exemple !

Ensuite, vous devez connaitre la dernière valeur de votre clef primaire. Puis ajouter +1 à celui-ci afin de modifier la valeur de l'auto increment :

Code :

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

alter table `test` auto_increment = 10

Ici, la valeur estde 10 car la prochaine insertion aura cette valeur pour la clef primaire.

Et enfin, effectuer l'insertion de masse.
On a le choix entre un insert associé à un select.
Sauf que cela risque de coincer avec 23 millions de lignes.
Je préfère faire cela par paquet disons de 1.000 lignes.
Dans ce cas, un procédure stockée peut gérer cela en faisant à chaque fois un "commit" pout la validation.
On peut même stocker la valeur qui est utilisé lors de l'incrément.
Ce qui fait, en cas d'un platage ou je ne sais quoi d'autre de repartir du dernier point de sauvegarde.

Envoyé par Tanaka59

Sur une table plus petite ou je stocke de la donnée.

C'est la solution d'Escartefigue et je la déconseille.
Regadez ma solution et vous verrez que je modifie la valeur de stockage de l'auto incrément.
Lors des insertions, c'est automatique. C'est MySql qui va s'en charger.
Pourquoi réinventer ce qui existe déjà ?

Envoyé par Tanaka59

370911 lignes unique avec un id max à ... roulement de tambour : 431257 .

Ben oui, des trous, ça arrive, mais cela ne pose en général aucun problème.

Envoyé par Tanaka59

Je n'ai jamais vidé la table, je n'ai jamais rien supprimé ... d’où mon étonnement.

C'est un fonctionnement qui déconcerte plus d'une personne sur le fonctionnement de l'auto incrément.
D'où des tas de questions sur comment éviter d'avoir des trous.

Je suis prêts à vous fournir des exemples selon les problématiques dque vous allez rencontrer.

@+