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

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