mysql查询导出csv文件并自动发送附件给领导

LINUX IT敢客 7个月前 (01-20) 7346次浏览 已收录 2个评论 扫描二维码

        领导又吩咐活过来了,要求每周日查询 4 个 sql,统计一周的数据,并生存 excel 文件,然后还要发给他。想一想,这些都是重复性的劳动,除了时间是变,其他都是不变的。所以还是得搞个自动化脚本处理得了!


        首先我拿到了 4 个 sql 文件,我将其变成模板文件,比如 data1_muban.sql,data2_muban.sql,data3_muban.sql,data4_muban.sql;然后将其中的日期时间用变量名代替,比如我这边的时间是 BETWEEN ‘LAST_SUN’ AND ‘THIS_SAT’  其中 LAST_SUN 和 THIS_SAT 是指上一个周日和这个周六,毕竟是一周的数据,可以随意命名,根据自己需求来,考虑到这个变量始终都是要变成日期格式的,所以模板文件是不能动的,要生存临时 sql 文件,然后将临时 sql 文件中的变量名称替换成需要日期。以下是脚本内容,我会详细讲解一下

#!/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
export PATH
cp data_muban.sql data1.sql
today=`date -d "1 days ago" +%Y-%m-%d`
last_sun=`date -d "7 days ago" +%Y-%m-%d`
sed -ir 's/THIS_SAT/'"$today"'/g' data1.sql
sed -ir 's/LAST_SUN/'"$last_sun"'/g' data1.sql
passwd='yourdbpasswd'
dbname='yourdbname'
dbuser='yourdbuser'
/usr/bin/mysql -h xxx.xxx.xxx.xxx -u $dbuser -p$passwd >1.log << EOF
use $dbname;
source data1.sql
EOF
cat 1.log | while read line
do
echo $line | tr " " ","
done > data1.csv
iconv -f utf-8 -t gb2312 data1.csv > data2.csv 
mv data2.csv YHLS_$today.csv
rm -rf data1.*
rm -rf 1.log

sleep 5

cp data1_muban.sql data2.sql
sed -ir 's/THIS_SAT/'"$today"'/g' data2.sql
sed -ir 's/LAST_SUN/'"$last_sun"'/g' data2.sql
/usr/bin/mysql -h xxx.xxx.xxx.xxx -u $dbuser -p$passwd >2.log << EOF
use $dbname;
source data2.sql
EOF
cat 2.log | while read line
do
echo $line | tr " " ","
done > data2.csv
iconv -f utf-8 -t gb2312 data2.csv > data.csv 
mv data.csv FYHY_$today.csv
rm -rf data2.*
rm -rf 2.log

sleep 5

cp data2_muban.sql data3.sql
sed -ir 's/THIS_SAT/'"$today"'/g' data3.sql
sed -ir 's/LAST_SUN/'"$last_sun"'/g' data3.sql
/usr/bin/mysql -h xxx.xxx.xxx.xxx -u $dbuser -p$passwd >3.log << EOF
use $dbname;
source data3.sql
EOF
cat 3.log | while read line
do
echo $line | tr " " ","
done > data3.csv
iconv -f utf-8 -t gb2312 data3.csv > data.csv 
mv data.csv YZZCYH_$today.csv
rm -rf data3.*
rm -rf 3.log

sleep 5

cp data3_muban.sql data4.sql
/usr/bin/mysql -h xxx.xxx.xxx.xxx -u $dbuser -p$passwd >4.log << EOF
use $dbname;
source data4.sql
EOF
cat 4.log | while read line
do
echo $line | tr " " ","
done > data4.csv
iconv -f utf-8 -t gb2312 data4.csv > data.csv
mv data.csv YQQK_$today.csv
rm -rf data4.*
rm -rf 4.log

这个脚本内容是执行 sql 生成导出的 csv 文件,也就是领导需要的 excel 文件,这其中前期有一些坑存在,最开始,我准备用 into outfile “xxx.csv”的方式,后来发现文件都生成到数据库服务器上去了,我在本地找了半天没找到文件,原来 into outfile 是在数据库服务器生成的,这个坑有点大,主要是它能直接将查询结果导出为 csv 文件,但是还是放弃了这种方式,毕竟数据库服务器上拿文件还是不太安全。然后我就用了一种将查询结果进行转换的方式也就是以上中脚本的 for 循环的部分,本来直接转换成 csv 文件了,一下载到本地一看,打开是乱码的,后面查询版本,原来 excel 对 utf8 编码的方式打开是乱码显示的,这又坑了,巴拉巴拉又去查询了一番,发现 excel 是可以打开 gb2312 的中文格式的,那就好办了,就将生成的 csv 文件再转码一次,用 iconv -f utf-8 -t gb2312 data4.csv > data.csv 种方式可以将 utf8 格式的 data4.csv 文件转为 gb2312 格式的 data.csv 文件,不过你在 linux 系统上看 data.csv 文件以 utf8 方式查看会是乱码的,但是下载到 windows 上查询是正常的,毕竟我要的是这种效果。


        好了,以上文件生成了,接下来是考虑如何自动发送给领导了。于是又写了一个 sendmail 通过邮件方式发送附件给领导的脚本

#!/bin/bash
today=`date -d "1 days ago" +%Y-%m-%d`
/usr/local/bin/sendEmail -f youemail@xxxx.com -t lindao@xxxx.com \
    -s smtp.mxhichina.com -u "每周数据库查询" -o message-content-type=html \
    -o message-charset=utf-8 -a /home/mysql/data1/*_$today.csv -xu youremail@xxxx.com \
    -xp youremailpasswd -m "每周数据库查询-详见附件"

以上是将某日期的所有的 csv 文件发送给领导了。
关于 sendmail 的安装,建议用 yum 直接安装了,

yum install -y sendmail 
yum install -y sendmail-cf

之后就是设置定日任务了,先执行生成文件的脚本,然后执行发送邮件的脚本,比如我的定时任务是

0 9 * * 7 source /etc/profile;cd /home/mysql/data1/;sh exec.sh
30 9 * * 7 source /etc/profile;sh /home/mysql/data1/sendmail.sh

然后我的生成文件和脚本文件如图
mysql 查询导出 csv 文件并自动发送附件给领导


IT 敢客 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:mysql 查询导出 csv 文件并自动发送附件给领导
喜欢 (155)
[313176056@qq.com]
分享 (0)
IT敢客
关于作者:
“我所做的一切都是为了方便我的生活~~~“
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
(2)个小伙伴在吐槽
  1. 看看
    Amber2018-04-12 20:44 回复 未知操作系统 | 未知浏览器
  2. IT敢客
    唉,本博主自己发现了一个小小的bug,就是编码转换的时候会偶尔报错, iconv: 未知 xxx 处的非法输入序列针对这个问题,需要在iconv后面加上一个-c的参数,表示忽略错误,这可能是由于文件里本来就有非法字符产生的。签到成功!签到时间:2018-01-22 10:22:23,每日打卡,生活更精彩哦~ :grin: :grin:
    IT敢客2018-01-22 10:25 回复 Windows 7 | Maxthon 浏览器5.0