go mysql查询操作数据库

2017/12/05
go mysql 协程解决日志分析
go很简洁,像php一样好学
又有很强大的协程
值得好好学习下

查询mysql日志库,转移到另一个库中进行分析统计
运用mysql查询,运用协程

正文

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"os"
	"strconv"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

func main() {

	db, err := sql.Open("mysql", "xxxx")
	dbtwo, err := sql.Open("mysql", "xxxx")

	if err != nil {
		fmt.Println(err)
	}
	defer db.Close()
	//GetDateUserAccess(db, dbtwo)
	chdate := make(chan string)
	chweek := make(chan string)
	chmonth := make(chan string)
	go insertDateUserAccess(db, dbtwo, chdate)
	go insertWeekUserAccess(db, dbtwo, chweek)
	go insertMonthUserAccess(db, dbtwo, chmonth)

	fmt.Println(<-chdate)
	fmt.Println(<-chweek)
	fmt.Println(<-chmonth)
}

func insertMonthUserAccess(db *sql.DB, dbtwo *sql.DB, chmonth chan string) {
	t := time.Now()
	tm1 := time.Date(t.Year(), t.Month(), t.Day(), 0, 0, 0, 0, t.Location())
	z := tm1.Unix() - (3600 * 24 * 30)

	sqlTwo := "SELECT role,user_id,access_time,access_date FROM user_access where access_date>=" + strconv.FormatInt(z, 10) + " and access_date < " + strconv.FormatInt(tm1.Unix(), 10) + " group by role,user_id"
	rows, _ := dbtwo.Query(sqlTwo)
	tx, _ := db.Begin()

	for rows.Next() {
		var role int
		var user_id int
		var access_time string
		var access_date int64
		err := rows.Scan(&role, &user_id, &access_time, &access_date)
		if err != nil {
			panic(err)
		}
		tx.Exec("INSERT INTO date_user_access(role,user_id,access_time,access_date,type) values(?,?,?,?,?)", role, user_id, access_time, access_date, 3)
	}
	tx.Commit()
	chmonth <- "month success..."
}

func insertWeekUserAccess(db *sql.DB, dbtwo *sql.DB, chweek chan string) {
	t := time.Now()
	tm1 := time.Date(t.Year(), t.Month(), t.Day(), 0, 0, 0, 0, t.Location())
	z := tm1.Unix() - (3600 * 24 * 7)

	sqlTwo := "SELECT role,user_id,access_time,access_date FROM user_access where access_date>=" + strconv.FormatInt(z, 10) + " and access_date < " + strconv.FormatInt(tm1.Unix(), 10) + " group by role,user_id"
	rows, _ := dbtwo.Query(sqlTwo)
	tx, _ := db.Begin()

	for rows.Next() {
		var role int
		var user_id int
		var access_time string
		var access_date int64
		err := rows.Scan(&role, &user_id, &access_time, &access_date)
		if err != nil {
			panic(err)
		}
		tx.Exec("INSERT INTO date_user_access(role,user_id,access_time,access_date,type) values(?,?,?,?,?)", role, user_id, access_time, access_date, 2)
	}
	tx.Commit()

	chweek <- "week success..."
}

type UserAccess struct {
	role        int64
	user_id     int64
	access_time int64
	access_date int64
}

var UserA UserAccess

func GetDateUserAccess(db *sql.DB, dbtwo *sql.DB) {
	t := time.Now()

	tm1 := time.Date(t.Year(), t.Month(), t.Day(), 0, 0, 0, 0, t.Location())
	tm2 := tm1.AddDate(0, 0, 1)
	sql := "SELECT role,user_id,access_time,access_date FROM user_access where access_date>=" + strconv.FormatInt(tm1.Unix(), 10) + " and access_date < " + strconv.FormatInt(tm2.Unix(), 10) + " group by role,user_id"
	rows, _ := dbtwo.Query(sql)
	var UserList []UserAccess
	for rows.Next() {
		err := rows.Scan(&UserA.role, &UserA.user_id, &UserA.access_time, &UserA.access_date)
		if err != nil {
			panic(err)
		}
		UserList = append(UserList, UserA)
	}

	mapList := map[string][]UserAccess{}
	mapList["list"] = UserList
	fmt.Println(mapList)
	jsonList, _ := json.Marshal(mapList)
	fmt.Println(string(jsonList))
	os.Exit(1)
}

func insertDateUserAccess(db *sql.DB, dbtwo *sql.DB, chdate chan string) {
	t := time.Now()

	tm1 := time.Date(t.Year(), t.Month(), t.Day(), 0, 0, 0, 0, t.Location())
	tm2 := tm1.AddDate(0, 0, 1)
	sql := "SELECT role,user_id,access_time,access_date FROM user_access where access_date>=" + strconv.FormatInt(tm1.Unix(), 10) + " and access_date < " + strconv.FormatInt(tm2.Unix(), 10) + " group by role,user_id"
	rows, _ := dbtwo.Query(sql)
	tx, _ := db.Begin()

	for rows.Next() {
		var role int
		var user_id int
		var access_time string
		var access_date int64
		err := rows.Scan(&role, &user_id, &access_time, &access_date)
		if err != nil {
			panic(err)
		}
		tx.Exec("INSERT INTO date_user_access(role,user_id,access_time,access_date) values(?,?,?,?)", role, user_id, access_time, access_date)
	}
	tx.Commit()
	chdate <- "date success..."
}




这个是循环查询多条,可以封装成函数,查询单条,多条

个人随笔

安静的等待,你若盛开,蝴蝶自来

承接高质量 网站开发 app开发 如有需要请点击About联系

@承鹏辉