shell执行mysql数据导入导出

最新推荐文章于 2023-12-19 10:54:59 发布

原创最新推荐文章于 2023-12-19 10:54:59 发布 · 1.4k 阅读

0 ·

CC 4.0 BY-SA版权

linux 专栏收录该内容

10 篇文章

订阅专栏

本文介绍了一种使用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"

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")