目录

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

本文参考mysql 查询导出 csv 文件并自动发送附件给领导一篇文章，最近发现一个小小的 bug，然后也是优化了一下。

一、需求拆分

1，首先我们要写出sql语句，可以和开发一起完成。

2，然后将其变成模板文件，比如 data1_muban.sql,data2_muban.sql,data3_muban.sql,data4_muban.sql；

3，将sql 中的日期时间用变量名代替，比如我这边的时间是 BETWEEN ‘LAST_SUN’ AND ‘THIS_SAT’ 其中 LAST_SUN和 THIS_SAT 是指上一个周日和这个周六，毕竟是一周的数据，可以随意命名，根据自己需求来，

4，考虑到这个变量始终都是要变成日期格式的，所以模板文件是不能动的，要生存临时 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

sed -i 's/t/,/g' 1.log

cat 1.log | while read line

do

echo $line

done > data1.csv

iconv -c -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

sed -i 's/t/,/g' 2.log

cat 2.log | while read line

do

echo $line

done > data2.csv

iconv -c -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

sed -i 's/t/,/g' 3.log

cat 3.log | while read line

do

echo $line

done > data3.csv

iconv -c -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

sed -i 's/t/,/g' 4.log

cat 4.log | while read line

do

echo $line

done > data4.csv

iconv -c -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。

三、配置邮箱sendmail+定时任务

3.1 部署sendmail

yum install -y sendmail

yum install -y sendmail-cf

3.2 添加定时任务

30 8 * * 7 source /etc/profile;cd /home/mysql/data1/;sh exec.sh

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

3.3 邮件自动推送脚本

#!/bin/bash

today=`date -d "1 days ago" +%Y-%m-%d`

/usr/local/bin/sendEmail -f youemail@xxxx.com -t lingdao@xxxx.com lindao2@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 "每周数据库查询-详见附件"

## 另外之余，如果发送的邮件有多位领导，可以多个收件人，sendmail 的方式如下所示，就是第一个收件人后，空格再增加一个收件人。

以上是将某日期的所有的 csv 文件发送给领导了。

四、问题总结

问题BUG：基于 mysql 查询出来的结果是 tab 分割字段的，而之前用的脚本处理，是用空格进行处理，以至于后来查询结果中有字段值里面有空格，而导致最后出来的报表中有错乱的现象，还有有些字段内容本身就是空的，也会出现栏目交错的现象！

之前的逻辑在这里：用 tr 命令将空行换成逗号，之后进行导入 csv 文件；

/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

后来，将 1.log 拿到本地查看，发现每个字段之间是 tab 分割的，这就表示之前将空格分割的逻辑是错误的；按理来说，只要将空格替换成t 的 tab 就可以了，后来发现还是不行，因为循环读 1.log 内容的时候，每一行将 tab 都换成空行了。所以只能先将所有的 tab 换成逗号，然后再进行转化成 csv 文件。处理后的逻辑如下所示

/usr/bin/mysql -h xxx.xxx.xxx.xxx -u $dbuser -p$passwd >1.log << EOF

use $dbname;

source data1.sql

EOF

sed -i 's/t/,/g' 1.log

cat 1.log | while read line

do

echo $line

done > data1.csv