mysql数据转换的shell

最新推荐文章于 2021-11-15 18:58:31 发布

e421083458

最新推荐文章于 2021-11-15 18:58:31 发布

阅读量1k

点赞数

分类专栏： Linux shell编程

本文链接：https://blog.youkuaiyun.com/e421083458/article/details/38537985

版权

Linux 同时被 2 个专栏收录

72 篇文章

订阅专栏

shell编程

30 篇文章

订阅专栏

本文介绍了一种使用Shell脚本进行MySQL数据库中特定表的数据迁移方法。该脚本能够从多个分表中查找最大ID，并迁移最新的记录到目标表。此过程适用于小规模数据迁移场景。

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

mysql数据转换的shell

#!/bin/bash

tablearr=("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对应表中最大的ID
maxid=` (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[@]}; do
		echo $table_name"_"$table_jo;
			
			while read -a row
			do
				# 构建sql查询语句
				if [[ ${row[0]} == "id" ]]; then
				field_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 row
				do
					#todo 数据在这里处理
					
					if [[ ${row[0]} != "id" ]]; then
							unset field_row[0];	#删除id
							field_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;
						
					else
						field_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;

针对小数据量数据效果很不错。