shell脚本实现mysql查询及结果集导出

数据库背景：

两表通过fid外键关联

表1保存基本信息fid、fname

表2保存对每个fid的操作

意在将fid、fname、fid上的操作次数一同导出

表1数据6w+

表2数据百万+

为了避免连表查询导致对数据库的破坏，通过脚本分别执行两表查询并整合结果集导出

测试数据表结构：

 

脚本：export4db.sh

# 查询所有id的mysql配置
HOSTNAME="172.25.44.142";
PORT="3310";
USERNAME="test";
PASSWORD="123456";
database_name="test";
table_name="test_tal";
field="f_id,file_name";


# 对id查询进行limit循环查询，每次查询1w条id，分页循环查询所有id。追加写入id.txt
file_path="/id.txt";
page=0;
pageSize=10000;
idCount="select count(*) from ${table_name} ";
echo " 获取所有id总量的SQL为：${idCount}";
# 参考：https://www.jianshu.com/p/092a6c260c2f
# -e情况下默认包含表头，等同于-Be
# -s 不要表头(即结果集字段名)
count=`mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD}  ${database_name} -Bse "${idCount}"`
roundOne=`expr $count / $pageSize`;
# 下述三元运算符无意义，上个运算的/除运算结果只有商部分，不是小数（expr不支持浮点除法）
# round=`echo $roundOne | awk '{print int($1)==$1?int($1):int(int($1*10/10+1))}'`;
# 参考：https://blog.csdn.net/weixin_45595689/article/details/103035655
# roundOne支持浮点型的写法：roundOne=`awk 'BEGIN{printf "%.2f\n",’$num1‘/’$num2‘}'`;
# round的值与下述for循环中的<=相关，如果round=`echo $roundOne | awk '{print int(int($1*10/10+1))}'`;则for循环中条件为page < $round
round=`echo $roundOne | awk '{print int($1)}'`;
echo "共有 ${count} 条数据";
echo "共需要循环 $round 次";
echo "" > $file_path;

for ((page = 0; page <= $round; page++))
    do
    start=`expr $page \* $pageSize`;
    where=" limit ${start},${pageSize}";
    queryRes="select ${field} from ${table_name} ${where}";
    # echo "${queryRes}";
    # -N 不要表头(即结果集字段名)
    # idRes=`mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD}  ${database_name} -A --default-character-set=utf8 -Ne "${queryRes}"`
    # sed设置分隔符，字段间默认分隔符为制表符，替换为---++++++++++---。相应的，后续读取并用分隔符分割截取时，因为+在正则中有特殊含义，需要转义
    idRes=`mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD}  ${database_name} -A --default-character-set=utf8 -Ne "${queryRes}" | sed -e "s/[\t]/---++++++++++---/g"`
    echo "${idRes}" >> $file_path;
    done

# 不对id查询进行limit循环查询
# queryRes="select ${field} from ${table_name} ";
# echo " 获取结果集的SQL为：${queryRes}";
# res=`mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD}  ${database_name} -NBse "${queryRes}"`
# echo "${res}" > /id.txt;

# 查询分组count表mysql配置
HOSTNAME="172.25.44.142";
PORT="3310";
database_name_count="test";
table_name_count="test_tal";
field_count="count(f_id)";
where_field="f_id";
file_path_count="/countRes.txt";
echo "" > $file_path_count;

cat $file_path | while read line
	do 
		# id=$(echo "$line"| awk -F ' ' '{print $1}');
		# +在正则中有特殊含义，用分隔符分割截取时，需要转义
		id=$(echo "$line"| awk -F '---\+\+\+\+\+\+\+\+\+\+---' '{print $1}');
		# name=$(echo "$line"| awk -F ' ' '{print $2}');
		# +在正则中有特殊含义，用分隔符分割截取时，需要转义
		name=$(echo "$line"| awk -F '---\+\+\+\+\+\+\+\+\+\+---' '{print $2}');
		# echo $id;
		# echo $name;
		# 空行跳过
		if [ ! $id ]; 
		then
			continue;
		fi
		queryCount="select ${field_count} as count,'${id}' as id,'${name}' as name from ${table_name_count} where ${where_field}=${id}";
		# echo "获取总量的SQL为：${queryCount}";
		countRes=`mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD}  ${database_name_count} -Bse "${queryCount}"`
		echo "${countRes}" >> $file_path_count;
	done

脚本执行结果：

$ ./export4db.sh 
 获取所有id总量的SQL为：select count(*) from test_tal 
mysql: [Warning] Using a password on the command line interface can be insecure.
共有 3 条数据
共需要循环 0 次
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
$ cat id.txt 

1---++++++++++---test1
2---++++++++++---test2
10---++++++++++---特使团
$ cat countRes.txt 

1	1	test1
1	2	test2
1	10	特使团