使用shell脚本导出MySql查询的月表数据到EXCEL中

typecodes

发布于 2024-03-29 15:21:35

1000

发布于 2024-03-29 15:21:35

文章被收录于专栏：typecodestypecodes

经常会踫到这样的场景需求：自定义时间从MySql流水月表中SELECT出来数据到excel报表文件中，所以自己写了这个shell脚本来处理。

1 实现思路

代码比较简单，主要使用了mysql -e执行SQL语句，然后重定向到txt文件中。由于linux默认是uft-8的格式，所以在使用awk命令处理完txt文件后，通过iconv命令把utf8的文件转换成最终的gbk文件。

2 脚本代码

鉴于数据量比较大，我们的shell脚本需要考虑MySQL执行INSERT的效率，所以采用了对次数取模拼接多个VALUES的值来实现。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59

#!/bin/bash # FileName: exportmysqlshell1.sh # Description: 使用shell脚本导出MySql月表数据到EXCEL中 # Simple Usage: sh exportmysqlshell1.sh # (c) 2020.08.01 vfhky https://typecodes.com/linux/exportmysqlshell1.html # https://github.com/vfhky/shell-tools/blob/master/mysql/exportmysqlshell1.sh if [ $# -ne 2 ]; then echo "usage: sudo $0 startTimeStamp endTimeStamp" exit 0 fi startTimeStamp=$1 endTimeStamp=$2 # 简单校验合法性 if [ "${startTimeStamp}" -ge "${endTimeStamp}" ]; then echo "${startTimeStamp} >= ${endTimeStamp}." exit 1 fi # 获得月表后缀 startYearMonth=$(date -d @"${startTimeStamp}" "+%Y%m") endYearMonth=$(date -d @"${endTimeStamp}" "+%Y%m") if [ "${startYearMonth}" -ne "${endYearMonth}" ]; then echo "${startYearMonth} is not equalt to ${endYearMonth}." exit 2 fi curDateTime=$(date "+%Y-%m-%d %H:%M:%S") timeStamp=$(date -d "$current" +%s) dstFilePrefix="gather_rcd_"${startTimeStamp}"_"${endTimeStamp}"_"${timeStamp} dstFile=${dstFilePrefix}"_gbk.csv" dstFileUtf8Txt=${dstFilePrefix}"_utf8.txt" dstFileUtf8Csv=${dstFilePrefix}"_utf8.csv" echo ${curDateTime}","${timeStamp}","${dstFile} # mysql命令导出查询结果到txt文件中 mysql -h113.16.111.17 -P3301 -utest_user -p12345678 activity --default-character-set=utf8 -A -e "SELECT uid, FROM_UNIXTIME(createtime,'%Y-%m-%d %H:%i:%s') as createTime, ispush from test_log_${startYearMonth} WHERE createtime>=${startTimeStamp} AND createtime<=${endTimeStamp};" > ${dstFileUtf8Txt} if [ $? -ne 0 ]; then echo "== mysql query failed ==" exit 3 fi # 注意是3个字段，但是createTime值自带了1个空格 awk -F " " '{if(NR==1){print $1",",$2",", $3}else{print $1",",$2" "$3",", $4}}' ${dstFileUtf8Txt} > ${dstFileUtf8Csv} if [ $? -ne 0 ]; then echo "== handle file failed ==" exit 4 fi # utf-8转换成gbk格式 iconv -f utf8 -t gb2312 -o ${dstFile} ${dstFileUtf8Csv} if [ $? -ne 0 ]; then echo "== iconv failed ==" else echo "== iconv success. ==" fi