Go语言MySQL操作

发表于 更新于 分类于
Go语言MySQL操作

创建表

1
2
3
4
5
6
7
8
9
10
11
12
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `userid` int NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `nickname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `type` int NULL DEFAULT NULL,
  `createtime` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`userid`)
);

INSERT INTO `t_user` VALUES (1, '张剑', '123456', '码客说', 1, '2024-02-18 17:29:07');

实体类

1
2
3
4
5
6
7
8
9
10
package model

type Tuser struct {
	Userid     int    `db:"userid"`
	UserName   string `db:"username"`
	Password   string `db:"password"`
	Nickname   string `db:"nickname"`
	Type       int    `db:"type"`
	Createtime string `db:"createtime"`
}

下载依赖

1
2
go get github.com/go-sql-driver/mysql
go get github.com/jmoiron/sqlx

工具类

db/mydb.go

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
package mydb

import (
	"fmt"

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

var Db *sqlx.DB

var (
	userName  string = "zhangjian"
	password  string = "Test_123456"
	ipAddrees string = "dbtest.xhkjedu.com"
	port      int    = 3306
	dbName    string = "z-wiki"
	charset   string = "utf8"
)

// OpenDB 打开DB
func OpenDB() *sqlx.DB {
	var err error
	//"用户名:密码@[连接方式](主机名:端口号)/数据库名"
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, password, ipAddrees, port, dbName, charset)
	Db, err = sqlx.Open("mysql", dsn) // 设置连接数据库的参数
	if err != nil {
		fmt.Printf("mysql connect failed, detail is [%v]", err.Error())
	}
	Db.SetMaxOpenConns(100)
	Db.SetMaxIdleConns(10)
	return Db
}

// CloseDb 关闭数据库
func CloseDb() {
	err := Db.Close()
	if err != nil {
		fmt.Println("数据库关闭失败")
	}
	Db = nil
}

func PingDbHasErr() bool {
	if Db == nil {
		OpenDB()
	}

	err := Db.Ping() //连接数据库
	if err != nil {
		fmt.Println("数据库连接失败")
		return true
	}
	return false
}

增删改查

基本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
package dao

import (
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"z-wiki/db"
	"z-wiki/model"
)

// UserLogin 查询单个用户
func UserLogin(username string, pwd string) *model.UserVo {
	if mydb.PingDbHasErr() {
		return nil
	}
	var err error
	var user = new(model.UserVo)
	err = mydb.Db.Get(user, "select * from t_user where username=?&&password=?", username, pwd)
	if err != nil {
		fmt.Printf("query faied, error:%v\n", err.Error())
		return nil
	}

	fmt.Println("user", ":", user)
	return user
}

// UserList 查询用户列表
func UserList() []model.UserVo {
	if mydb.PingDbHasErr() {
		return nil
	}
	var err error
	var userList []model.UserVo
	err = mydb.Db.Select(&userList, "select * from t_user")
	if err != nil {
		fmt.Printf("query faied, error:%v\n", err.Error())
		return nil
	}

	fmt.Println("userList", ":", userList)
	return userList
}

// UserAdd 添加 返回自增长字段的值
func UserAdd(username string, password string, nickname string, mtype int, createtime int) int64 {
	if mydb.PingDbHasErr() {
		return 0
	}
	result, err := mydb.Db.Exec("insert into t_user (username, password,nickname,type,createtime) values(?,?,?,?,?)", username, password, nickname, mtype, createtime)
	if err != nil {
		fmt.Printf("data insert faied, error:%v\n", err.Error())
		return 0
	}
	id, _ := result.LastInsertId()
	fmt.Printf("insert success, last id:%d\n", id)
	return id
}

// UserDelete 删除 返回删除的数量
func UserDelete(userid int) int64 {
	if mydb.PingDbHasErr() {
		return 0
	}
	result, err := mydb.Db.Exec("delete from t_user where userid = ?", userid)
	if err != nil {
		fmt.Printf("delete faied, error:%v\n", err.Error())
		return 0
	}
	num, _ := result.RowsAffected()
	fmt.Printf("delete success, affected rows:%d\n", num)
	return num
}

// UserUpdate 更新 返回更新的数量
func UserUpdate(username string, userid int) int64 {
	if mydb.PingDbHasErr() {
		return 0
	}
	result, err := mydb.Db.Exec("update t_user set username = ? where userid = ?", username, userid)
	if err != nil {
		fmt.Printf("update faied, error:%v\n", err.Error())
		return 0
	}
	num, _ := result.RowsAffected()
	fmt.Printf("update success, affected rows:%d\n", num)
	return num
}


// UserListCount 获取总条数
func UserListCount() int {
	if mydb.PingDbHasErr() {
		return 0
	}
	var err error
	var count int
	err = mydb.Db.QueryRow("select count(*) from t_user").Scan(&count)
	if err != nil {
		fmt.Printf("query faied, error:%v\n", err.Error())
		return 0
	}
	return count
}

分页和筛选

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
// ArticleListByManager 查询列表
func ArticleListByManager(search string, categoryid int, status int, currPage int, pageSize int) ([]model.ArticleVo, int) {
	var offset = (currPage - 1) * pageSize
	if mydb.PingDbHasErr() {
		return []model.ArticleVo{}, 0
	}
	var err error
	var mList []model.ArticleVo
	var count int

	var selectSql string
	var selectParas []interface{}

	var countSql string
	var countParas []interface{}

	selectSql = "select articleid,title,description,wordnum,tags,visitnum,createtime,updatetime,status from t_article where 1=1 "
	countSql = "select count(*) from t_article where 1=1 "
	if search != "" {
		var searchStr = "%" + search + "%"
		selectSql += " and title like ? or outline like ?"
		selectParas = append(selectParas, searchStr, searchStr)
		countSql += " and title like ? or outline like ?"
		countParas = append(countParas, searchStr, searchStr)
	}

	if categoryid != 0 {
		selectSql += " and categoryid=?"
		selectParas = append(selectParas, categoryid)
		countSql += " and categoryid=?"
		countParas = append(countParas, categoryid)
	}

	if status != 0 {
		selectSql += " and status=?"
		selectParas = append(selectParas, status)
		countSql += " and status=?"
		countParas = append(countParas, status)
	}

	selectSql += " LIMIT ? OFFSET ?"
	selectParas = append(selectParas, pageSize, offset)
	err = mydb.Db.Select(&mList, selectSql, selectParas...)
	err = mydb.Db.QueryRow(countSql, countParas...).Scan(&count)

	if err != nil {
		fmt.Printf("query faied, error:%v\n", err.Error())
		return []model.ArticleVo{}, 0
	}
	if mList == nil {
		mList = []model.ArticleVo{}
	}
	return mList, count
}

调用

1
2
3
4
mydb.OpenDB()
defer mydb.CloseDb()
userList := dao.UserList()
fmt.Println("userList:", userList)

其中

  • Get 查询单条数据
  • Select 查询多条数据

注意

defer 语句会将其后面跟随的语句进行延迟处理，在 defer 归属的函数即将返回时，将延迟处理的语句按 defer 的逆序进行执行，

也就是说，先被 defer 的语句最后被执行，最后被 defer 的语句，最先被执行。

连接池的实现关键在于SetMaxOpenConns和SetMaxIdleConns，其中：

SetMaxOpenConns用于设置最大打开的连接数，默认值为0表示不限制。
SetMaxIdleConns用于设置闲置的连接数。

设置最大的连接数，可以避免并发太高导致连接mysql出现too many connections的错误。

设置闲置的连接数则当开启的一个连接使用完成后可以放在池里等候下一次使用。