· 6 years ago · Jun 08, 2019, 04:40 PM
1####################
2# step 1 : www_guest
3####################
4
5# already imported by the tool MySQL Workbench
6
7
8
9#######################
10# step 2 : name3000_xml
11#######################
12
13# create table from xml
14create table if not exists name3000_xml (
15 full_name varchar(60),
16 country varchar(60),
17 created_at varchar(500),
18 id int,
19 email varchar(60)
20);
21
22# import xml
23load xml infile 'c:\\programdata\\mysql\\mysql server 8.0\\uploads\\Name3000.xml'
24into table name3000_xml
25rows identified by '<record>'
26(@Full, @Country, @Created, @Id, @Email)
27set full_name=@Full, country=@Country, created_at=@Created, id=@Id, email=@Email;
28
29# add additional columns into name3000_xml
30alter table name3000_xml add created_at_readable datetime after created_at;
31alter table name3000_xml add full_name_mod varchar(60) after full_name;
32alter table name3000_xml add first_name varchar(60) after full_name_mod;
33alter table name3000_xml add last_name varchar(60) after first_name;
34
35# avoid error message
36set sql_safe_updates=0;
37
38# post-process Timestamp
39update ignore name3000_xml as t1
40set t1.created_at_readable = CONVERT_TZ(STR_TO_DATE(substring(t1.created_at,5,20),'%b %d %Y %H:%i:%s'), @@session.time_zone, '+00:00');
41
42# post-process full_name
43update ignore name3000_xml as t1
44set t1.full_name_mod = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(t1.full_name,'Ms. ',''),'Mr. ',''),'Miss ',''),' PhD',''),' Sr.',''),' Jr.',''),'Dr. ',''),' DDS',''),' MD',''),' DVM',''),' III',''),' II',''),' IV',''),' I','');
45
46# separate full_name into first_name + last_name
47update ignore name3000_xml as t1
48set t1.first_name = substring_index(t1.full_name_mod, ' ', 1);
49
50update ignore name3000_xml as t1
51set t1.last_name = substring_index(t1.full_name_mod, ' ', -1);
52
53# debug, query
54select * from name3000_xml;
55
56
57
58########################
59# step 3 : name3000_json
60########################
61
62# create table from json by MySQL Workbench
63
64select * from name3000_json;
65
66# add additional columns into name3000_json
67alter table name3000_json add created_at_readable datetime after `Created At`;
68alter table name3000_json add full_name_mod varchar(60) after `Full Name`;
69alter table name3000_json add first_name varchar(60) after full_name_mod;
70alter table name3000_json add last_name varchar(60) after first_name;
71
72# avoid error message
73set sql_safe_updates=0;
74
75# post-process Timestamp
76update ignore name3000_json as t1
77set t1.created_at_readable = CONVERT_TZ(STR_TO_DATE(substring(t1.`Created At`,1,19),'%Y-%c-%dT%H:%i:%s'), @@session.time_zone, '+00:00');
78
79# post-process full_name
80update ignore name3000_json as t1
81set t1.full_name_mod = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(t1.`Full Name`,'Ms. ',''),'Mr. ',''),'Miss ',''),' PhD',''),' Sr.',''),' Jr.',''),'Dr. ',''),' DDS',''),' MD',''),' DVM',''),' III',''),' II',''),' IV',''),' I','');
82
83# separate full_name into first_name + last_name
84update ignore name3000_json as t1
85set t1.first_name = substring_index(t1.full_name_mod, ' ', 1);
86
87update ignore name3000_json as t1
88set t1.last_name = substring_index(t1.full_name_mod, ' ', -1);
89
90# debug, query
91select * from name3000_json;
92
93
94
95###################
96# step 5 : customer
97###################
98
99# Merge 3 tables -> new table
100
101create table if not exists customer (
102 #full_id int not null auto_increment,
103 from_table varchar(60),
104
105 # common fields in all three tables
106 first_name varchar(60),
107 last_name varchar(60),
108 country varchar(60),
109 email varchar(60),
110
111 # uncommon fields in www_guests
112 ip_address varchar(60),
113 gender varchar(60),
114 town varchar(60),
115 member varchar(60),
116 skill varchar(60),
117
118 # uncommon fields in name3000_xml, name3000_json
119 created_at varchar(500)
120
121 #primary key (full_id)
122);
123
124# Note that following fields are skipped in the final customer table.
125# www_guests (id)
126# name3000_xml (id, full_name)
127# name3000_json (id, `Full Name`, BirthDate)
128
129insert into customer
130(
131 select 'www_guests', g.first_name, g.last_name, g.country, g.email, g.ip_address, g.gender, g.town, g.member, g.skill, 'NaN'
132 from www_guests as g
133);
134
135insert into customer
136(
137 select 'name3000_xml', x.first_name, x.last_name, x.country, x.email, 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', x.created_at_readable
138 from name3000_xml as x
139);
140
141insert into customer
142(
143 select 'name3000_json', j.first_name, j.last_name, j.country, j.email, j.ip, 'NaN', 'NaN', 'NaN', 'NaN', j.created_at_readable
144 from name3000_json as j
145);
146
147# debug
148select * from customer;