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
| delimiter $$
drop procedure if exists import_conrad2$$
create procedure import_conrad2()
begin
#on se crée la table d'import
drop table `import_conrad2`;
CREATE TABLE `import_conrad2`(
`sku` mediumint unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`url_product` varchar(255) NOT NULL,
`url_image` varchar(255) NOT NULL,
`description` text NOT NULL,
`merchant_store_name` varchar(255) NOT NULL,
`merchant_univers_name` varchar(255) NOT NULL,
`merchant_category_name` varchar(255) NOT NULL,
`merchant_department_name` varchar(255) NOT NULL,
`price` varchar(7) NOT NULL,
PRIMARY KEY (`sku`)
)engine=memory auto_increment=1;
#ici tu colles ton load, il faut que le fichier soit sur le même serveur que mysql et accessible par lui
#on désactive le controle d'intégrité référentiel
SET FOREIGN_KEY_CHECKS=0;
#on recrée et remplit la table `store_name`
drop table `store_name`;
create table `store_name`(
id tinyint unsigned NOT NULL auto_increment,
name varchar(255) not null,
constraint pk_store primary key(id)
)engine=innodb auto_increment=1;
insert into `store_name`(`name`)
select unique `merchant_store_name`
from `import_conrad2`
order by `merchant_store_name`;
#on recrée et remplit la table `univers_name`
drop table `univers_name`;
create table `univers_name`(
id tinyint unsigned NOT NULL auto_increment,
name varchar(255) not null,
constraint pk_univers primary key(id)
)engine=innodb auto_increment=1;
insert into `univers_name`(`name`)
select unique `merchant_univers_name`
from `import_conrad2`
order by `merchant_univers_name`;
#on recrée et remplit la table `merchant_category_name`
drop table `category_name`;
create table `category_name`(
id tinyint unsigned NOT NULL auto_increment,
name varchar(255) not null,
constraint pk_category primary key(id)
)engine=innodb auto_increment=1;
insert into `category_name`(`name`)
select unique `merchant_category_name`
from `import_conrad2`
order by `merchant_category_name`;
#on recrée et remplit la table `store_name`
drop table `department_name`;
create table `department_name`(
id tinyint unsigned NOT NULL auto_increment,
name varchar(255) not null,
constraint pk_department primary key(id)
)engine=innodb auto_increment=1;
insert into `department_name`(`name`)
select unique `merchant_department_name`
from `import_conrad2`
order by `merchant_department_name`;
#on recrée enfin la table principale
drop table `conrad2`;
create table `conrad2`(
`sku` mediumint unsigned NOT NULL auto_increment,
`id_store` tinyint unsigned NOT NULL,
`id_univers` tinyint unsigned NOT NULL,
`id_category` tinyint unsigned NOT NULL,
`id_department` tinyint unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`url_product` varchar(255) NOT NULL,
`url_image` varchar(255) NOT NULL,
`price` varchar(7) NOT NULL,
`description` text NOT NULL,
constraint pk_conrad2 PRIMARY KEY (`sku`)
)engine=memory auto_increment=1;
insert into `conrad2`(`sku`,`id_store`,`id_univers`,`id_category`,`id_department`,
`name`,`url_product`,`url_image`,`price`,`description`)
select i.sku,s.id,u.id,c.id,d.id,i.name,i.url_product,i.url_image,i.price,i.description
from `import_conrad2` i
left join `store_name` s on s.name=i.`merchant_store_name`
left join `univers_name` u on u.name=i.`merchant_univers_name`
left join `category_name` c on c.name=i.`merchant_category_name`
left join `department_name` d on d.name=i.`merchant_department_name`;
end$$
delimiter ; |
Soucis avec une requête sur une table de 93 000 entrées
Répondre avec citation
Envoyé par Fred_34
Partager