MySQL

import and init

import (
	"database/sql"
)

var (
	DB         *sql.DB //資料庫連線
)

// DBconn for global
func DBconn() (err error) {
	mysql := fmt.Sprintf("%s:%s@tcp(%s:3306)/%s?parseTime=true&loc=Local", Config.DB[Env].User, Config.DB[Env].Password, Config.DB[Env].Host, Config.DB[Env].Database)
	DB, err = sql.Open("mysql", mysql)
	// DB.SetMaxpenConns(50) // 設定最大連線
	// DB.SetMaxIdleConns(50)
	return
}

Use

Conn

// 取得 db connection
dbctx, cancel := context.WithCancel(context.Background())
defer cancel()
conn, err := globals.DB.Conn(dbctx)
if err != nil {
	return
}
defer conn.Close()

Query

// 只選擇一行
row := conn.QueryRowContext(dbctx, "SELECT account,password,name,secret,`lock`,is_admin FROM `users` WHERE `account` = ?", u.Account)
if err = row.Scan(&user.Account, &user.Password, &user.Name, &user.Secret, &user.Lock, &user.IsAdmin); err != nil {
	if err == sql.ErrNoRows {
		err = nil
		return
	}
	return
}

// 選擇多行
rows, err := conn.QueryContext(dbctx, "SELECT account,name,`lock`,registerID,is_admin FROM `users` ")
if err != nil {
	return
}
for rows.Next() {
	var user Users
	rows.Scan(&user.Account, &user.Name, &user.Lock, &user.RegisterID, &user.IsAdmin)
	users = append(users, user)
}

Exec

_, err = conn.ExecContext(dbctx, "INSERT INTO `users` (`account`,`password`,`name`) VALUES(?,?,?)", u.Account, u.Password, u.Name)
if err != nil {
	return
}