Discussion :

[philpus256]

Bonjour,
j'ai un problème à mon avis assez simple mais je ne sais pas comment le formuler vu que je ne pratique pas souvent le mysql

j'ai une table :

Code :

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

1
2
3
4
5
CREATE TABLE computers (
id int(11)	AUTO_INCREMENT,
computername char(255) DEFAULT NULL,
PRIMARY KEY (`id`,`computername`)
)

une autre :

Code :

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

1
2
3
4
5
6
CREATE TABLE evolution (
id int(11)	AUTO_INCREMENT,
computername_id int(11)	DEFAULT NULL,
username_id int(11) DEFAULT NULL,
TargetUrl varchar(255) DEFAULT NULL
)

ces tables sont vides
je dois les peupler à partir d’enregistrements fait à partir de fichiers de commandes SQL qui arrivent au fil de l'eau

par exemple la commande d'insertion :

Code :

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

1
2
3
4
INSERT INTO evolution (computername_id,username_id,TargetUrl) VALUES  (
    '(SELECT id FROM computers WHERE computername=$computername) as computername_id',
    '(SELECT id FROM users WHERE username=$username) as username_id',
    'TargetUrl')

mon problème est que je ne sais pas formuler sur une seule requête l'obtention du code id car ma commande ne marche pas si computername n'existe pas (vu que la 1ere fois il n'y a pas d'enregistrements dans la table computers)

merci pour des éclairciseements

[Golgotha]

Bonjour,

Dans une procédure stocké ça serait plus simple non ?

[escartefigue]

Bonjour,

Le principe d'une colonne de type "auto_incrément" est de déléguer au moteur de la base de données l'attribution de la valeur de cette colonne.
On peut forcer tout de même cette valeur, mais c'est bien plus pratique de ne pas s'en préoccuper.
Pour une PK, ce type de colonne est idéal car stable (c'est une valeur technique sans signification particulière) et concis donc performant.
Inutile donc d'encombrer votre PK d'une autre colonne comme vous l'avez fait. Le chrono seul est par construction unique (qui dit PK dit unique ), ajouter une colonne qui plus est de type varchar est inutile et contre-performant.

Le DDL de votre première table devient donc :

Code :

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

1
2
3
4
5
CREATE TABLE computers_CO (
 CO_id int(11) AUTO_INCREMENT,
 CO_computername char(255) DEFAULT NULL,
 PRIMARY KEY (`CO_id`,)
 )

Notez l'ajout du préfixe, ici "CO", dans les noms de colonne.
Cette astuce est bien pratique pour retrouver l'origine des informations et évite de tomber dans le piège des noms réservés SQL

Votre deuxième table n'a pas de PK peut être n'avez vous pas communiqué le DDL complet ?
Si "évolution" est une table qui enregistre des évolutions de "computer", alors il s'agit de ce qu'on appelle une "entité-type faible" : l'évolution n'existe que si le computeur existe.
En ce cas il faut identifier l'évolution relativement au computer, ça signifie que la PK de la table évolution est composée de la PK de computer + un identifiant propre
Le DDL de votre deuxième table devient donc :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE evolution_EV (
 CO_id          int(11) 
 EV_id          small(04)     AUTO_INCREMENT,
 EV_username_id int(11)       DEFAULT NULL,
 EV TargetUrl   varchar(255)  DEFAULT NULL
 PRIMARY KEY (`CO_id`, `EV_id`)
 
 Constraint FK1_ref_computer 
            foreing key (CO_id)            
            references computer(CO_id)         
            on update cascade
            on delete cascade
 )

Notez la contrainte d'intégrité qui permet de vérifier que la colonne CO_id de la table "evolution", possède une valeur qui existe dans la table "computer"
Cette contrainte permet également, si on le souhaite, propager les modifications faites dans "computer" vers "evolution" (instructions ON DELETE et ON UPDATE)


Ensuite,
- pour insérer dans la première table, il ne faut pas déclarer la colonne CO_id qui sera alimentée automatiquement par le SGBD
- pour insérer dans la deuxième table, il faut récupérer la valeur de la foreign key CO_id alimentée lors de l'insertion dans la première table. Pour ça il faut utiliser la fonction LAST_INSERT_ID
cf. https://www.w3schools.com/sql/func_m..._insert_id.asp

PS : dans une table il y a des colonnes, les champs sont dans les formulaires ou à la campagne

[philpus256]

Bonjour,

Dans une procédure stocké ça serait plus simple non ?

je ne pense pas que cela soit possible parce que les fichiers de commandes SQL sont fabriqués au fil de l'eau aussi

[philpus256]

PS : dans une table il y a des colonnes, les champs sont dans les formulaires ou à la campagne

merci pour la note d'humour et les explications très utiles, ça va m'aider à mieux organiser mes tables etc.
je reviendrai surement quand j'aurai avancé un peu plus dans mon projet

Cdt.

[Golgotha]

je ne pense pas que cela soit possible parce que les fichiers de commandes SQL sont fabriqués au fil de l'eau aussi

Si c'est possible, c'est la même chose, sauf que tu aura des fonctions à la place de requête sql.

à l'intérieur de ton fichier tu pourrai avoir quelque chose comme ça :

insert_evolution( $computername, $username , ... );

et tout serait traité dans cette fonction quand tu exécute le fichier.

[Artemus24]

Salut philpus256.

je ne sais pas formuler sur une seule requête l'obtention du code id

En ce qui concerne l'automatisation de la colonne "id", Escartefigue l'a très bien expliqué.
Il suffit de déclarer votre colonne ainsi :

Code :

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

`id` integer unsigned not null auto_increment primary key,

et de ne jamais faire référence à cette colonne lors de l'insertion.

ma commande ne marche pas si computername n'existe pas

La solution d'Escartefigue consistant à utiliser la fonction :

Code :

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

LAST_INSERT_ID()

est une bonne idée, à la condition que dans votre traitement, vous avez fait l'insertion de la ligne dans la table "computer" puis de récupérer cet identifiant avant de faire l'insertion dans la table "evolution".

Dans votre problème, nous ne savons pas comment a été rempli la table "computer" au prealable.
Vous nous dites :

vu que la 1ere fois il n'y a pas d'enregistrements dans la table computers

ce qui signifie que pour les autres fois, vous avez un critère pour retrouver cet identifiant.

Dans l'exemple ci-après, j'ai créé un jeu d'essai fictif, que j'ai placé dans une table de travail.
J'ai créé trois tables qui sont 'computer', 'user' et 'evolution'.
Le but est de les remplir à partir de la table travail qui est en fait un fichier plat que j'ai chargé.
Je suppose que vous avez fait cela, au prealable, en utilisant "load data local infile" depuis un fichier Excel.

Quand les tables 'computer' et 'user' ont été remplies, la requête qui vous intéresse est l'avant dernière, là où je fais deux jointures afin de récupérer les identifiants.
Voici ce que je propose :

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
--------------
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 `trav`
--------------
 
--------------
CREATE TABLE `trav`
( `id`            integer  unsigned  NOT NULL auto_increment primary key,
  `computername`  varchar(255)       NOT NULL,
  `username`      varchar(255)       NOT NULL,
  `targeturl`     varchar(255)       NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `trav` (`computername`,`username`,`targeturl`) VALUES
  ('ordi 1', 'user 1', 'cible a'),
  ('ordi 1', 'user 2', 'cible b'),
  ('ordi 1', 'user 3', 'cible c'),
  ('ordi 2', 'user 1', 'cible d'),
  ('ordi 2', 'user 4', 'cible e'),
  ('ordi 2', 'user 5', 'cible f'),
  ('ordi 3', 'user 2', 'cible g'),
  ('ordi 3', 'user 5', 'cible h'),
  ('ordi 4', 'user 2', 'cible i'),
  ('ordi 4', 'user 3', 'cible j')
--------------
 
--------------
select * from `trav`
--------------
 
+----+--------------+----------+-----------+
| id | computername | username | targeturl |
+----+--------------+----------+-----------+
|  1 | ordi 1       | user 1   | cible a   |
|  2 | ordi 1       | user 2   | cible b   |
|  3 | ordi 1       | user 3   | cible c   |
|  4 | ordi 2       | user 1   | cible d   |
|  5 | ordi 2       | user 4   | cible e   |
|  6 | ordi 2       | user 5   | cible f   |
|  7 | ordi 3       | user 2   | cible g   |
|  8 | ordi 3       | user 5   | cible h   |
|  9 | ordi 4       | user 2   | cible i   |
| 10 | ordi 4       | user 3   | cible j   |
+----+--------------+----------+-----------+
--------------
DROP TABLE IF EXISTS `computer`
--------------
 
--------------
CREATE TABLE `computer`
( `id`             integer  unsigned  NOT NULL auto_increment primary key,
  `computer_name`  varchar(255)       NOT NULL,
  index `IDX_COMPUTERNAME` (`computer_name`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TABLE IF EXISTS `user`
--------------
 
--------------
CREATE TABLE `user`
( `id`         integer  unsigned  NOT NULL auto_increment primary key,
  `user_name`  varchar(255)       NOT NULL,
  index `IDX_USERNAME` (`user_name`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TABLE IF EXISTS `evolution`
--------------
 
--------------
CREATE TABLE `evolution`
( `id`              integer  unsigned  NOT NULL auto_increment primary key,
  `target_url`      varchar(255)       NOT NULL,
  `computer_clef`   integer  unsigned  NOT NULL,
  `user_clef`       integer  unsigned  NOT NULL,
  CONSTRAINT `FK_COMPUTER` FOREIGN KEY (`computer_clef`) REFERENCES `computer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_USER`     FOREIGN KEY (`user_clef`)     REFERENCES `user`     (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `computer` (`computer_name`)
  select  distinct computername as computer_name
    from  `trav`
--------------
 
--------------
select * from `computer`
--------------
 
+----+---------------+
| id | computer_name |
+----+---------------+
|  1 | ordi 1        |
|  2 | ordi 2        |
|  3 | ordi 3        |
|  4 | ordi 4        |
+----+---------------+
--------------
insert into `user` (`user_name`)
  select  distinct username as user_name
    from  `trav`
--------------
 
--------------
select * from `user`
--------------
 
+----+-----------+
| id | user_name |
+----+-----------+
|  1 | user 1    |
|  2 | user 2    |
|  3 | user 3    |
|  4 | user 4    |
|  5 | user 5    |
+----+-----------+
--------------
explain
insert into `evolution` (`target_url`,`computer_clef`,`user_clef`)
    select  t1.targeturl  as target_url,
            t2.id         as computer_clef,
            t3.id         as user_clef
 
      from  `trav`     as t1
 
inner join  `computer` as t2
        on  t2.computer_name = t1.computername
 
inner join  `user`     as t3
        on  t3.user_name = t1.username
--------------
 
+----+-------------+-----------+------------+-------+------------------+------------------+---------+------------------+------+----------+----------------------------------------------------+
| id | select_type | table     | partitions | type  | possible_keys    | key              | key_len | ref              | rows | filtered | Extra                                              |
+----+-------------+-----------+------------+-------+------------------+------------------+---------+------------------+------+----------+----------------------------------------------------+
|  1 | INSERT      | evolution | NULL       | ALL   | NULL             | NULL             | NULL    | NULL             | NULL |     NULL | NULL                                               |
|  1 | SIMPLE      | t2        | NULL       | index | IDX_COMPUTERNAME | IDX_COMPUTERNAME | 257     | NULL             |    4 |   100.00 | Using index                                        |
|  1 | SIMPLE      | t1        | NULL       | ALL   | NULL             | NULL             | NULL    | NULL             |   10 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | t3        | NULL       | ref   | IDX_USERNAME     | IDX_USERNAME     | 257     | base.t1.username |    1 |   100.00 | Using index                                        |
+----+-------------+-----------+------------+-------+------------------+------------------+---------+------------------+------+----------+----------------------------------------------------+
--------------
select * from `evolution`
--------------
 
--------------
insert into `evolution` (`target_url`,`computer_clef`,`user_clef`)
    select  t1.targeturl  as target_url,
            t2.id         as computer_clef,
            t3.id         as user_clef
 
      from  `trav`     as t1
 
inner join  `computer` as t2
        on  t2.computer_name = t1.computername
 
inner join  `user`     as t3
        on  t3.user_name = t1.username
--------------
 
--------------
select * from `evolution`
--------------
 
+----+------------+---------------+-----------+
| id | target_url | computer_clef | user_clef |
+----+------------+---------------+-----------+
|  1 | cible a    |             1 |         1 |
|  2 | cible b    |             1 |         2 |
|  3 | cible c    |             1 |         3 |
|  4 | cible d    |             2 |         1 |
|  5 | cible e    |             2 |         4 |
|  6 | cible f    |             2 |         5 |
|  7 | cible g    |             3 |         2 |
|  8 | cible h    |             3 |         5 |
|  9 | cible i    |             4 |         2 |
| 10 | cible j    |             4 |         3 |
+----+------------+---------------+-----------+
--------------
select  t2.computer_name,
        t3.user_name,
        t1.target_url
 
      from  `evolution` as t1
 
inner join  `computer` as t2
        on  t2.id = t1.computer_clef
 
inner join  `user`     as t3
        on  t3.id = t1.user_clef
--------------
 
+---------------+-----------+------------+
| computer_name | user_name | target_url |
+---------------+-----------+------------+
| ordi 1        | user 1    | cible a    |
| ordi 1        | user 2    | cible b    |
| ordi 1        | user 3    | cible c    |
| ordi 2        | user 1    | cible d    |
| ordi 2        | user 4    | cible e    |
| ordi 2        | user 5    | cible f    |
| ordi 3        | user 2    | cible g    |
| ordi 3        | user 5    | cible h    |
| ordi 4        | user 2    | cible i    |
| ordi 4        | user 3    | cible j    |
+---------------+-----------+------------+
--------------
drop table `trav`
--------------
 
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Afin de vérifier la conformité du travail, j'ai fait un select avec jointure sur les trois tables afin d'afficher le computer_name et le user_name associé au target_url.

@+