1.MongoDB启动 net start MongoDB
mongoexport -h 127.0.0.1 root 123456 -d Sina -c Relationships -f _id,crawl_time,fan_id,followed_id --csv -o /data/share/sentimentRecords.csv
mongoexport -h 127.0.0.1 -d Sina -c Relationships -f _id,crawl_time,fan_id,followed_id, --type=csv -o sentimentRecords.csv
load data local infile 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\sentimentRecords.csv'
into table `follow` character set utf8
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\n'
ignore 1 lines;
H:/Spider/WeiboSpider-weiboInfo/sina/spiders/weibo
load data local infile 'H:/Spider/WeiboSpider-weiboInfo/sina/spiders/weibo/weibo.txt'
into table weibo(user_id,weibo_content,gmt_create,like_amount,forwarding_amount,comment_amount);
1.MongoDB导出csv
mongoexport -h 127.0.0.1 -d Sina -c Relationships -f fan_id,followed_id, --type=csv -o follow.csv
mongoexport -h 127.0.0.1 -d Sina -c Tweets -f _id,crawl_time --type=csv -o user.csv
2.将csv文件放到
C:\ProgramData\MySQL\MySQL Server 5.7\Uploads 中
3.在MySQL里执行下面语句
load data local infile 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\follow.csv'
into table `follow` character set utf8
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\n'
ignore 1 lines;
//collection
load data local infile 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\collection.csv'
into table `collection` character set utf8
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\n'
ignore 1 lines;
//like
load data local infile 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\like.csv'
into table `like` character set utf8
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\n'
ignore 1 lines;
//follow
load data local infile 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\follow.csv'
into table `follow` character set utf8
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\n'
ignore 1 lines;
//user_id
load data local infile 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\userid.csv'
into table `id_user` character set utf8
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\n'
ignore 1 lines;
//tweets
load data local infile 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\Tweets.csv'
into table `tweets` character set utf8mb4
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\n'
ignore 1 lines;
//tweetsurl
load data local infile 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\TweetsUrl.csv'
into table `tweetsurl` character set utf8
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\n'
ignore 1 lines;
//comments
load data local infile 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\Comments.csv'
into table `comments` character set utf8mb4
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\n'
ignore 1 lines;
//labels
load data local infile 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\userlabels.csv'
into table `userlabels` character set utf8
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\n'
ignore 1 lines;
//userdata
load data local infile 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\userdata.csv'
into table `userdata` character set utf8
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\n'
ignore 1 lines;
6.mysql字符串切割
SELECT substring_index(substring_index(t.context,',', b.help_topic_id + 1), ',', -1) FROM test.test t join mysql.help_topic b ON b.help_topic_id < (LENGTH(t.context) - LENGTH(REPLACE(t.context, ',', '')) + 1);