mysql数据转换的shell

来源：互联网发布：手机九宫格锁屏软件编辑：程序博客网时间：2024/05/23 17:52
mysql数据转换的shell
#!/bin/bashtablearr=("hr_business_from" "hr_business_shared" "hr_business_status" "hr_resume_certification" "hr_resume_content" "hr_resume_core" "hr_resume_education" "hr_resume_hope_city" "hr_resume_hope_jobs" "hr_resume_hope_trade" "hr_resume_img" "hr_resume_keywords" "hr_resume_lang" "hr_resume_projects" "hr_resume_refreshtime" "hr_resume_skill" "hr_resume_training" "hr_resume_work");tablearr=("hr_business_from");joarr=(0 1);for table_name in ${tablearr[@]}; do#拿到134对应表中最大的IDmaxid=` (echo "SELECT t0.resume_id,t1.resume_id,t2.resume_id,t3.resume_id,t4.resume_id,t5.resume_id,t6.resume_id,t7.resume_id,t8.resume_id,t9.resume_id FROM (SELECT resume_id FROM  "$table_name"_0 ORDER BY resume_id DESC LIMIT 1) AS t0,(SELECT resume_id FROM  "$table_name"_1 ORDER BY resume_id DESC LIMIT 1) AS t1,(SELECT resume_id FROM  "$table_name"_2 ORDER BY resume_id DESC LIMIT 1) AS t2,(SELECT resume_id FROM  "$table_name"_3 ORDER BY resume_id DESC LIMIT 1) AS t3,(SELECT resume_id FROM  "$table_name"_4 ORDER BY resume_id DESC LIMIT 1) AS t4,(SELECT resume_id FROM  "$table_name"_5 ORDER BY resume_id DESC LIMIT 1) AS t5,(SELECT resume_id FROM  "$table_name"_6 ORDER BY resume_id DESC LIMIT 1) AS t6,(SELECT resume_id FROM  "$table_name"_7 ORDER BY resume_id DESC LIMIT 1) AS t7,(SELECT resume_id FROM  "$table_name"_8 ORDER BY resume_id DESC LIMIT 1) AS t8,(SELECT resume_id FROM  "$table_name"_9 ORDER BY resume_id DESC LIMIT 1) AS t9" | mysql -h 192.168.6.134 -P3306 hr_resume_version_tmp -u miaohr -p123456)| awk '/^id|^resume_id/{next;}{lens=split($0,tA," ");m = tA[0];for (i in tA) {if (tA[i] > m) {m = tA[i];}}print m;}' `;for table_jo in ${joarr[@]}; doecho $table_name"_"$table_jo;while read -a rowdo# 构建sql查询语句if [[ ${row[0]} == "id" ]]; thenfield_row=(${row[@]});#获取字段列field_str2="";for row_data in ${field_row[@]}; do#REPLACE(content, '\'', '\\\'' )field_str2=$field_str2",CONCAT(\"'\",REPLACE("$row_data", \"'\", \"\\\'\" ),\"'\") AS "$row_data"";#field_str2=$field_str2",CONCAT(\"'\","$row_data",\"'\") AS "$row_data"";#field_str2=$field_str2",IF("$row_data"!=\"\","$row_data",' ') AS "$row_data"";done;field_str2=$(echo $field_str2|sed -e "s/^,//g");#echo "SELECT "$field_str2" FROM "$table_name"_"$table_jo" where resume_id=2153529 ORDER BY id DESC";echo "SELECT "$field_str2" FROM "$table_name"_"$table_jo" where resume_id>"$maxid" ORDER BY resume_id DESC limit 1"while read -a rowdo#todo 数据在这里处理if [[ ${row[0]} != "id" ]]; thenunset field_row[0];#删除idfield_str=$(echo ${field_row[@]:0}|sed -e 's/\s/,/g');#重新组合字段字符串unset row[0];#删除id值#重新组合字段值字符串(方法一)value_str=$(echo ${row[@]:0}|sed -e 's/\s/,/g');  #重新组合字段值字符串profix=$((${row[1]//\'/}%10))sql="INSERT INTO "$table_name"_"$profix" ("$field_str", from_db, resume_v3_id) VALUES ("$value_str", 'hr_resume_history_for_82', "${row[1]}")";echo $sql;#执行数据#echo $sql | mysql -h192.168.6.134 -umiaohr -p123456 hr_resume_version_tmp;elsefield_row=(${row[@]});#获取字段列fi;done < <(echo "SELECT "$field_str2" FROM "$table_name"_"$table_jo" ORDER BY resume_id DESC limit 1" | mysql -h 192.168.6.82 -P3306 hr_resume_history -u miaohr -p123456)fi;done < <(echo "SELECT * FROM "$table_name"_"$table_jo"  ORDER BY resume_id DESC LIMIT 1" | mysql -h 192.168.6.82 -P3306 hr_resume_history -u miaohr -p123456)done;done;
针对小数据量数据效果很不错。
0 0