【Go新手向】MySQL数据库基本CRUD

站长

2024年04月02日 21:00 · 阅读数 10

题图：本文作者拍摄于武汉·沙湖公园

Go 语言中有成熟的 ORM 库可以非常方便地进行数据库读写，各种 ORM 库的底层都是基于原生 SQL 语句来操作数据库，掌握原生 SQL 是根本。

下面来学习下 Go 语言如何使用 go-sql-driver/sql 结合原生 SQL 语句对 MySQL 数据库进行基本的 CRUD 操作。

数据库初始化

启动 MySQL 服务

使用 Docker 启动 MySQL 服务，前提是需要先安装 Docker

docker pull mysql:5.7 # 拉取 5.7 版本的 MySQL 镜像
docker run -itd --name mysql -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 mysql:5.7 # 启动 MySQL 容器实例

启动实例成功

【Go新手向】MySQL数据库基本CRUD

这里设置的 MySQL 链接密码是：123456，默认用户名是：root，服务启动在 http://localhost:3306。

创建数据库

通过 Navicat 等软件连接数据库服务，创建数据库、数据表。

连接数据库

【Go新手向】MySQL数据库基本CRUD

新建数据库

【Go新手向】MySQL数据库基本CRUD

数据库名就是数据库的名称，与后面代码连接数据库直接相关。

字符集、排序规则是 MySQL 处理和比较中非常重要组成部分，字符集直接决定了能够存储的字符范围一般就选择最全的 utf8mb4。

排序规则决定了字符的比较顺序，包括大小写敏感或不敏感、重音符号的处理等，不过多介绍，如图配置就行。

创建数据表

按照表设计规范定义通用字段:

id：自增 ID
del_flag：逻辑删除标记
reserved1：备用字段 1
reserved2：备用字段 2
reserved3：备用字段 3
create_time：创建时间
update_time：更新时间
remarks：备注

业务字段定义为如下：

mer_no：商户号
app_id：渠道号
app_name：渠道名称

数据库连接

编写 Go 代码连接数据库并创建连接实例。

安装 MySQL 数据库驱动依赖并注册

go get -u github.com/go-sql-driver/mysql

注册驱动

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

连接数据库

dsn := "root:123456@tcp(localhost:3306)/c2b_baffle?charset=utf8mb4&parseTime=True&loc=Local"
db, err := sql.Open("mysql", dsn)
if err != nil {
    log.Fatal(err)
}

这里 dsn 是数据库连接地址，完整的规范写法是这样的：[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]，其说明在这里查看

使用 Ping() 测试连接成功

【Go新手向】MySQL数据库基本CRUD

CRUD 代码实现

通用代码

连接数据库

在 main 入口函数中连接数据库，数据库连接对象保存到 db 变量

db, err := sql.Open("mysql", "root:123456@tcp(localhost:3306)/c2b_baffle?charset=utf8&parseTime=True&loc=Local")

if err != nil {
    panic(err)
}

db.SetConnMaxLifetime(time.Minute * 3)
db.SetMaxOpenConns(10)
db.SetMaxIdleConns(10)

err = db.Ping()

if err != nil {
    panic(err)
}

定义结构体、类型描述上述数据表

type AppItemAll struct {
	Id         uint      `json:"id"`
	MerNo      string    `json:"merNo"`
	AppId      string    `json:"appId"`
	AppName    string    `json:"appName"`
	Reserved1  string    `json:"reserved1"`
	Reserved2  string    `json:"reserved2"`
	Reserved3  string    `json:"reserved3"`
	CreateTime time.Time `json:"createTime"`
	UpdateTime time.Time `Json:"updateTime"`
	DelFlag    string    `json:"-"`
	Remarks    string    `json:"remarks"`
}
type AppItem struct {
	Id      uint   `json:"id"`
	MerNo   string `json:"merNo"`
	AppId   string `json:"appId"`
	AppName string `json:"appName"`
}
type AppList []AppItem

启动 http 服务

【Go新手向】MySQL数据库基本CRUD

删除分为物理删除和逻辑删除。

物理删除：通过 delete 语句删除 MySQL 数据记录；逻辑删除：标记 del_flag 为删除；批量删除是用来实现 SQL 语句的事务。

各方法的具体实现见下文。

新增记录

新增记录 SQL

INSERT INTO c2b_app_channel (mer_no, app_id, app_name, del_flag, create_time, update_time, remarks) VALUES (?,?,?,?,?,?,?)

Go 代码实现

st, err := db.Prepare("INSERT INTO c2b_app_channel (mer_no, app_id, app_name, del_flag, create_time, update_time, remarks) VALUES (?,?,?,?,?,?,?)")

result, err := st.Exec(appItem.MerNo, appItem.AppId, appItem.AppName, appItem.DelFlag, appItem.CreateTime, appItem.UpdateTime, appItem.Remarks)
if err != nil {
    rw.WriteError(err)
    return
}

id, _ := result.LastInsertId()
appItem.Id = uint(id)

rw.WriteSuccess(appItem)

appItem 是从 POST 请求 body 中解析而来。

查询列表

查询列表 SQL

SELECT mer_no,app_id,app_name,id FROM c2b_app_channel WHERE del_flag != 1 ORDER BY id DESC

这里没有添加分页查询参数

Go 代码实现

row, err := db.Query("SELECT mer_no,app_id,app_name,id FROM c2b_app_channel WHERE del_flag != 1 ORDER BY id DESC")
if err != nil {
    return nil, err
}
defer row.Close()

var list AppList

for row.Next() {
    var col1 string
    var col2 string
    var col3 string
    var col4 uint

    if err = row.Scan(&col1, &col2, &col3, &col4); err != nil {
        fmt.Println("scan fail", err)
        return nil, err
    }
    list = append(list, AppItem{
        Id:      col4,
        MerNO:   col1,
        AppId:   col2,
        AppName: col3,
    })
}

使用 db.Query 查询数据表，再使用 for row.Next() 遍历结果转换为 AppList 切片结构。

查询详情

查询详情 SQL

SELECT id,mer_no,app_id,app_name,remarks,create_time,update_time FROM c2b_app_channel WHERE id = ?

Go 代码实现

st, err := db.Prepare("SELECT id,mer_no,app_id,app_name,remarks,create_time,update_time FROM c2b_app_channel WHERE id = ?")
if err != nil {
    rw.WriteError(err)
    defer st.Close()
    return
}

rows, err := st.Query(requestParam.Id)
if err != nil {
    rw.WriteError(err)
    return
}

var appItemAll = AppItemAll{}

var id uint
var merNo string
var appId string
var appName string
var remarks string
var createTime time.Time
var updateTime time.Time

for rows.Next() {
    rows.Scan(&id, &merNo, &appId, &appName, &remarks, &createTime, &updateTime)
    appItemAll.Id = id
    appItemAll.MerNo = merNo
    appItemAll.AppId = appId
    appItemAll.AppName = appName
    appItemAll.Remarks = remarks
    appItemAll.CreateTime = createTime
    appItemAll.UpdateTime = updateTime
}

rw.WriteSuccess(appItemAll)

使用 db.Query 查询数据表，再使用 for rows.Next() 遍历结果转换为 AppItemAll 结构体。

更新记录

更新记录 SQL

UPDATE c2b_app_channel SET mer_no = ?, app_id = ?, app_name = ?, remarks = ? where id = ?

Go 代码实现

st, err := db.Prepare("UPDATE c2b_app_channel SET mer_no = ?, app_id = ?, app_name = ?, remarks = ? where id = ?")
if err != nil {
    rw.WriteError(err)
    return
}

result, err := st.Exec(requestParam.MerNo, requestParam.AppId, requestParam.AppName, requestParam.Remarks, requestParam.Id)
if err != nil {
    rw.WriteError(err)
    return
}
i, _ := result.RowsAffected()
if i != 1 {
    rw.WriteError(errors.New("更新失败"))
    return
}

rw.WriteSuccess(nil)

使用 st.Exec 执行 SQL，根据返回 result.RowsAffected 代表的影响记录数判断是否更新成功。

逻辑删除

逻辑删除 SQL

UPDATE c2b_app_channel SET del_flag = 1 WHERE id = ?

逻辑删除是通过更新 del_flag 标记为 1 实现

Go 代码实现

st, err := db.Prepare("UPDATE c2b_app_channel SET del_flag = 1 WHERE id = ?")
if err != nil {
    rw.WriteError(err)
    defer st.Close()
    return
}

result, err := st.Exec(requestParam.Id)
if err != nil {
    rw.WriteError(err)
    return
}

affected, _ := result.RowsAffected()

if affected != 1 {
    rw.WriteError(errors.New("删除失败"))
    return
}

rw.WriteSuccess(nil)

使用 st.Exec 执行 SQL，根据返回 result.RowsAffected 代表的影响记录数判断是否逻辑删除成功。

物理删除

物理删除 SQL

DELETE FROM c2b_app_channel WHERE id = ?

使用主键 id 直接删除记录

Go 代码实现

st, err := db.Prepare("DELETE FROM c2b_app_channel WHERE id = ?")
if err != nil {
    rw.WriteError(err)
    defer st.Close()
    return
}

result, err := st.Exec(requestParam.Id)
if err != nil {
    rw.WriteError(err)
    return
}

if affected, _ := result.RowsAffected(); affected > 1 {
    rw.WriteError(errors.New("删除失败"))
    return
}

rw.WriteSuccess(nil)

使用 st.Exec 执行 SQL，根据返回 result.RowsAffected 代表的影响记录数判断是否物理删除成功。

批量逻辑删除（事务）

批量逻辑删除 SQL

UPDATE c2b_app_channel SET del_flag = 1 WHERE id in [?,?,?]

这是 SQL 批量逻辑删除，并非事务

事务逻辑删除 SQL

BEGIN;
	UPDATE c2b_app_channel SET del_flag = 1 WHERE app_id = ?;
	UPDATE c2b_app_channel SET del_flag = 1 WHERE app_id = ?;
	UPDATE c2b_app_channel SET del_flag = 1 WHERE app_id = ?;
COMMIT;

执行结果如下：

【Go新手向】MySQL数据库基本CRUD

通过 Go 代码实现事务

ctx, err := db.Begin()
if err != nil {
    rw.WriteError(err)
    return
}

for _, id := range requestParam.Ids {
    result, err := ctx.Exec("UPDATE c2b_app_channel SET del_flag = 1 WHERE id = ?", id)
    if err != nil {
        ctx.Rollback()
        rw.WriteError(err)
        break
    }
    affected, err := result.RowsAffected()
    if err != nil || affected > 1 {
        ctx.Rollback()
        if err == nil {
            rw.WriteError(errors.New(fmt.Sprintf("删除记录 %d 失败", id)))
        } else {
            rw.WriteError(err)
        }
        break
    }
}

ctx.Commit()
rw.WriteSuccess(nil)

将 POST 请求参数解析为 requestParam ，然后对其 ids 属性进行遍历；
遍历开始前用 db.Begin() 开启事务
遍历每一步执行单条逻辑删除，若报错用 ctx.Roolback() 回滚事务并终止遍历
遍历完成用 ctx.Commit() 提交事务

使用事务可保证，要么同时执行成功，要么同时执行失败，不会存在部分成功。

总结

本文从零到一搭建了一个 MySQL 数据库，并实现了基本的数据库增删改查功能，同时结合业务实现了逻辑删除、事务操作的功能。

所有这些接口也都使用 HTTP 服务提供为接口服务，对 Go 新手上手 Web 服务端开发很友好，可是尝试练习一下。

文中项目完整代码在这里：github.com/JohnieXu/de…。

参考资料

Go-MySQL-Driver github.com/go-sql-driv…
database/sql golang.org/pkg/databas…