Golang-MySQL

安装驱动

方式一：设置代理并安装驱动

$ go env -w GOPROXY=https://goproxy.cn
$ go get github.com/go-sql-driver/mysql
$ go get github.com/jmoiron/sqlx

方式二：使用mod创建项目

# 初始化
$ go mod init go-mysql
# 在go.mod中添加
require github.com/go-sql-driver/mysql v1.6.0
# 下载依赖
$ go mod download

数据准备

建表语句

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` tinyint unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

初始数据

INSERT INTO `demo`.`user`(`id`, `name`, `age`) VALUES (1, 'Khighness', 19);
INSERT INTO `demo`.`user`(`id`, `name`, `age`) VALUES (2, 'RabbishK', 18);
INSERT INTO `demo`.`user`(`id`, `name`, `age`) VALUES (3, 'UnknownK', 17);

项目结构

go-mysql
├── .idea
├── db
│   ├── delete.go
│   ├── insert.go
│   ├── mysql.go
│   ├── select.go
│   ├── transaction.go
│   ├── update.go
│   └── user.go
├── db_test.go
├── go.mod
└── go.sum

MySQL连接池

package db

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"log"
	"time"
)

/**
 * @author KHighness
 * @since 2021-04-20
 */

var MysqlDB *sql.DB
var MysqlDBERR error

const (
	USERNAME = "root"
	PASSWORD = "KAG1823"
	HOST     = "8.133.183.149"
	PORT     = "3306"
	DATABASE = "demo"
	CHARSET  = "utf8"
)

// 初始化链接
func init() {
	dbDSN := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=%s", USERNAME, PASSWORD, HOST, PORT, DATABASE, CHARSET)

	// 打开连接失败
	MysqlDB, MysqlDBERR = sql.Open("mysql", dbDSN)
	if MysqlDBERR != nil {
		log.Println("dbDSN: " + dbDSN)
		panic("数据源配置错误: " + MysqlDBERR.Error())
	}

	// 最大连接数
	MysqlDB.SetMaxOpenConns(100)
	// 闲置连接数
	MysqlDB.SetMaxIdleConns(20)
	// 最大连接周期
	MysqlDB.SetConnMaxLifetime(100 * time.Second)

	if MysqlDBERR = MysqlDB.Ping(); nil != MysqlDBERR {
		panic("数据库连接失败: " + MysqlDBERR.Error())
	}

}

用户结构体

package db

/**
 * @author KHighness
 * @since 2021-04-20
 */

// 用户结构体
type User struct {
	Id int64 `db:"id"`
	Name string `db:"name"`
	Age int `db:"age"`
}

INSERT操作

package db

import "log"

/**
 * @author KHighness
 * @since 2021-04-20
 */

// 保存用户
func SaveUser(user User)  {
	res, _ := MysqlDB.Exec("INSERT INTO user(name, age) VALUES (?, ?)", user.Name, user.Age)
	lastInsertId, _ := res.LastInsertId()
	rowsAffected, _ := res.RowsAffected()
	log.Printf("插入ID => [%d], 影响行数 => [%d]", lastInsertId, rowsAffected)
}

SELECT操作

package db

import (
	"log"
)

/**
 * @author KHighness
 * @since 2021-04-20
 */

// 根据ID查询
func QueryById(id int)  {
	user := new(User)
	row := MysqlDB.QueryRow("SELECT * FROM user WHERE id = ?", id)
	if err :=row.Scan(&user.Id,&user.Name,&user.Age); err != nil{
		log.Printf("scan failed, err:%v",err)
		return
	}
	log.Printf("query result => [%s]\n", user)
}

// 查询所有
func QueryList() {
	users := make([]User, 0)
	rows, _ := MysqlDB.Query("SELECT * FROM user")
	// 遍历
	var user User
	for rows.Next() {
		_ = rows.Scan(&user.Id, &user.Name, &user.Age)
		users = append(users, user)
	}
	log.Printf("query result => [%s]\n", users)
}

UPDATE操作

package db

import "log"

/**
 * @author KHighness
 * @since 2021-04-20
 */

// 更新用户
func UpdateById(user User)  {
	res, _ := MysqlDB.Exec("UPDATE user SET name = ?, age = ? WHERE id = ? ", user.Name, user.Age, user.Id)
	lastInsertId, _ := res.LastInsertId()
	rowsAffected, _ := res.RowsAffected()
	log.Printf("更新ID => [%d], 影响行数 => [%d]", lastInsertId, rowsAffected)
}

DELETE操作

package db

import "log"

/**
 * @author KHighness
 * @since 2021-04-20
 */

// 删除用户
func DeleteById(id int)  {
	res, _ := MysqlDB.Exec("DELETE FROM user WHERE id = ?", id)
	lastInsertId, _ := res.LastInsertId()
	rowsAffected, _ := res.RowsAffected()
	log.Printf("删除ID => [%d], 影响行数 => [%d]", lastInsertId, rowsAffected)
}

事务操作

import (
	"log"
	"strconv"
)

/**
 * @author KHighness
 * @since 2021-04-20
 */

// 事务: 批量插入5个用户
func BatchSaveUser(users [5]User)  {
	// 开始事务
	tx, _ := MysqlDB.Begin()
	var total int64 = 0
	for _, user := range users {
		res, _ := MysqlDB.Exec("INSERT INTO user(name, age) VALUES(?, ?)", user.Name, user.Age)
		aff, _ := res.RowsAffected()
		total += aff
	}
	// 转字符串比较
	if  strconv.FormatInt(total, 10) == strconv.Itoa(len(users)) { 	// 提交事务
		_ = tx.Commit()
		log.Print("事务提交")
	} else { // 回滚
		_ = tx.Rollback()
		log.Print("事务回滚")
	}

}

单元测试

package main

import (
	"fmt"
	"go-mysql/db"
	"testing"
)

/**
 * @author KHighness
 * @since 2021-04-20
 */


// 测试查询
func TestQueryById(t *testing.T) {
	db.QueryById(1)
}

// 测试查询所有
func TestQueryList(t *testing.T) {
	db.QueryList()
}

// 测试保存
func TestSaveUser(t *testing.T) {
	user := db.User{Name: "FlowerK", Age: 16}
	db.SaveUser(user)
}

// 测试更新
func TestUpdateById(t *testing.T)  {
	user := db.User{Id: 1, Name: "K", Age: 20}
	db.UpdateById(user)
}

// 测试删除
func TestDeleteById(t *testing.T)  {
	db.DeleteById(4)
}

// 测试事务
func TestBatchSaveUser(t *testing.T)  {
	var users [5]db.User
	for i := 0; i < 5; i++ {
		users[i] = db.User{Name: fmt.Sprintf("%s-%d", "K", i + 1), Age: i + 1}
	}
	db.BatchSaveUser(users)
}