-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathsql.clean
executable file
·70 lines (36 loc) · 3.52 KB
/
sql.clean
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
mysql -u root --local-infile nader14_jobb120
---------------------------------
LOAD DATA LOCAL INFILE '/var/www/jobber/danger43/Crawler_internship.com/computer.csv' INTO TABLE cities FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n' (@paid,@college_cridit,@description, @campany_name,@cities,@deadline,@link,@title,@time) set name=@cities;
------------FOR ALL CSV !!!!!!!!!!!!!!!!!!!
------------------------------------------
CREATE TABLE `tmp` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(50) NOT NULL,`ascii_name` varchar(50) NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=73 ;
INSERT INTO tmp(name) SELECT DISTINCT name FROM cities;
DROP TABLE cities;
RENAME TABLE tmp TO cities;
-------------------------------------
UPDATE cities SET ascii_name=REPLACE(SUBSTRING_INDEX(name, ',', 1), '"', "");
UPDATE cities SET name=REPLACE(SUBSTRING_INDEX(name, ',', -1), '"', "");
/////////////////////////////////////////////////////////////////////////////////////////////////
LOAD DATA LOCAL INFILE '/var/www/jobber/danger43/Crawler_internship.com/computer.csv' INTO TABLE jobs FIELDS TERMINATED BY '^' LINES TERMINATED BY '"\n' (@paid,@college_cridit,@description, @campany_name,@cities,@deadline,@link,@title,@time) set type_id =1 ,category_id=1 , title=@title , description=@description , company=@campany_name , city_id=(select id from cities where ascii_name LIKE CONCAT('%' , @cities , '%' ) ), url=@link , created_on=(SELECT timestamp('2015-04-30 14:53:27') - INTERVAL FLOOR( RAND( ) * 366) DAY), views_count=(select floor(99*RAND()+11)), apply_online=(select floor(96*RAND()+1)) , is_active=true , apply_online=true , poster_email='[email protected]';
---------------------------------------------------
UPDATE cities SET ascii_name=REPLACE(SUBSTRING_INDEX(name, ',', 1), '"', "");
UPDATE cities SET name=REPLACE(SUBSTRING_INDEX(name, ',', -1), '"', "");
------------FOR ALL CSV but please change the category !!!!!!!!!!!!!!!!!!!
SELECT timestamp('2015-04-30 14:53:27') - INTERVAL FLOOR( RAND( ) * 366) DAY;
| category_id | int(11) | NO | MUL | NULL | |
| title | varchar(100) | YES | | | |
| description | text | YES | | NULL | |
| company | varchar(150) | YES | | | |
| city_id | int(11) | YES | | NULL | |
| url | varchar(150) | YES | | NULL | |
| apply | varchar(200) | YES | | | |
| created_on | timestamp | NO | | 0000-00-00 00:00:00 | |
| is_temp | tinyint(4) | NO | | NULL | |
| is_active | tinyint(4) | NO | | NULL | |
| views_count | int(11) | NO | | NULL | |
| auth | varchar(32) | NO | | NULL | |
| outside_location | varchar(150) | NO | | NULL | |
| poster_email | varchar(100) | NO | | NULL | |
| apply_online | tinyint(4) | NO | | NULL | |
| spotlight | tinyint(4) | NO | | NULL | |
Caractère Code ISO Code HTML