Discussion :

[borgnefesse]

Bonjour.

Voici en quelques mots mon problème et son contexte. Excusez par avance les termes utilisés qui sont ceux d'un néophyte.

J'ai 2 tables:
table 1
table 2 avec une clé étrangère en référence à la clé primaire de table 1

Je 'nourris' mes tables avec des données qui proviennent de 2 sources différentes.
Malheureusement, pour quelques données (9 enregistrements sur 315000) la clé primaire n'existe pas dans la table 1 quand j'enregistre les données dans la table 2.
Ceci m'oblige à utiliser : SET foreign_key_checks = 0;
avant d'insérer les données dans la table 2 (LOAD DATA LOCAL INFILE...)

Je voudrais savoir ce que je risque à procéder ainsi.
J'ai quelques millions d'enregistrements à insérer, ça prend un peu de temps donc j'aimerais éviter de trop le perdre...

Quand j'ouvre la table 2 dans PHPMYADMIN, seuls 130000 enregistrement sont indiqués mais avec une requête je retrouve mes 315000 enregistrements (idem quand j'actualise, voir images ci-dessous)
A-priori je ferai plutôt des restrictions dans la table 1 pour afficher les données de la table 2, donc les enregistrements de la table 2 qui n'ont pas de correspondance dans la table 1 seront inutilisables mais je peux me le permettre dans les proportions actuelles (0.003%).
Le plus important pour mon usage est la rapidité d'exécution des requêtes dans une base volumineuse.

Merci d'avoir pris le temps de me lire. S'il vous en reste un peu pour m'éclairer, merci beaucoup!




En écrivant ce post la lumière m'est apparue (peut-être?) et aussi une question de plus...
Il ne me semble pas très long ni compliqué d'insérer les données manquantes dans la table 1 avec: LOAD DATA INFILE 'fichier.csv' IGNORE INTO TABLE table1
de manière à ce que les données de la table 1 ne soient pas remplacées par cette insertion quand elles existent déjà. J'ai bon?

[Michel.Priori]

Plusieurs choses:

1- MyISAM ne respecte pas les FK, InnoDB oui.
2- Pourquoi mettre une contrainte de FK si c'est pas pour la faire respecter ?
3- Les instructions INSERT ... ON DUPLICATE KEY UPDATE ou simplement MERGE traitent ce genre de problème.

[borgnefesse]

Merci pour ta réponse Michel.

1- Tu penses que je devrais utiliser MyISAM sachant que j'ai besoin de jointures dans mes requêtes? De mon point de vue (de néophyte), MyISAM est utilisé quand il n'y a pas de jointure et InnoDB quand il y en a. J'ai peut-être tout faux?

2- Réponse un peu similaire à la précédente: j'ai besoin de jointures puisque je souhaiterais utiliser les données de la table 2 en fonction de critères vérifiables dans la table 1. Peut-on réaliser des jointures dans les requêtes sans avoir au préalable défini une clé étrangère en lien avec la clé primaire d'une autre table?

3- J'utilise LOAD DATA INFILE parce que mes sources de données sont des fichiers csv volumineux. Je suis d'abord passé par l'interface d'administration PHPMyAdmin mais face au temps pris par l'exécution d'une insertion, j'ai cherché une autre solution.
LOAD DATA INFILE m'offre un gain de temps considérable. Je vais donc creuser les 2 pistes que tu proposes.

[Michel.Priori]

MyISAM est utilisé quand il n'y a pas de jointure et InnoDB quand il y en a. J'ai peut-être tout faux?

Confusion entre :
1-une FK
2- une jointure
Une jointure est une méthode pour "assembler" plusieurs sources de données entre elles.
Une FK est une contrainte, c'est à dire une règle qui ne peut être violée tant qu'elle existe.

Les jointures sont de la compétence du moteur SQL (donc valide quel que soit le moteur de stockage)
Les FK sont dépendantes du moteur de stockage. MyIsam n'implémente pas les FK, InnoDB oui.

InnoDB est un très bon moteur de stockage.
Le fait d'avoir différents moteurs de stockage pour la même base apporte une complexité supplémentaire.

Ce que je voulais signifier est : les FK sont optionnelles (la preuve MyIsam ...)

Dans le même temps, même si elles sont optionnelles, elles ne sont pas moins utiles.
Et là, vu les questions que tu me pose, j'ai l'impression qu'il te manque quelques notions pour juger de leur pertinence.
Globalement, vaut mieux les garder et faire avec.

2- Réponse un peu similaire à la précédente: j'ai besoin de jointures puisque je souhaiterais utiliser les données de la table 2 en fonction de critères vérifiables dans la table 1. Peut-on réaliser des jointures dans les requêtes sans avoir au préalable défini une clé étrangère en lien avec la clé primaire d'une autre table?

encore une fois les clés sont des contraintes sur les données, pas des axes de recherche.

3- J'utilise LOAD DATA INFILE parce que mes sources de données sont des fichiers csv volumineux. Je suis d'abord passé par l'interface d'administration PHPMyAdmin mais face au temps pris par l'exécution d'une insertion, j'ai cherché une autre solution.
LOAD DATA INFILE m'offre un gain de temps considérable. Je vais donc creuser les 2 pistes que tu proposes.

Tu peux passer par une table intermédiaire, à savoir une table qui reçoit les lignes du csv et à partir de laquelle on fait les insert dans les autres tables. En fin de traitement on supprime le contenu de la table pour la prochaine campagne. (ou création d'une table horodatée pour éviter les conflits si 2 imports sont concomitants)

La version 8 apporte justement la notion de table temporaire :
https://dev.mysql.com/doc/refman/8.0...ary-table.html

[Artemus24]

Salut à tous.

Malheureusement, pour quelques données (9 enregistrements sur 315000) la clé primaire n'existe pas dans la table 1 quand j'enregistre les données dans la table 2.
Ceci m'oblige à utiliser : SET foreign_key_checks = 0;
avant d'insérer les données dans la table 2 (LOAD DATA LOCAL INFILE...)

Non, ce n'est pas ainsi que l'on procède.
Pour ce faire, vous devez utiliser un identifiant technique (auto_increment) et l'utiliser si vous avez besoin de renseigner une clef étrangère.
Si maintenant, pour une raison indépendante de votre volonté mais une erreur que vous devez gérer, le mieux est alors de créer "unique index" sur votre ancienne clé primaire.
Chose bizarre, MySql autorise les multiples NULL dans un index unique.
A vous de les gérer afin de les transformer en valeur unique.

Il ne me semble pas très long ni compliqué d'insérer les données manquantes dans la table 1 avec: LOAD DATA INFILE 'fichier.csv' IGNORE INTO TABLE table1
de manière à ce que les données de la table 1 ne soient pas remplacées par cette insertion quand elles existent déjà. J'ai bon?

Comment voulez-vous insérer les données manquantent si vous ne les connaissez pas à l'avance ?

1- Tu penses que je devrais utiliser MyISAM sachant que j'ai besoin de jointures dans mes requêtes? De mon point de vue (de néophyte), MyISAM est utilisé quand il n'y a pas de jointure et InnoDB quand il y en a. J'ai peut-être tout faux?

Si vous avez des jointures, vous devez utiliser le moteur "InnoDB". Vous n'avez pas trop le choix.

2- Réponse un peu similaire à la précédente: j'ai besoin de jointures puisque je souhaiterais utiliser les données de la table 2 en fonction de critères vérifiables dans la table 1. Peut-on réaliser des jointures dans les requêtes sans avoir au préalable défini une clé étrangère en lien avec la clé primaire d'une autre table?

Comme dit précédemment, utilisez des identifiants techniques.

3- J'utilise LOAD DATA INFILE parce que mes sources de données sont des fichiers csv volumineux. Je suis d'abord passé par l'interface d'administration PHPMyAdmin mais face au temps pris par l'exécution d'une insertion, j'ai cherché une autre solution.
LOAD DATA INFILE m'offre un gain de temps considérable. Je vais donc creuser les 2 pistes que tu proposes.

L'avantage du "LOAD DATA INFILE" est de pouvoir automatiser le chargement des tables.
Mais je rappelle qu'une base de données n'est pas un tableau.
De ce fait, vous devez penser relation et éviter les duplications que l'on peut retrouver dans les tableurs.

Le mieux serait de nous communiquer le descriptifs de vos tables, ainsi que vos scripts "LOAD DATA INFILE".

Voici un exemple.
Au départ, j'ai créé une table 'trav' (de travail) pour insérer toutes les lignes en provenance de mon fichier de type ".csv" (comme le tableur).
Je ne vais pas stocker cette table car elle n'est pas en conformité avec les "norm form".
De ce fait, je vais créer des nouvelles tables afin d'éviter les répétitions inutiles.
La table 'père' (contiendra des nombres) possède déjà un existant que je vais complèter à partir de la table 'trav'.
La table 'mère' (contiendra les langues) possède aussi un existant que je vais complèter ) partir de la table 'trav'.
La table 'fille' (les traductions) va être construite à partir de la table 'père' et 'mère' et ce qui est spécifique à elle.
Tout ce qui n'est pas conforme va dans une table dite 'poubelle'.
A la fin, je reproduis par une requête, les mêmes relations que j'avais au départ.
Et comme je n'ai plus besoin de ma table de travail, je peux la détruire.

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
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
--------------
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 trav
--------------
 
--------------
CREATE TABLE `trav`
( `id`    integer unsigned not null auto_increment primary key,
  `col1`  varchar(255)         null,
  `col2`  varchar(255)         null,
  `col3`  varchar(255)         null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
LOAD DATA INFILE 'E:/30.MySql/40.Load/Ex_04/fichier.txt'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY ','
            OPTIONALLY ENCLOSED   BY '\"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 0 LINES
     (`col1`,`col2`,`col3`)
--------------
 
--------------
select * from trav
--------------
 
+----+--------+----------+--------------+
| id | col1   | col2     | col3         |
+----+--------+----------+--------------+
|  1 | quatre | Anglais  | four         |
|  2 | quatre | Allemand | vier         |
|  3 | quatre | Italien  | quattro      |
|  4 | trois  | anglais  | three        |
|  5 | cinq   | Allemand | fünf         |
|  6 | cinq   | Italien  | cinque       |
|  7 | cinq   | Anglais  | five         |
|  8 | NULL   | NULL     | vingt-quatre |
|  9 | six    | Anglais  | six          |
| 10 | six    | Allemand | sechs        |
| 11 | six    | Italien  | sei          |
| 12 | un     | Espagnol | uno          |
| 13 | deux   | Espagnol | dos          |
| 14 | trois  | Espagnol | tres         |
| 15 | quatre | Espagnol | cuatro       |
| 16 | cinq   | Espagnol | cinquo       |
| 17 | six    | Espagnol | seis         |
+----+--------+----------+--------------+
--------------
COMMIT
--------------
 
--------------
DROP TABLE IF EXISTS pere
--------------
 
--------------
CREATE TABLE pere
( `id`      integer unsigned not null auto_increment primary key,
  `nombre`  varchar(255)     not null,
  index `idx` (`nombre`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `pere` (`nombre`) value ('un'),('deux'),('trois')
--------------
 
--------------
select * from pere
--------------
 
+----+--------+
| id | nombre |
+----+--------+
|  2 | deux   |
|  3 | trois  |
|  1 | un     |
+----+--------+
--------------
insert into `pere` (`nombre`)
  select  distinct t.col1
    from  `trav` as t
   where  not exists (select 1 from `pere` as p where p.nombre = t.col1)
     and  t.col1 is not null
--------------
 
--------------
select * from pere
--------------
 
+----+--------+
| id | nombre |
+----+--------+
|  5 | cinq   |
|  2 | deux   |
|  4 | quatre |
|  6 | six    |
|  3 | trois  |
|  1 | un     |
+----+--------+
--------------
COMMIT
--------------
 
--------------
DROP TABLE IF EXISTS mere
--------------
 
--------------
CREATE TABLE mere
( `id`      integer unsigned not null auto_increment primary key,
  `langue`  varchar(255)     not null,
  index `idx` (`langue`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `mere` (`langue`) value ('Allemand'), ('Italien'), ('Anglais')
--------------
 
--------------
select * from mere
--------------
 
+----+----------+
| id | langue   |
+----+----------+
|  1 | Allemand |
|  3 | Anglais  |
|  2 | Italien  |
+----+----------+
--------------
insert into `mere`  (`langue`)
   select distinct  t.col2
              from  `trav` as t
  where not exists  (select 1 from `mere` as m  where m.langue = t.col2)
               and  t.col2 is not null
--------------
 
--------------
select * from mere
--------------
 
+----+----------+
| id | langue   |
+----+----------+
|  1 | Allemand |
|  3 | Anglais  |
|  4 | Espagnol |
|  2 | Italien  |
+----+----------+
--------------
COMMIT
--------------
 
--------------
DROP TABLE IF EXISTS fille
--------------
 
--------------
CREATE TABLE fille
( `id`          integer unsigned not null auto_increment primary key,
  `pere_id`     integer unsigned not null,
  `mere_id`     integer unsigned not null,
  `traduction`  varchar(255)     not null,
  unique index `idx` (`pere_id`,`mere_id`),
  CONSTRAINT `FK_PERE` FOREIGN KEY (`pere_id`) REFERENCES `pere` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_MERE` FOREIGN KEY (`mere_id`) REFERENCES `mere` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `fille` (`pere_id`,`mere_id`,`traduction`) value
  (1, 1, 'eins'), (2, 1, 'zwei'), (3, 1, 'drei'),
  (1, 2, 'uno'),  (2, 2, 'due'),  (3, 2, 'tre'),
  (1, 3, 'one'),  (2, 3, 'two'),  (3, 3, 'tree')
--------------
 
--------------
select * from fille
--------------
 
+----+---------+---------+------------+
| id | pere_id | mere_id | traduction |
+----+---------+---------+------------+
|  1 |       1 |       1 | eins       |
|  2 |       2 |       1 | zwei       |
|  3 |       3 |       1 | drei       |
|  4 |       1 |       2 | uno        |
|  5 |       2 |       2 | due        |
|  6 |       3 |       2 | tre        |
|  7 |       1 |       3 | one        |
|  8 |       2 |       3 | two        |
|  9 |       3 |       3 | tree       |
+----+---------+---------+------------+
--------------
insert into `fille` (`pere_id`,`mere_id`,`traduction`)
    select  p.id,
            m.id,
            t.col3
      from  `trav` as t
 
inner join  `pere` as p
        on  p.nombre = t.col1
 
inner join  `mere` as m
        on  m.langue = t.col2
 
     where  t.col3 is not null
 
        on  duplicate key update traduction = values(traduction)
--------------
 
--------------
select * from fille
--------------
 
+----+---------+---------+------------+
| id | pere_id | mere_id | traduction |
+----+---------+---------+------------+
|  1 |       1 |       1 | eins       |
|  2 |       2 |       1 | zwei       |
|  3 |       3 |       1 | drei       |
|  4 |       1 |       2 | uno        |
|  5 |       2 |       2 | due        |
|  6 |       3 |       2 | tre        |
|  7 |       1 |       3 | one        |
|  8 |       2 |       3 | two        |
|  9 |       3 |       3 | three      |
| 10 |       4 |       3 | four       |
| 11 |       4 |       1 | vier       |
| 12 |       4 |       2 | quattro    |
| 13 |       5 |       1 | fünf       |
| 14 |       5 |       2 | cinque     |
| 15 |       5 |       3 | five       |
| 16 |       6 |       3 | six        |
| 17 |       6 |       1 | sechs      |
| 18 |       6 |       2 | sei        |
| 19 |       1 |       4 | uno        |
| 20 |       2 |       4 | dos        |
| 21 |       3 |       4 | tres       |
| 22 |       4 |       4 | cuatro     |
| 23 |       5 |       4 | cinquo     |
| 24 |       6 |       4 | seis       |
+----+---------+---------+------------+
--------------
alter table `fille` drop   column `id`
--------------
 
--------------
alter table `fille` add    column `id` integer first
--------------
 
--------------
set @id:=0
--------------
 
--------------
update `fille` set id=(@id:=@id+1) order by mere_id, pere_id
--------------
 
--------------
alter table `fille` change column `id` `id` integer unsigned auto_increment primary key first
--------------
 
--------------
describe fille
--------------
 
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| pere_id    | int(10) unsigned | NO   | MUL | NULL    |                |
| mere_id    | int(10) unsigned | NO   | MUL | NULL    |                |
| traduction | varchar(255)     | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
--------------
select * from fille
--------------
 
+----+---------+---------+------------+
| id | pere_id | mere_id | traduction |
+----+---------+---------+------------+
|  1 |       1 |       1 | eins       |
|  2 |       2 |       1 | zwei       |
|  3 |       3 |       1 | drei       |
|  4 |       4 |       1 | vier       |
|  5 |       5 |       1 | fünf       |
|  6 |       6 |       1 | sechs      |
|  7 |       1 |       2 | uno        |
|  8 |       2 |       2 | due        |
|  9 |       3 |       2 | tre        |
| 10 |       4 |       2 | quattro    |
| 11 |       5 |       2 | cinque     |
| 12 |       6 |       2 | sei        |
| 13 |       1 |       3 | one        |
| 14 |       2 |       3 | two        |
| 15 |       3 |       3 | three      |
| 16 |       4 |       3 | four       |
| 17 |       5 |       3 | five       |
| 18 |       6 |       3 | six        |
| 19 |       1 |       4 | uno        |
| 20 |       2 |       4 | dos        |
| 21 |       3 |       4 | tres       |
| 22 |       4 |       4 | cuatro     |
| 23 |       5 |       4 | cinquo     |
| 24 |       6 |       4 | seis       |
+----+---------+---------+------------+
--------------
COMMIT
--------------
 
--------------
DROP TABLE IF EXISTS poubelle
--------------
 
--------------
CREATE TABLE poubelle
( `id`    integer unsigned not null auto_increment primary key,
  `col1`  varchar(255)         null,
  `col2`  varchar(255)         null,
  `col3`  varchar(255)         null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `poubelle` (`col1`,`col2`,`col3`)
    select  col1,
            col2,
            col3
      from  `trav`
 
     where  col1 is null
        or  col2 is null
--------------
 
--------------
select * from poubelle
--------------
 
+----+------+------+--------------+
| id | col1 | col2 | col3         |
+----+------+------+--------------+
|  1 | NULL | NULL | vingt-quatre |
+----+------+------+--------------+
--------------
COMMIT
--------------
 
--------------
drop table `trav`
--------------
 
--------------
select      p.nombre,
            m.langue,
            f.traduction
      from  `fille` as f
 
inner join  `pere` as p
        on  p.id = f.pere_id
 
inner join  `mere` as m
        on  m.id = f.mere_id
 
  order by  p.id, m.id
--------------
 
+--------+----------+------------+
| nombre | langue   | traduction |
+--------+----------+------------+
| un     | Allemand | eins       |
| un     | Italien  | uno        |
| un     | Anglais  | one        |
| un     | Espagnol | uno        |
| deux   | Allemand | zwei       |
| deux   | Italien  | due        |
| deux   | Anglais  | two        |
| deux   | Espagnol | dos        |
| trois  | Allemand | drei       |
| trois  | Italien  | tre        |
| trois  | Anglais  | three      |
| trois  | Espagnol | tres       |
| quatre | Allemand | vier       |
| quatre | Italien  | quattro    |
| quatre | Anglais  | four       |
| quatre | Espagnol | cuatro     |
| cinq   | Allemand | fünf       |
| cinq   | Italien  | cinque     |
| cinq   | Anglais  | five       |
| cinq   | Espagnol | cinquo     |
| six    | Allemand | sechs      |
| six    | Italien  | sei        |
| six    | Anglais  | six        |
| six    | Espagnol | seis       |
+--------+----------+------------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

@+

[borgnefesse]

Confusion entre :
1-une FK
2- une jointure
Une jointure est une méthode pour "assembler" plusieurs sources de données entre elles.
Une FK est une contrainte, c'est à dire une règle qui ne peut être violée tant qu'elle existe.

Les jointures sont de la compétence du moteur SQL (donc valide quel que soit le moteur de stockage)
Les FK sont dépendantes du moteur de stockage. MyIsam n'implémente pas les FK, InnoDB oui.

InnoDB est un très bon moteur de stockage.
Le fait d'avoir différents moteurs de stockage pour la même base apporte une complexité supplémentaire.

Ce que je voulais signifier est : les FK sont optionnelles (la preuve MyIsam ...)

Dans le même temps, même si elles sont optionnelles, elles ne sont pas moins utiles.
Et là, vu les questions que tu me pose, j'ai l'impression qu'il te manque quelques notions pour juger de leur pertinence.
Globalement, vaut mieux les garder et faire avec.

Merci Michel, ça clarifie pas mal de choses dans ma tête!

Tu peux passer par une table intermédiaire, à savoir une table qui reçoit les lignes du csv et à partir de laquelle on fait les insert dans les autres tables. En fin de traitement on supprime le contenu de la table pour la prochaine campagne. (ou création d'une table horodatée pour éviter les conflits si 2 imports sont concomitants)

OK. Je vais tester ça.

[borgnefesse]

Merci Artemus24.

Non, ce n'est pas ainsi que l'on procède.

Oui j'imagine bien que ce n'est pas une façon correcte de procéder, d'où mon post. J'ai utilisé cette méthode glanée sur le Web pour contourner une erreur.

Comment voulez-vous insérer les données manquantent si vous ne les connaissez pas à l'avance ?

Ma première source me fournit les données de la table1. Certaines sont manquantes puisque lorsque j'insère les données dans la table2 (fournies par ma source 2), certaines clés étrangères n'ont pas de correspondance avec les clés primaires de la table 1. Je pensais donc insérer les clés primaires (pas les autres données que je ne possède pas) dans la table 1 en utilisant les données de ma source 2 afin de respecter la contrainte d'intégrité quand j'insère ensuite les données dans la table 2.
Bien sûr je ne souhaite qu'insérer les clés primaire manquante et ignorer les enregistrements existants. D'où l'idée du: LOAD DATA INFILE 'fichier.csv' IGNORE INTO TABLE table1

Le mieux serait de nous communiquer le descriptifs de vos tables, ainsi que vos scripts "LOAD DATA INFILE".

Je me rends bien compte en essayant d'expliquer la situation que ce n'est pas très clair... Mon niveau et ma maîtrise du vocabulaire et des règles des bdd n'aident pas!
Mon projet ayant une finalité commerciale, je suis embêté de dévoiler mon idée sur un forum de développeurs. J'ai donc créé une base similaire dans un contexte différent.

Une source 1 me fournit les données pour remplir les tables 'famille' et 'membre'.
Une source 2 me fournit les données pour remplir une table 'document'.
Dans l'idéal les 2 sources devraient être parfaitement concordantes. Évidemment nous ne vivons pas dans un monde idéal et j'ai des documents qui n'appartiennent à aucune famille.

Voici mes tables:

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
table famille
+---------------+---------------------------+------+-----+---------+-------+
| Field         | Type                      | Null | Key | Default | Extra |
+---------------+---------------------------+------+-----+---------+-------+
| id            | int(12) unsigned zerofill | NO   | PRI | NULL    |       |
| date_creation | date                      | YES  |     | NULL    |       |
| sexe_chef     | varchar(2)                | YES  |     | NULL    |       |
| prenom_chef   | varchar(20)               | NO   |     | NULL    |       |
| nom_chef      | varchar(20)               | NO   |     | NULL    |       |
+---------------+---------------------------+------+-----+---------+-------+
 
table membre
+------------+---------------------------+------+-----+---------+-------+
| Field      | Type                      | Null | Key | Default | Extra |
+------------+---------------------------+------+-----+---------+-------+
| id         | int(18)                   | NO   | PRI | NULL    |       |
| id_fam     | int(12) unsigned zerofill | NO   | MUL | NULL    |       |
| nom        | varchar(20)               | NO   |     | NULL    |       |
| prenom     | varchar(20)               | NO   |     | NULL    |       |
| date_naiss | date                      | NO   |     | NULL    |       |
| voie       | varchar(3)                | NO   |     | NULL    |       |
| cp         | int(5)                    | NO   |     | NULL    |       |
| ville      | varchar(20)               | NO   |     | NULL    |       |
+------------+---------------------------+------+-----+---------+-------+
ALTER TABLE `mabase`.`membre` DROP INDEX `fk_membres`, ADD INDEX `fk_membres` (`id_fam`) USING BTREE;
 
table document
+--------+---------------------------+------+-----+---------+-------+
| Field  | Type                      | Null | Key | Default | Extra |
+--------+---------------------------+------+-----+---------+-------+
| id     | varchar(100)              | NO   | PRI | NULL    |       |
| id_fam | int(12) unsigned zerofill | NO   | MUL | NULL    |       |
| date   | date                      | NO   |     | NULL    |       |
| coda   | varchar(20)               | NO   |     | NULL    |       |
| codb   | varchar(20)               | NO   |     | NULL    |       |
| codc   | varchar(20)               | NO   |     | NULL    |       |
+--------+---------------------------+------+-----+---------+-------+
ALTER TABLE `mabase`.`document` DROP INDEX `fk_doc`, ADD INDEX `fk_doc` (`id_fam`) USING BTREE;

Pour insérer mes données respectivement dans les tables 'famille' puis 'membre', j'utilise ceci:

Code :

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

1
2
3
4
5
6
7
LOAD DATA LOCAL INFILE 'fichier_famille.csv' REPLACE INTO TABLE famille
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';
 
LOAD DATA LOCAL INFILE 'fichier_membre.csv' REPLACE INTO TABLE membre
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';

Puis pour les données de la table 'document':

Code :

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

1
2
3
4
5
SET foreign_key_checks = 0;
LOAD DATA LOCAL INFILE 'fichier_doc.csv' REPLACE INTO TABLE document
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';
SET foreign_key_checks = 1;

J'utilise le mot clé REPLACE parce que les données fournies par mes sources sont mises à jour et sont susceptibles d'être corrigées, ma base doit donc contenir les dernières données publiées.

Le but est d'obtenir les données de la table 'document' en fonction de critères définis dans les tables 'famille' et/ou 'membre'.
Un utilisateur de mon site Web doit avoir accès aux données rapidement après avoir entré ses critères.
Je dois par ailleurs insérer un nombre conséquent de données:
-20 000 000 d'enregistrements dans la table famille
-28 000 000 d'enregistrements dans la table membre
-un nombre pour l'instant indéterminé d'enregistrements dans la table document qui augmente d'environ 100 000 chaque mois

Pour ce faire, vous devez utiliser un identifiant technique (auto_increment) et l'utiliser si vous avez besoin de renseigner une clef étrangère.
Si maintenant, pour une raison indépendante de votre volonté mais une erreur que vous devez gérer, le mieux est alors de créer "unique index" sur votre ancienne clé primaire.
Chose bizarre, MySql autorise les multiples NULL dans un index unique.
A vous de les gérer afin de les transformer en valeur unique.

D'après ce que je comprends, je dois modifier ma table 'famille' comme ceci:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
explain famille;
+---------------+---------------------------+------+-----+---------+----------------+
| Field         | Type                      | Null | Key | Default | Extra          |
+---------------+---------------------------+------+-----+---------+----------------+
| id_tech       | int(12)                   | NO   | PRI | NULL    | auto_increment |
| id            | int(12) unsigned zerofill | YES  | UNI | NULL    |                |
| date_creation | date                      | YES  |     | NULL    |                |
| sexe_chef     | varchar(2)                | YES  |     | NULL    |                |
| prenom_chef   | varchar(20)               | NO   |     | NULL    |                |
| nom_chef      | varchar(20)               | NO   |     | NULL    |                |
+---------------+---------------------------+------+-----+---------+----------------+

Voici un exemple.
Au départ, j'ai créé une table 'trav' (de travail) pour insérer toutes les lignes en provenance de mon fichier de type ".csv" (comme le tableur).
Je ne vais pas stocker cette table car elle n'est pas en conformité avec les "norm form".
De ce fait, je vais créer des nouvelles tables afin d'éviter les répétitions inutiles.
La table 'père' (contiendra des nombres) possède déjà un existant que je vais complèter à partir de la table 'trav'.
La table 'mère' (contiendra les langues) possède aussi un existant que je vais complèter ) partir de la table 'trav'.
La table 'fille' (les traductions) va être construite à partir de la table 'père' et 'mère' et ce qui est spécifique à elle.
Tout ce qui n'est pas conforme va dans une table dite 'poubelle'.
A la fin, je reproduis par une requête, les mêmes relations que j'avais au départ.
Et comme je n'ai plus besoin de ma table de travail, je peux la détruire.

Merci pour cet exemple. Je vais essayer de le comprendre et de le mettre en œuvre.

[Artemus24]

Salut borgnefesse.

Avez-vous jetez un coup d'oeil à l'exemple que je vous ai communiqué ?

Je me rends bien compte en essayant d'expliquer la situation que ce n'est pas très clair... Mon niveau et ma maîtrise du vocabulaire et des règles des bdd n'aident pas!

Ne vous excusez pas, la communication est tout un art qui est difficile à maîtriser (avec l'orthographe aussi).
C'est pourquoi, je préfère de loin donner un script répondant à la demande de l'utilisateur, plutôt que de longue explication qui ne font que nous embrouiller l'esprit.

Dans l'idéal les 2 sources devraient être parfaitement concordantes.

Que vous ayez des absences d'informations n'est pas un problème en soit, à la condition de savoir comment les traiter par la suite.

... j'ai des documents qui n'appartiennent à aucune famille.

Dans ce cas, vous avez deux solutions possibles :
a) soit vous créez une ligne fictive dans la table famille, par exemple un dénomme "anonymous". et vous rattachez à celui-ci tous vos documents sans famille.
b) soit vous rejetez vos documents en les recyclant dans une table poubelle, afin de les traiter ultérieurement.

Le cas général sera fait, et il vous restera dans une phase corrective, à entreprendre un travail pour ce qui n'est pas conforme.
Soit la famille "anonymous" ou soit la table "poubelle". Je préfère gérer la table "poubelle" et ne mettre dans les tables que ce qui est conforme.
Autrement dit, votre phase de chargement se fera en deux temps, le cas général, et les corrections.

Prenez l'habitude d'utiliser des identifiants techniques :

Code :

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

`id` integer unsigned not null auto_increment primary key;

C'est sur ces identifants que vous devez faire pointer vos clefs étrangères.
Laissez ce nom 'id' pour bien identifier que c'est une clef technique.

Pour la table 'famille', quelques remarques :
a) "zerofill" ne sert que pour l'affichage. Au lieu d'afficher "1", vous préférez voir "0000001".
b) à quoi correspond votre date de création ? Est-ce la date à laquelle la ligne a été créé dans la table ?
Si c'est oui, il est inutile de la renseigner comme vous le faites. Il suffit d'indiquer dans votre table :

Code :

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

1
2
`create_date`  TIMESTAMP(6)           DEFAULT current_timestamp(6),
`update_date`  TIMESTAMP(6)           DEFAULT current_timestamp(6) ON UPDATE CURRENT_TIMESTAMP(6),

Il est inutile dans l'insert de préciser le nom de la colonne, cela se fera automatiquement, tout comme pour la colonne 'id'.
A chaque modification de la ligne, la colonne `update_date` se mettra automatiquement à jour.

Si c'est une information fonctionnelle, ne tenez pas compte de ce que je viens de dire.

c) en général, pour des colonnes de types alphanumériques, dont la longueur est inférieure à 10 caractères, on met plutôt char(n) au lieu de varchar(n).

d) comme vous ne connaissez pas à l'avance la longueur du prénom ou du nom, il faut mieux mettre varchar(255).

e) pour la création de la table, si vous utilisez que le français, ajoutez ceci :

Code :

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

1
2
3
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;

Le jeu de caractères sera du "latin1", c'est-à-dire de l'ISO_8859_01
--> https://fr.wikipedia.org/wiki/ISO/CEI_8859-1

Pour insérer mes données respectivement dans les tables 'famille' puis 'membre', j'utilise ceci:

C'est pas faux, mais c'est basique.
Par exemple, vous pouvez appliquer des vérifications ou des traitements sur vos colonnes :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
LOAD DATA INFILE 'E:/30.MySql/40.Load/Ex_02/fichier.txt'
     INTO TABLE `Cities`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY ','
            OPTIONALLY ENCLOSED   BY '\"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 0 LINES
     (@F1, @F2, @F3, @F4, Population, Latitude, Longitude, @F8)
     set Country = trim(@F1),
            City = trim(@F2),
      AccentCity = trim(@F3),
          Region = trim(@F4),
            Date = outils.chgdate(@F8);

La date est sous le format "JJ/MM/AAAA".
MySql utilise le type "date" mais son format est "YYYY-MM-JJ".
De ce fait, vous devez corriger votre date pour quelle soit exploitable sous MySql.
J'utilise une fonction toute personnelle pour corriger cela. Si vous ne savez pas comment faire, demandez-le moi.

Il arrive que la chaîne de caractères contiennent des blancs en début ou en fin de chaîne. La fonction "trim()" permet de les supprimer.

Le fichier que vous désirez charger doit être compréhensible par votre système d'exploitation.
Je suis sous windows, et donc je doit gérer des lignes au format windows.
Elles se terminent par "carriage return" (\n) et "line feed" (\n). Etes-vous sous windows ?

Pour ne pas perdre du temps à chaque insertion d'une ligne, il serait bon de désactiver les contrôles et de les rétablir par la suite. Entre, vous mettez votre "load data infile"

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
-- =============
-- Désactivation
-- =============
 
TRUNCATE votre_table;
 
set unique_checks      = 0;
set foreign_key_checks = 0;
set sql_log_bin        = 0;
 
alter table `votre_table` DISABLE KEYS;
 
commit;
 
...
 
-- ==========
-- Activation
-- ==========
 
alter table `votre_table` ENABLE KEYS;
 
set unique_checks      = 1;
set foreign_key_checks = 1;
set sql_log_bin        = 1;
 
commit;

N'utilisez pas le moteur "MyIsam" si vous utilisez des clefs étrangères, mais plutôt le moteur "InnoDB".

Pour la table 'membre', quelques remarques :

'id_fam' est une clef étrangère qui va pointer sur la table famille.
'nom' et 'prénom' devraient être plutôt en "varchar(255)".
Vous devriez externaliser les informations relatives à l'adresse dans une table à cet effet.
Et revoir le modèle associé aux adresses, aux codes postaux, et aux villes.
--> https://sqlpro.developpez.com/cours/normes/#L3

Un utilisateur de mon site Web doit avoir accès aux données rapidement après avoir entré ses critères.

Ceci est un problème de requête et d'index. Définir un index sur les colonnes qui sont dans la clause where.

D'après ce que je comprends, je dois modifier ma table 'famille' comme ceci:

Comme vous utilisez des clefs étrangères, ceux-ci doivent pointer sur des identifiants techniques. C'est plus optimum comme accès !

Merci pour cet exemple. Je vais essayer de le comprendre et de le mettre en œuvre.

L'exemple est un cas d'école, où il est nécessaire de compléter des tables existantes et d'éviter les répétions que l'on trouve dans les tableurs.

@+

[borgnefesse]

Bonjour,
J'ai appliqué la plupart des conseils et je suis assez satisfait du résultat.
Je charge mes fichiers csv avec LOAD DATA INFILE dans une table de travail puis j'utilise INSERT INTO ... WHERE EXISTS ... ON DUPLICATE KEY UPDATE ... pour remplir ma table de données conformes. J'utilise ensuite INSERT INTO ... WHERE NOT EXISTS ... pour remplir une table poubelle à traiter plus tard.

J'ai cependant encore plusieurs questions:

Le jeu de caractères sera du "latin1", c'est-à-dire de l'ISO_8859_01

1- Concernant le codage des caractères, vous me conseillez "latin1" au lieu d'utf8. Lorsque j'interroge ma base en utf8_general_ci, je peux demander WHERE nom="rené" ou WHERE nom="rene" j'obtiens bien tous les résultats dont le nom est RENE. Avec latin1 ça ne fonctionne pas avec WHERE nom="rené". Dans ma base tous les enregistrements sont en majuscule mais l'utilisateur n'est pas sensé le savoir donc je trouve utf8 bien pratique. Le codage des caractères étant un vaste sujet il y a très certainement des éléments que j'ignore qui devraient m'inciter à utiliser "latin1"?

2- Avant de remplir ma table de travail j'utilise TRUNCATE TABLE pour la vider. Vous le faites aussi dans un exemple mais utilisez également dans un autre exemple DROP TABLE IF EXISTS puis CREATE TABLE. Une méthode est-elle préférable?

3- Un détail m'interroge:
Quand j'ouvre ma base dans PHPMYADMIN après avoir rempli mes tables, un nombre de lignes approximatif est affiché (393931)


qui devient 397191 lorsque je clique dessus.


Quand j'ouvre ma table, le total est de 393931 lignes


mais une requête COUNT(*) m'indique bien 397191 lignes.

Dois-je m'inquiéter?

[Artemus24]

Salut Borgnefesse.

J'ai appliqué la plupart des conseils et je suis assez satisfait du résultat.

C'est bien, mais il faut poursuivre vos améliorations !

Je rappelle que la table "trav" sert à charger votre fichier ".csv" qui est un tableur.
Vous faites ensuite la répartition de vos données dans les bonnes tables, comme dans mon exemple.
Ce que vous ne pouvez pas charger dans vos tables, va dans la table poubelle pour subir un recyclage.
Si vous avez compris le principe du recyclage, vous avez compris l'essentiel de comment effectuer un chargement.

1- Concernant le codage des caractères, vous me conseillez "latin1" au lieu d'utf8.

J'utilise "latin1" car un caractère est codé dans un octet et cela me suffit pour faire ce que je fais.
Si vous avez besoin d'un jeu de caractères plus étendu, vous pouvez utiliser utf-8.
Sauf qu'un caractère est codé de 1 à 4 octets avec le charset "utf8mb4".

Lorsque j'interroge ma base en utf8_general_ci, je peux demander WHERE nom="rené" ou WHERE nom="rene" j'obtiens bien tous les résultats dont le nom est RENE. Avec latin1 ça ne fonctionne pas avec WHERE nom="rené".

Il faut dans ce cas utiliser "latin1_swedish_ci" :

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
--------------
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,
  `lib`  varchar(255)      not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_swedish_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`lib`) VALUES
  ('RENE'),('rené'),('rene'),('forêt'),('pré'),('pre')
--------------
 
--------------
select * from `test`
--------------
 
+----+-------+
| id | lib   |
+----+-------+
|  1 | RENE  |
|  2 | rené  |
|  3 | rene  |
|  4 | forêt |
|  5 | pré   |
|  6 | pre   |
+----+-------+
--------------
select  *  from  `test`  where  lib = 'rene'
--------------
 
+----+------+
| id | lib  |
+----+------+
|  1 | RENE |
|  2 | rené |
|  3 | rene |
+----+------+
--------------
select  *  from  `test`  where  lib = 'RENE'
--------------
 
+----+------+
| id | lib  |
+----+------+
|  1 | RENE |
|  2 | rené |
|  3 | rene |
+----+------+
--------------
select  *  from  `test`  where  lib = 'rené'
--------------
 
+----+------+
| id | lib  |
+----+------+
|  1 | RENE |
|  2 | rené |
|  3 | rene |
+----+------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Si vous utilisez que le français et l'anglais, autant utiliser le "latin1".
Mais si vous désirez vous mettre à l'arabe et au chinois, utilisez "utf-8".

Dans ma base tous les enregistrements sont en majuscule

Je ne vois pas pourquoi vous désirez tout mettre en majuscule. Laissez les en minuscule.

2- Avant de remplir ma table de travail j'utilise TRUNCATE TABLE pour la vider. Vous le faites aussi dans un exemple mais utilisez également dans un autre exemple DROP TABLE IF EXISTS puis CREATE TABLE. Une méthode est-elle préférable?

J'utilise le "create" pour créer une nouvelle table. Donc auparavant, elle n'existe pas.
Inversement, si j'utilise TRUNCATE pour la vider, elle doit nécessairement exister, non ?

Sinon, une petite différente. Le TABLESPACE n'est pas remis à zéro avec le TRUNCATE.

Dois-je m'inquiéter?

Non, ne vous inquitez pas. PhpMyAdmin sert juste à faire des manipulations quand on est débutant.
Quand on est un peu plus expérimenté, on utilise des scripts MySql, comme celui que je vous ai donné.
En ce qui me concerne, je n'utilise plus phpmyadmin, ou très peu.

La bonne façon de lire le nombre de lignes dans votre table est bien par le "count(*)".

@+