初识golang
逻辑如下:
程序开启http服务器接收请求,且每隔20秒查询一次表auto_backup中flag为0的值,如果有查到且计划执行时间小于当前时间,则将表to_do的数据抓出来,通过邮件发送,完成后将数据表flag update 为1
表结构:
MySQL []> desc auto_backup;
+----------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| datetime | int(11) | NO | | NULL | |
| to_do | varchar(2000) | NO | | NULL | |
| flag | int(11) | NO | MUL | NULL | |
+----------+---------------+------+-----+---------+----------------+
4 rows in set (0.04 sec)
表数据:
MySQL []> SELECT
-> `id`,
-> FROM_UNIXTIME(`auto_backup`.`datetime`) AS `datetimes`,
-> `auto_backup`.`to_do` AS `to_do`,
-> `auto_backup`.`flag` AS `flag`
-> FROM
-> `auto_backup`
-> order by datetimes desc
-> limit 1;
+----+---------------------+-----------------+------+
| id | datetimes | to_do | flag |
+----+---------------------+-----------------+------+
| 63 | 2019-04-02 17:30:00 | 哈哈哈测试 | 1 |
+----+---------------------+-----------------+------+
1 row in set (0.04 sec)
效果如下:
添加记录
发送记录
服务器log:
代码如下:
package main
// 导入相关包
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"gopkg.in/gomail.v2"
"log"
"net/http"
"strconv"
"time"
)
// http 服务器
func Httpd_beiwang(w http.ResponseWriter, r *http.Request) {
r.ParseForm()
// 赛选掉访问的url路径为 /favicon.ico
if "/favicon.ico" != r.URL.Path {
log.Println(r.Form)
// 获取url参数
time_value, timeOK := r.Form["time"]
beiwang_value, beiwangOK := r.Form["beiwang"]
// 如果两个参数正确,则判断参数正确,需要将定时任务写到数据库中去
if timeOK && beiwangOK {
log.Println("参数判断ok,参数值为:")
log.Println(time_value[0])
log.Println(beiwang_value[0])
// 定义初始时间
const base_format = "\"2006-01-02 15:04\""
// 转化为unix时间
loc, _ := time.LoadLocation("Local")
parse_time, err := time.ParseInLocation(base_format, time_value[0], loc)
unix_parse_time := parse_time.Unix()
log.Println("Unix 时间为: ", unix_parse_time)
if err != nil {
fmt.Println(err)
}
// 需要Insert的sql
insert_sql := fmt.Sprintf("insert into auto_backup (datetime , to_do , flag) values (%d,%s , 0);", unix_parse_time, beiwang_value[0])
log.Println("执行的sql: ", insert_sql)
// 执行sql
results, err := db.Exec(insert_sql)
if err != nil {
log.Println("sql执行错误,错误内容为:")
log.Println(err)
} else {
log.Println("执行成功,", results)
fmt.Fprintf(w, "request ok")
}
} else {
fmt.Fprintf(w, "request error")
}
}
}
// 发送邮件
func SendMail(body string) error {
mailConn := map[string]string{
"user": "from_user",
"pass": "password",
"host": "smtp",
"port": "port",
}
port, _ := strconv.Atoi(mailConn["port"])
m := gomail.NewMessage()
//m.SetHeader("From" , "XD Game" + "<" mailConn["user"] + ">")
m.SetHeader("From", "XD Game"+"<"+mailConn["user"]+">")
m.SetHeader("To", "to_user")
m.SetHeader("Subject", "执行计划")
m.SetBody("text/html", body)
d := gomail.NewDialer(mailConn["host"], port, mailConn["user"], mailConn["pass"])
err := d.DialAndSend(m)
return err
}
// 返回需要执行定时任务的id,返回值为Int数组
func Deal_Exec_id(db *sql.DB) []int {
var send_id []int
// 获取当前时间戳
now_t := time.Now()
// 执行查询sql
select_datetime := fmt.Sprintf("select id from auto_backup where flag = 0 and datetime <= %d", now_t.Unix())
// 执行sql
select_rows, err := db.Query(select_datetime)
log.Println(select_datetime)
if err != nil {
log.Println(err)
}
for select_rows.Next() {
var id int
if err := select_rows.Scan(&id); err != nil {
log.Println(err)
}
//fmt.Println(id)
// 将查询的结果追加到send_id中
send_id = append(send_id, id)
}
log.Println("查询结果返回ID", send_id)
// 将send_id数组返回
return send_id
}
func Deal_SendMail(db *sql.DB, send_id []int) {
var to_do string
for id := range (send_id) {
fmt.Println(send_id[id])
// 根据send_id查询 messages
select_sql := fmt.Sprintf("SELECT to_do FROM auto_backup WHERE id = '%d';", send_id[id])
log.Println(select_sql)
err := db.QueryRow(select_sql).Scan(&to_do)
if err != nil {
log.Println(err)
}
log.Println("发送的内容", to_do)
SendMail(to_do)
// 执行 update sql
update_sql := fmt.Sprintf("UPDATE auto_backup SET flag = 1 WHERE id = '%d'", send_id[id])
log.Println(update_sql)
results, err := db.Exec(update_sql)
if err != nil {
log.Println(err)
} else {
log.Println(results)
}
}
}
func start_jobs() {
for {
log.Println("Auto Time jobs Start")
send_id := Deal_Exec_id(db)
if 0 == len(send_id) {
log.Println("未找到相关jobs")
} else {
Deal_SendMail(db, send_id)
}
// 每隔20秒刷新一次数据库,查看是否存在新数据
time.Sleep(time.Second * 20)
}
}
// 定义*sql.DB 变量
var db *sql.DB
func main() {
// 连接mysql
var err error
c := make(chan int, 1)
db, err = sql.Open("mysql", "username:password@tcp(host)/database_name")
// 在main退出时关闭链接
defer db.Close()
if err != nil {
log.Println("Connect DB failed")
log.Println(err)
}
// 开启http服务器
http.HandleFunc("/", Httpd_beiwang)
// 开启协程
go func() {
err = http.ListenAndServe("0.0.0.0:9090", nil)
if err != nil {
log.Fatal(err)
}
}()
go start_jobs()
<- c
}