mysql查询结果发送到邮箱

#mail配置请移步这里传送门

1.需求

领导每天要查看谁登陆过系统，就是要看公司系统的用户登录情况。要求以邮件方式发给他。

思路：mysql查询出结果，直接通过mailx邮箱发送给指定邮箱

mysql查询出数据格式如下

问题就在于发送到企业邮箱，老板是用钉钉打开的，就是在钉钉客户端接收到的邮件，然后这个格式就乱了(比较严重)，打开就这样了（老板觉得，没对齐，看的他委屈了）

于是怎么办呢，那换个法子呗。

2. 有3个办法

<1>.使用mysql 函数 LPAD 和 RPAD 补位

1.LPAD(字段,length,'#')：字符串左填充函数
用字符串#对 字段进行左边填补直至它的长度达到 len个字符长度，然后返回 字段内容。如果 字段内容的长度长于 length，那么它将被截除到 length个字符。
mysql> SELECT LPAD('hi',6,'#'); -> '####hi'
mysql> SELECT LPAD('hi',8,'ab'); -> 'abababhi'
2.RPAD(字段,len,'#')：字符串右填充函数
用字符串 #对 字段进行右边填补直至它的长度达到 len个字符长度，然后返回 字段内容。如果 字段内容的长度长于 len，那么它将被截除到 len个字符。
mysql> SELECT RPAD('hi',5,'?'); -> 'hi???'

1.LPAD(字段,length,'#')：字符串左填充函数

用字符串#对字段进行左边填补直至它的长度达到 len个字符长度，然后返回字段内容。如果字段内容的长度长于 length，那么它将被截除到 length个字符。

mysql> SELECT LPAD('hi',6,'#'); -> '####hi'

mysql> SELECT LPAD('hi',8,'ab'); -> 'abababhi'

2.RPAD(字段,len,'#')：字符串右填充函数

用字符串 #对字段进行右边填补直至它的长度达到 len个字符长度，然后返回字段内容。如果字段内容的长度长于 len，那么它将被截除到 len个字符。

mysql> SELECT RPAD('hi',5,'?'); -> 'hi???'

具体填充多少，定义长度len多少你需要根据自身情况进行决定。下面提供解决我自身问题脚本

#!/bin/bash
# execute sql 
dirname=`date --date='0 days ago' "+%Y-%m-%d %H:%M:%S"`
echo "">user_login_log.txt
echo "蛋疼的系统的登录记录:" 
mysql -uroot -proot -e "tee user_login_log.txt;select RPAD(u.real_name,4,'丶') as '访客姓名',
 log.create_time  as '@@访问时间@1',
  LPAD(LPAD(log.type,10,'0'),11,'#') as '@系统类型@1',
  LPAD(log.remote_ip
  --log.user_agent,15,'#') as '@@访问类型@1'
from factor_prod.sys_log_api log,
  factor_prod.sys_user  u
where log.create_by = u.account
  and log.create_time between '2019-11-20' and '2019-11-28'   
  and method_name='repayStatistics'
  and log.create_by is not null  
GROUP BY log.create_by;"
mail -s "蛋疼的系统{ $dirname }用户登录记录" xxxxx@sincerd.com </root/scripts/user_login_log.txt
echo "发送完成"

#!/bin/bash

# execute sql

dirname=`date --date='0 days ago' "+%Y-%m-%d %H:%M:%S"`

echo "">user_login_log.txt

echo "蛋疼的系统的登录记录:"

mysql -uroot -proot -e "tee user_login_log.txt;select RPAD(u.real_name,4,'丶') as '访客姓名',

log.create_time as '@@访问时间@1',

LPAD(LPAD(log.type,10,'0'),11,'#') as '@系统类型@1',

LPAD(log.remote_ip

--log.user_agent,15,'#') as '@@访问类型@1'

from factor_prod.sys_log_api log,

factor_prod.sys_user u

where log.create_by = u.account

and log.create_time between '2019-11-20' and '2019-11-28'

and method_name='repayStatistics'

and log.create_by is not null

GROUP BY log.create_by;"

mail -s "蛋疼的系统{ $dirname }用户登录记录" xxxxx@sincerd.com </root/scripts/user_login_log.txt

echo "发送完成"

发送邮件内容

填充前

填充后

可见格式已经对齐了

<2>，mysql导出结果为html，mail发送html到邮箱(这种方式普通邮箱没问题，企业邮箱有些不支持，可能要开启某些东西。)

1.通过tee命令结合--html输出查询结果到html文件
  [mysql@testvm ~]$ mysql --html   #--html选项的意思产生html格式的输出.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.21-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> tee test.html    #tee命令表示将之后的内容记录到某个文件
Logging to file 'test.html'
mysql> select * from test.tab_json;    #由于使用--html选项,输出的查询结果都是html格式
<TABLE BORDER=1><TR><TH>id</TH><TH>uid</TH><TH>info</TH></TR><TR><TD>1</TD><TD>1001</TD><TD>{"name": "????", "status": 0, "addtime": "2017-10-10"}</TD></TR><TR><TD>2</TD><TD>1002</TD><TD>[{"name": "????", "status": 0, "addtime": "2017-10-11"}, {"name": "????", "status": 0, "addtime": "2017-10-12"}]</TD></TR><TR><TD>3</TD><TD>1003</TD><TD>[{"name": "????", "status": 0, "addtime": "2017-10-12"}, {"name": "????", "status": 0, "addtime": "2017-09-28"}]</TD></TR></TABLE>3 rows in set (0.00 sec)

mysql> notee;   #关闭记录日志.
Outfile disabled.

#在shell中执行
mysql --html -e "select * from test.tabjson;"  > html_test.html
同样也可以达到导出到html的效果，导出的文件最终在你当前目录下。

1.通过tee命令结合--html输出查询结果到html文件

[mysql@testvm ~]$ mysql --html #--html选项的意思产生html格式的输出.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 13

Server version: 5.7.21-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> tee test.html #tee命令表示将之后的内容记录到某个文件

Logging to file 'test.html'

mysql> select * from test.tab_json; #由于使用--html选项,输出的查询结果都是html格式

<TABLE BORDER=1><TR><TH>id</TH><TH>uid</TH><TH>info</TH></TR><TR><TD>1</TD><TD>1001</TD><TD>{"name": "????", "status": 0, "addtime": "2017-10-10"}</TD></TR><TR><TD>2</TD><TD>1002</TD><TD>[{"name": "????", "status": 0, "addtime": "2017-10-11"}, {"name": "????", "status": 0, "addtime": "2017-10-12"}]</TD></TR><TR><TD>3</TD><TD>1003</TD><TD>[{"name": "????", "status": 0, "addtime": "2017-10-12"}, {"name": "????", "status": 0, "addtime": "2017-09-28"}]</TD></TR></TABLE>3 rows in set (0.00 sec)

mysql> notee; #关闭记录日志.

Outfile disabled.

#在shell中执行

mysql --html -e "select * from test.tabjson;" > html_test.html

同样也可以达到导出到html的效果，导出的文件最终在你当前目录下。

我的脚本

#!/bin/bash
# execute sql 
dirname=`date --date='0 days ago' "+%Y-%m-%d %H:%M:%S"`
echo "">user_login_log.html
echo "蛋疼的系统的登录记录：$dirname" 
mysql -uroot -proot --html -e"tee user_login_log.html;select u.real_name as '访客姓名',
  log.create_time  as '访问时间            ',
  log.type '系统类型         ',
  log.remote_ip
  --log.user_agent as '访问类型      '
from factor_prod.sys_log_api log,
  factor_prod.sys_user  u
where log.create_by = u.account
  and log.create_time between '2019-11-20' and '2019-11-28'   
  and method_name='repayStatistics'
  and log.create_by is not null  
GROUP BY log.create_by;"
cat /root/scripts/user_login_log.html | mail -s "$(echo -e "蛋疼的系统{$dirname}登录记录\nContent-Type: text/html;")" ccdkdkkl@126.com
echo "发送完成"

#!/bin/bash

# execute sql

dirname=`date --date='0 days ago' "+%Y-%m-%d %H:%M:%S"`

echo "">user_login_log.html

echo "蛋疼的系统的登录记录：$dirname"

mysql -uroot -proot --html -e"tee user_login_log.html;select u.real_name as '访客姓名',

log.create_time as '访问时间 ',

log.type '系统类型 ',

log.remote_ip

--log.user_agent as '访问类型 '

from factor_prod.sys_log_api log,

factor_prod.sys_user u

where log.create_by = u.account

and log.create_time between '2019-11-20' and '2019-11-28'

and method_name='repayStatistics'

and log.create_by is not null

GROUP BY log.create_by;"

cat /root/scripts/user_login_log.html | mail -s "$(echo -e "蛋疼的系统{$dirname}登录记录\nContent-Type: text/html;")" ccdkdkkl@126.com

echo "发送完成"

发送后查看邮箱

另外啊，如果发送html企业邮箱不支持，可以试试用sendmail发送或者fastmail,方式都是一样的。

<3.>先导出为excle再以txt格式读取发送

我的脚本

#!/bin/bash
dirname=`date --date='0 days ago' "+%Y-%m-%d %H:%M:%S"`
echo "">user_login_log.xls
echo "操蛋系统的登录记录:" 
echo "select u.real_name as '访客姓名',
  log.create_time  as '访问时间',
  log.type '系统类型',
  log.remote_ip
  --log.user_agent as '访问类型'
from factor_prod.sys_log_api log,
  factor_prod.sys_user  u
where log.create_by = u.account
  and log.create_time between '2019-11-20' and '2019-11-28'   
  and method_name='repayStatistics'
  and log.create_by is not null  
GROUP BY log.create_by;" | mysql -uroot -proot > user_login_log.xls
mail -s "操蛋系统{ $dirname }用户登录记录" tn@126.com </root/scripts/user_login_log.xls
echo "发送完成"

#!/bin/bash

dirname=`date --date='0 days ago' "+%Y-%m-%d %H:%M:%S"`

echo "">user_login_log.xls

echo "操蛋系统的登录记录:"

echo "select u.real_name as '访客姓名',

log.create_time as '访问时间',

log.type '系统类型',

log.remote_ip

--log.user_agent as '访问类型'

from factor_prod.sys_log_api log,

factor_prod.sys_user u

where log.create_by = u.account

and log.create_time between '2019-11-20' and '2019-11-28'

and method_name='repayStatistics'

and log.create_by is not null

GROUP BY log.create_by;" | mysql -uroot -proot > user_login_log.xls

mail -s "操蛋系统{ $dirname }用户登录记录" tn@126.com </root/scripts/user_login_log.xls

echo "发送完成"

邮箱显示结果

如果你使用钉钉客户端邮箱来接收查看，抱歉那傻逼会自动干掉格式。就一傻逼软件

本文固定链接: https://www.yoyoask.com/?p=438
转载请注明: shooter 2019年12月04日于 SHOOTER 发表

最后编辑：2019-12-04

作者：shooter

这个作者貌似有点懒，什么都没有留下。

站内专栏

您可能还会对这些文章感兴趣！