package main
import (
_ "github.com/go-sql-driver/mysql"
"database/sql"
"fmt"
"strconv"
"gopkg.in/gomail.v2"
"crypto/tls"
)
const runsql = "select id, 11 as run_sql,create_time from tb_wrong_run_sql limit 10"
const
(
dbhostsip = "192.168.1.113"
dbusername = "root"
dbpassowrd = "yeemiao1117"
dbname = "db_admin"
)
func SendMail(mailTo []string,subject string, body string ) error {
//定义邮箱服务器连接信息，如果是阿里邮箱 pass填密码，qq邮箱填授权码
mailConn := map[string]string {
"user": "yw@test.com",
"pass": "test",
"host": "mail.test.com",
"port": "25",
}
port, _ := strconv.Atoi(mailConn["port"]) //转换端口类型为int
m := gomail.NewMessage()
m.SetHeader("From","yw" + "<" + mailConn["user"] + ">") //这种方式可以添加别名，即“XD Game”， 也可以直接用<code>m.SetHeader("From",mailConn["user"])</code> 读者可以自行实验下效果
m.SetHeader("To", mailTo...) //发送给多个用户
m.SetHeader("Subject", subject) //设置邮件主题
m.SetBody("text/html", body) //设置邮件正文
d := gomail.NewDialer(mailConn["host"], port, mailConn["user"], mailConn["pass"])
d.TLSConfig = &tls.Config{InsecureSkipVerify: true}
err := d.DialAndSend(m)
fmt.Println(err)
return err
}
func get_data()(string) {
//使用database/sql包中的Open连接数据库
db, err := sql.Open("mysql", dbusername+":"+dbpassowrd+"@tcp("+dbhostsip+")/"+dbname)
if err != nil {
fmt.Println("连接数据库失败:", err)
return "error"
}
//使用DB的query方法遍历数据库数据
rows, err := db.Query(runsql)
//获取完毕释放rows，阻止更多的列举
defer rows.Close()
if err != nil {
fmt.Println("获取错误:", err)
return "error"
}
//var title = `xx实例慢查询`
var header = `<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /></head>`
var i =0
//如果有数据记录Next指针就不为true
var tr = ""
for rows.Next() {
var id int
var run_sql string
var create_time string
rows.Scan(&id, &run_sql, &create_time)
//fmt.Println(id, user, create_time, update_time)
i++
var td = ""
td = td + "<td>" + strconv.Itoa(id) + "</td>"
td = td + "<td>" + run_sql + "</td>"
td = td + "<td>" + create_time + "</td>"
tr = tr + "<tr>" + td + "</tr>"
}
var body = tr
//Err返回可能的、在迭代时出现的错误。Err需在显式或隐式调用Close方法后调用。
//err = rows.Err()
var th = `<body text='#000000'><center><font size=5 color='#dd0000'><b> `+ `不符合规范查询数:` + strconv.Itoa(i) + `</b></font></center>
<br/><table style=' font-size: 14px;' border='1' cellspacing='0' cellpadding='1' bordercolor='#000000' width='20%' align='center' ></table>
<br/><table bgcolor='#B0E0E6' style=' font-size: 14px;'border='1' cellspacing='0' cellpadding='0' bordercolor='#000000' width='95%' align='center' >
<tr bgcolor='#F79646' align='left' >
<th>id</th>
<th style=width:60px>SQL</th>
<th style='width:50px'>采集时间</th>
</tr>`
var tail = `</table></body></html>`
var mail = header + th + body + tail
return mail
}
func main(){
//fmt.Println(get_data())
//定义收件人
mailTo := []string {
"test@test.com",
}
//邮件主题为"Hello"
subject := "xx实例慢查询"
// 邮件正文
body := get_data()
//fmt.Print(body)
SendMail(mailTo, subject, body)
}
浙公网安备 33010602011771号