因为权限问题,不能用mysqldump或是load file的方式。。。所以自己手动写脚本导入。
遇到了很多坑,最主要的就是null和空格问题:
function manage_db(){
mysql -h "s4494i.mars.grid.sina.com.cn" -P 4494 -u ea_fuyi_read_r -p3c5c7dfb6d873b7 ea -N -e "set character_set_results=utf8;\
select concat_ws('###',ifnull(id,111111),ifnull(customer_id,111111),ifnull(advert_id,111111),ifnull(advert_group_id,111111),ifnull(name,111111),ifnull(wdht,111111),ifnull(idea_title,111111),ifnull(keyword,111111),ifnull(idea_type,111111),ifnull(idea_content,111111),ifnull(landing_page,111111),ifnull(html,111111),ifnull(preview_url,111111),ifnull(material_md5,111111),ifnull(audit_status,111111),ifnull(status,111111),ifnull(refuse_content,111111),ifnull(send_status,111111),ifnull(ideaquality_json,111111),ifnull(idea_ignore,111111),ifnull(isadvise_update,111111),ifnull(video_time,111111),ifnull(component_id,111111),ifnull(material,111111),ifnull(create_time,111111),\
ifnull(modify_time,111111)) from ea_idea where modify_time > '$dt1';" >${INSERT_OP}/ea_idea.ea_idea.data
sed -i 's/"/\\"/g' ${INSERT_OP}/ea_idea.ea_idea.data
sed -i 's/#111111#/#null#/g' ${INSERT_OP}/ea_idea.ea_idea.data
sed -i 's/#111111/#null/g' ${INSERT_OP}/ea_idea.ea_idea.data
sed -i 's/111111#/null#/g' ${INSERT_OP}/ea_idea.ea_idea.data
cat ${INSERT_OP}/ea_idea.ea_idea.data | awk -F"###" '{ print "insert into ea_idea values(\""$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"\") on duplicate key update id=\""$1"\",customer_id=\""$2"\",advert_id=\""$3"\",advert_group_id=\""$4"\",name=\""$5"\",wdht=\""$6"\",idea_title=\""$7"\",keyword=\""$8"\",idea_type=\""$9"\",idea_content=\""$10"\",landing_page=\""$11"\",html=\""$12"\",preview_url=\""$13"\",material_md5=\""$14"\",audit_status=\""$15"\",status=\""$16"\",refuse_content=\""$17"\",send_status=\""$18"\",ideaquality_json=\""$19"\",idea_ignore=\""$20"\",isadvise_update=\""$21"\",video_time=\""$22"\",component_id=\""$23"\",material=\""$24"\",create_time=\""$25"\",modify_time=\""$26"\";";} ' > ${INSERT_OP}/ea_idea.ea_idea.sql
mysql -h "10.39.40.52" -P 3306 -u root -p123456 ad_analyse <${INSERT_OP}/ea_idea.ea_idea.sql
}