Day.16 应用中学习- 资料库操作 ( golang / sql )

身处後端开发一定会接触到写code去操作资料库的需求，所以今天主题来介绍一点实务应用，透过Golang操作mysql资料库，程序如何控制事务的提交与回滚＆资料异动操作～

使用套件: Go-MySQL-Driver

连线资料库设置

package main

import (
	"database/sql"
	"fmt"

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

func main() {

	//Init connect object.  content-> account:password@tcp(host_ip:port)/db_name
	dbu, err := sql.Open("mysql", "root:1234@tcp(127.0.0.1:3306)/user?charset=utf8&parseTime=True")
	if err != nil {
		fmt.Println("open mysql error", err)
		return
	}

	//close conn.
	defer dbu.Close()

	//Create mysql connect.
	err = dbu.Ping()
	if err != nil {
		fmt.Println("create mysql connect  error", err)
		return
	}

	/*
		>以下连接池设定
		    注意点:
			(1.)mysql连线预设保存时间为8小时，闲置超过8小时会被mysql断开变失效连线。
				查询: show variables like '%wait_timeout%';

				注意: 使用到被mysql断开的连线会产生ERROR -> packets.go:36: unexpected EOF

			(2.)mysql最大连线数。
				查询: show variables like '%max_connections%';

	*/

	//设置最大并发连线数，超过连线数需等待，直到其中连接被释放并变为空闲。
	dbu.SetMaxOpenConns(100)
	//设置最大的空闲连接数，适当的设定空闲连接数(会占用内存)将提高性能，减少从头建立新连接的可能。
	dbu.SetMaxIdleConns(10)
	//设置连线的生命周期，过期後无法重用。
	dbu.SetConnMaxLifetime(30)
}

(1.)建立资料库
CREATE DATABASE user;

(2.)建立资料表
CREATE TABLE `user_lists` (
  `user_id` bigint(20) NOT NULL COMMENT '使用者ID',
  `account` varchar(50) NOT NULL COMMENT '帐号',
  `level` tinyint(5) NOT NULL COMMENT '层级',
  `last_login_time` int(11) NOT NULL COMMENT '最後登入时间',
  `create_time` int(11) NOT NULL COMMENT '建立时间',
  PRIMARY KEY (`user_id`)
);

进入正题~透过mysql driver对user_list这张表进行资料存取操作。

Exec() -> 执行写入异动SQL操作。

//异动数据 ( 新增/删除/更新 )
func ExecSQL(dbu *sql.DB) error {

	//INSERT INTO user_list(user_id,account,level,last_login_time,create_time) values(8723131, "siangx", 1, 1630385990, 1530385990);

	sql := "INSERT INTO user_list(user_id,account,level,last_login_time,create_time) values(?,?,?,?,?)"

	res, err := dbu.Exec(sql, 8723131, "siangx", 1, 1630385990, 1530385990)
	if err != nil {
		fmt.Println("insert user table error", err)
		return err
	}
	//返回执行影响笔数
	count, err := res.RowsAffected()
	if err != nil {
		fmt.Println("get RowsAffected error", err)
		return err
	}
	fmt.Printf("[OK] %d row affected ", count)

	return nil
}

Query() -> 执行数据库查询操作(多笔) & QueryRow() -> 执行数据库查询操作(单笔)。

//查询数据
func QuerySQL(dbu *sql.DB) {

	//先定义欲搜寻栏位的资料结构
	type User struct {
		UserID  int64
		Account string
		Level   int
	}

	/*
		var userLists User
		//单笔查询
		if err := dbu.QueryRow("SELECT user_id,account,level FROM user_list WHERE user_id = ?", 8723131).
			Scan(&userLists.UserID, &userLists.Account, &userLists.Level); err != nil {
			fmt.Println("query user table error", err)
		}
		log.Println(userLists)
	*/

	//查询内容可能包含一笔(含)以上使用Query

	//定义好要回传的栏位结构
	var userList []User

	//send query sql to mysql
	rows, err := dbu.Query("SELECT user_id,account,level FROM user_list WHERE user_id = ?", 8723131)
	if err != nil {
		fmt.Println("query user table error", err)
	}

	// close sql.Rows -> 重要: 记得要有这个动作去手动释放连线。
	defer rows.Close()

	//read sql response rows -> 遍历读取出来的row资讯存入定义好的User结构
	for rows.Next() {
		user := User{}
		err := rows.Scan(&user.UserID, &user.Account, &user.Level)
		if err != nil {
			fmt.Println("get user row data error", err)
		}
		userList = append(userList, user)
	}

	fmt.Printf("query response: %+v:", userList)
}