shell执行mysql数据导入导出

本文介绍了一种使用SQL从多个表中提取数据并将其导出到文件的方法,然后将这些数据重新导入到另一个表中。具体步骤包括构建复杂的SQL查询来连接不同表,并通过特定命令将查询结果导出到文本文件,最后再将该文件的数据导入到目标表。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

CMD1="use nuomi_oc;SELECT u.id as user_id, u.login_name as login_name,u.email as email, u.name as name, u.user_no as user_no, o1.name as org_name,p.id as position_id,p.type as position_type, p.name as position_name,u.user_enabled as user_enabled,o2.id as city_org_id,o2.name as city_org_name,o3.id region_org_id, o3.name region_org_name,o.structure_code as saler_type

#into outfile \"export_saler_number.txt\" lines terminated by \"\r\n\"

FROM user_org_position_relation r

LEFT JOIN position p ON r.position_id = p.id

LEFT JOIN organization o ON r.org_id = o.id

LEFT JOIN org_higher_org_relation hr ON r.org_id = hr.org_id

LEFT JOIN organization o1 ON hr.higher_org_id = o1.id

LEFT JOIN user_org_position_relation r1 ON o1.id = r1.org_id

LEFT JOIN user u ON r.user_id = u.id

LEFT JOIN org_higher_org_relation hr1 ON r.org_id = hr1.org_id

LEFT JOIN organization o2 ON hr1.higher_org_id = o2.id

LEFT JOIN org_higher_org_relation hr2 ON r.org_id = hr2.org_id

LEFT JOIN organization o3 ON hr2.higher_org_id = o3.id

WHERE p.type =1 AND r.position_id != 144  AND o1.type = 4000

AND o2.type = 2000 AND o3.type = 1000

GROUP BY u.id;"

CMD2="use nuomi_oc;load data local infile \"export_saler_number.txt\"  into table erp_saler_info

file="./export_saler_number.txt"

if [  -f "$file" ]; then

  rm -f "$file"

fi

RES1=$(mysql -h102.94.22.00 -P8113 -uroot -p1234 -e "$CMD1"  > ./export_saler_number.txt)

CMD3="use nuomi_oc;select user_id,login_name,email,name,user_no,org_name,position_id,position_type,position_name,user_enabled,city_org_id,city_org_name,region_org_id,region_org_name,saler_type   into outfile \"export_saler_number.txt\" lines terminated by \"\r\n\" from erp_saler_info"

RES2=$(mysql -h10.94.32.00 -P8440 -uroot2 -p1234 -e "$CMD2")


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值