您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

sqlx操作MySQL实战及其原理

时间:2022-05-06 12:51:39  来源:  作者:萤火架构

sqlx是Golang中的一个知名三方库,其为Go标准库database/sql提供了一组扩展支持。使用它可以方便的在数据行与Golang的结构体、映射和切片之间进行转换,从这个角度可以说它是一个ORM框架;它还封装了一系列地常用SQL操作方法,让我们用起来更爽。

sqlx实战

这里以操作MySQL的增删改查为例。

准备工作

先要准备一个MySQL,这里通过Docker快速启动一个MySQL 5.7。

docker run -d --name mysql1 -p 3306:3306 -e MYSQL_ROOT_PASSword=123456 mysql:5.7

在MySQL中创建一个名为test的数据库:

CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

数据库中创建一个名为Person的数据库表:

CREATE TABLE test.Person (
    Id integer auto_increment NOT NULL,
    Name VARCHAR(30) NULL,
    City VARCHAR(50) NULL,
    AddTime DATETIME NOT NULL,
    UpdateTime DATETIME NOT NULL,
    CONSTRAINT Person_PK PRIMARY KEY (Id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;

然后创建一个Go项目,安装sqlx:

go get Github.com/jmoiron/sqlx

因为操作的是MySQL,还需要安装MySQL的驱动:

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

编写代码

添加引用

添加sqlx和mysql驱动的引用:

import (
    "log"

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

MySQL的驱动是隐式注册的,并不会在接下来的程序中直接调用,所以这里加了下划线。

创建连接

操作数据库前需要先创建一个连接:

    db, err := sqlx.Connect("mysql", "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8mb4&parseTime=true&loc=Local")
    if err != nil {
        log.Println("数据库连接失败")
    }

这个连接中指定了程序要用MySQL驱动,以及MySQL的连接地址、用户名和密码、数据库名称、字符编码方式;这里还有两个参数parseTime和loc,parseTime的作用是让MySQL中时间类型的值可以映射到Golang中的time.Time类型,loc的作用是设置time.Time的值的时区为当前系统时区,不使用这个参数的话保存到的数据库的就是UTC时间,会和北京时间差8个小时。

增删改查

sqlx扩展了DB和Tx,继承了它们原有的方法,并扩展了一些方法,这里主要看下这些扩展的方法。

增加

通用占位符的方式:

insertResult := db.MustExec("INSERT INTO Person (Name, City, AddTime, UpdateTime) VALUES (?, ?, ?, ?)", "Zhang San", "Beijing", time.Now(), time.Now())
lastInsertId, _ := insertResult.LastInsertId()
log.Println("Insert Id is ", lastInsertId)

这个表的主键使用了自增的方式,可以通过返回值的LastInsertId方法获取。

命名参数的方式:

insertPerson := &Person{
        Name:       "Li Si",
        City:       "Shanghai",
        AddTime:    time.Now(),
        UpdateTime: time.Now(),
    }
    insertPersonResult, err := db.NamedExec("INSERT INTO Person (Name, City, AddTime, UpdateTime) VALUES(:Name, :City, :AddTime, :UpdateTime)", insertPerson)

命名参数的方式是sqlx扩展的,这个方式就是常说的ORM。这里需要注意给struct字段添加上db标签:

type Person struct {
    Id         int       `db:"Id"`
    Name       string    `db:"Name"`
    City       string    `db:"City"`
    AddTime    time.Time `db:"AddTime"`
    UpdateTime time.Time `db:"UpdateTime"`
}

struct中的字段名称不必和数据库字段相同,只需要通过db标签映射正确就行。注意SQL语句中使用的命名参数需要是db标签中的名字。

除了可以映射struct,sqlx还支持map,请看下面这个示例:

insertMap := map[string]interface{}{
        "n": "Wang Wu",
        "c": "HongKong",
        "a": time.Now(),
        "u": time.Now(),
    }
    insertMapResult, err := db.NamedExec("INSERT INTO Person (Name, City, AddTime, UpdateTime) VALUES(:n, :c, :a, :u)", insertMap)

再来看看批量增加的方式:

insertPersonArray := []Person{
        {Name: "BOSIMA", City: "Wu Han", AddTime: time.Now(), UpdateTime: time.Now()},
        {Name: "BOSSMA", City: "Xi An", AddTime: time.Now(), UpdateTime: time.Now()},
        {Name: "BOMA", City: "Cheng Du", AddTime: time.Now(), UpdateTime: time.Now()},
    }
    insertPersonArrayResult, err := db.NamedExec("INSERT INTO Person (Name, City, AddTime, UpdateTime) VALUES(:Name, :City, :AddTime, :UpdateTime)", insertPersonArray)
    if err != nil {
        log.Println(err)
        return
    }
    insertPersonArrayId, _ := insertPersonArrayResult.LastInsertId()
    log.Println("InsertPersonArray Id is ", insertPersonArrayId)

这里还是采用命名参数的方式,参数传递一个struct数组或者切片就可以了。这个执行结果中也可以获取到最后插入数据的自增Id,不过实测返回的是本次插入的第一条的Id,这个有点别扭,但是考虑到增加多条只获取一个Id的场景似乎没有,所以也不用多虑。

除了使用struct数组或切片,也可以使用map数组或切片,这里就不贴出来了,有兴趣的可以去看文末给出的Demo链接。

删除

删除也可以使用通用占位符和命名参数的方式,并且会返回本次执行受影响的行数,某些情况下可以使用这个数字判断SQL实际有没有执行成功。

deleteResult := db.MustExec("Delete from Person where Id=?", 1)
log.Println(deleteResult.RowsAffected())

deleteMapResult, err := db.NamedExec("Delete from Person where Id=:Id",
                                     map[string]interface{}{"Id": 1})
if err != nil {
  log.Println(err)
  return
}
log.Println(deleteMapResult.RowsAffected())

修改

Sqlx对修改的支持和删除差不多:

updateResult := db.MustExec("Update Person set City=?, UpdateTime=? where Id=?", "Shanghai", time.Now(), 1)
log.Println(updateResult.RowsAffected())

updateMapResult, err := db.NamedExec("Update Person set City=:City, UpdateTime=:UpdateTime where Id=:Id",
                                     map[string]interface{}{"City": "Chong Qing", "UpdateTime": time.Now(), "Id": 1})
if err != nil {
  log.Println(err)
}
log.Println(updateMapResult.RowsAffected())

查询

Sqlx对查询的支持比较多。

使用Get方法查询一条:

getPerson := &Person{}
db.Get(getPerson, "select * from Person where Name=?", "Zhang San")

使用Select方法查询多条:

selectPersons := []Person{}
db.Select(&selectPersons, "select * from Person where Name=?", "Zhang San")

只查询部分字段:

getId := new(int64)
db.Get(getId, "select Id from Person where Name=?", "Zhang San")

selectTowFieldSlice := []Person{}
db.Select(&selectTowFieldSlice, "select Id,Name from Person where Name=?", "Zhang San")

selectNameSlice := []string{}
db.Select(&selectNameSlice, "select Name from Person where Name=?", "Zhang San")

从上可以看出如果只查询部分字段,还可以继续使用struct;特别的只查询一个字段时,使用基本数据类型就可以了。

除了这些高层次的抽象方法,Sqlx也对更低层次的查询方法进行了扩展:

查询单行:

row = db.QueryRowx("select * from Person where Name=?", "Zhang San")
    if row.Err() == sql.ErrNoRows {
        log.Println("Not found Zhang San")
    } else {
        queryPerson := &Person{}
        err = row.StructScan(queryPerson)
        if err != nil {
            log.Println(err)
            return
        }
        log.Println("QueryRowx-StructScan:", queryPerson.City)
    }

查询多行:

    rows, err := db.Queryx("select * from Person where Name=?", "Zhang San")
    if err != nil {
        log.Println(err)
        return
    }
    for rows.Next() {
        rowSlice, err := rows.SliceScan()
        if err != nil {
            log.Println(err)
            return
        }
        log.Println("Queryx-SliceScan:", string(rowSlice[2].([]byte)))
    }

命名参数Query:

rows, err = db.NamedQuery("select * from Person where Name=:n", map[string]interface{}{"n": "Zhang San"})

查询出数据行后,这里有多种映射方法:StructScan、SliceScan和MapScan,分别对应映射后的不同数据结构。

预处理语句

对于重复使用的SQL语句,可以采用预处理的方式,减少SQL解析的次数,减少网络通信量,从而提高SQL操作的吞吐量。

下面的代码展示了sqlx中如何使用stmt查询数据,分别采用了命名参数和通用占位符两种传参方式。

bosima := Person{}
bossma := Person{}

nstmt, err := db.PrepareNamed("SELECT * FROM Person WHERE Name = :n")
if err != nil {
  log.Println(err)
  return
}
err = nstmt.Get(&bossma, map[string]interface{}{"n": "BOSSMA"})
if err != nil {
  log.Println(err)
  return
}
log.Println("NamedStmt-Get1:", bossma.City)
err = nstmt.Get(&bosima, map[string]interface{}{"n": "BOSIMA"})
if err != nil {
  log.Println(err)
  return
}
log.Println("NamedStmt-Get2:", bosima.City)

stmt, err := db.Preparex("SELECT * FROM Person WHERE Name=?")
if err != nil {
  log.Println(err)
  return
}
err = stmt.Get(&bosima, "BOSIMA")
if err != nil {
  log.Println(err)
  return
}
log.Println("Stmt-Get1:", bosima.City)
err = stmt.Get(&bossma, "BOSSMA")
if err != nil {
  log.Println(err)
  return
}
log.Println("Stmt-Get2:", bossma.City)

对于上文增删改查的方法,sqlx都有相应的扩展方法。与上文不同的是,需要先使用SQL模版创建一个stmt实例,然后执行相关SQL操作时,不再需要传递SQL语句。

数据库事务

为了在事务中执行sqlx扩展的增删改查方法,sqlx必然也对数据库事务做一些必要的扩展支持。

tx, err = db.Beginx()
    if err != nil {
        log.Println(err)
        return
    }
    tx.MustExec("INSERT INTO Person (Name, City, AddTime, UpdateTime) VALUES (?, ?, ?, ?)", "Zhang San", "Beijing", time.Now(), time.Now())
    tx.MustExec("INSERT INTO Person (Name, City, AddTime, UpdateTime) VALUES (?, ?, ?, ?)", "Li Si Hai", "Dong Bei", time.Now(), time.Now())
    err = tx.Commit()
    if err != nil {
        log.Println(err)
        return
    }
    log.Println("tx-Beginx is successful")

上面这段代码就是一个简单的sqlx数据库事务示例,先通过db.Beginx开启事务,然后执行SQL语句,最后提交事务。

如果想要更改默认的数据库隔离级别,可以使用另一个扩展方法:

tx, err = db.BeginTxx(context.Background(), &sql.TxOptions{Isolation: sql.LevelRepeatableRead})

sqlx干了什么

通过上边的实战,基本上就可以使用sqlx进行开发了。为了更好地使用sqlx,我们可以再了解下sqlx是怎么做到上边这些扩展的。

Go的标准库中没有提供任何具体数据库的驱动,只是通过database/sql库定义了操作数据库的通用接口。sqlx中也没有包含具体数据库的驱动,它只是封装了常用SQL的操作方法,让我们的SQL写起来更爽。

MustXXX

sqlx提供两个几个MustXXX方法。

Must方法是为了简化错误处理而出现的,当开发者确定SQL操作不会返回错误的时候就可以使用Must方法,但是如果真的出现了未知错误的时候,这个方法内部会触发panic,开发者需要有一个兜底的方案来处理这个panic,比如使用recover。

这里是MustExec的源码:

func MustExec(e Execer, query string, args ...interface{}) sql.Result {
    res, err := e.Exec(query, args...)
    if err != nil {
        panic(err)
    }
    return res
}

NamedXXX

对于需要传递SQL参数的方法, sqlx都扩展了命名参数的传参方式。这让我们可以在更高的抽象层次处理数据库操作,而不必关心数据库操作的细节。

这种方法的内部会解析我们的SQL语句,然后从传递的struct、map或者slice中提取命名参数对应的值,然后形成新的SQL语句和参数集合,再交给底层database/sql的方法去执行。

这里摘抄一些代码:

func NamedExec(e Ext, query string, arg interface{}) (sql.Result, error) {
    q, args, err := bindNamedMApper(BindType(e.DriverName()), query, arg, mapperFor(e))
    if err != nil {
        return nil, err
    }
    return e.Exec(q, args...)
}

NamedExec 内部调用了 bindNamedMapper,这个方法就是用于提取参数值的。其内部分别对Map、Slice和Struct有不同的处理。

func bindNamedMapper(bindType int, query string, arg interface{}, m *reflectx.Mapper) (string, []interface{}, error) {
    ...
    switch {
    case k == reflect.Map && t.Key().Kind() == reflect.String:
        ...
        return bindMap(bindType, query, m)
    case k == reflect.Array || k == reflect.Slice:
        return bindArray(bindType, query, arg, m)
    default:
        return bindStruct(bindType, query, arg, m)
    }
}

以批量插入为例,我们的代码是这样写的:

insertPersonArray := []Person{
        {Name: "BOSIMA", City: "Wu Han", AddTime: time.Now(), UpdateTime: time.Now()},
        {Name: "BOSSMA", City: "Xi An", AddTime: time.Now(), UpdateTime: time.Now()},
        {Name: "BOMA", City: "Cheng Du", AddTime: time.Now(), UpdateTime: time.Now()},
    }
    insertPersonArrayResult, err := db.NamedExec("INSERT INTO Person (Name, City, AddTime, UpdateTime) VALUES(:Name, :City, :AddTime, :UpdateTime)", insertPersonArray)

经过bindNamedMapper处理后SQL语句和参数是这样的:

sqlx操作MySQL实战及其原理

 


sqlx操作MySQL实战及其原理

 

这里使用了反射,有些人可能会担心性能的问题,对于这个问题的常见处理方式就是缓存起来,sqlx也是这样做的。

XXXScan

这些Scan方法让数据行到对象的映射更为方便,sqlx提供了StructScan、SliceScan和MapScan,看名字就可以知道它们映射的数据结构。而且在这些映射能力的基础上,sqlx提供了更为抽象的Get和Select方法。

这些Scan内部还是调用了database/sql的Row.Scan方法。

以StructScan为例,其使用方法为:

queryPerson := &Person{}
err = row.StructScan(queryPerson)

经过sqlx处理后,调用Row.Scan的参数是:

sqlx操作MySQL实战及其原理

 


以上就是本文的主要内容,如有错漏,欢迎指正。

老规矩,Demo程序已经上传到Github,欢迎访问:
https://github.com/bosima/go-demo/tree/main/sqlx-mysql



Tags:sqlx   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,不构成投资建议。投资者据此操作,风险自担。如有任何标注错误或版权侵犯请与我们联系,我们将及时更正、删除。
▌相关推荐
sqlx操作MySQL实战及其原理
sqlx是Golang中的一个知名三方库,其为Go标准库database/sql提供了一组扩展支持。使用它可以方便的在数据行与Golang的结构体、映射和切片之间进行转换,从这个角度可以说它是一...【详细内容】
2022-05-06  Search: sqlx  点击:(303)  评论:(0)  加入收藏
▌简易百科推荐
MySQL 核心模块揭秘
server 层会创建一个 SAVEPOINT 对象,用于存放 savepoint 信息。binlog 会把 binlog offset 写入 server 层为它分配的一块 8 字节的内存里。 InnoDB 会维护自己的 savepoint...【详细内容】
2024-04-03  爱可生开源社区    Tags:MySQL   点击:(10)  评论:(0)  加入收藏
MySQL 核心模块揭秘,你看明白了吗?
为了提升分配 undo 段的效率,事务提交过程中,InnoDB 会缓存一些 undo 段。只要同时满足两个条件,insert undo 段或 update undo 段就能被缓存。1. 关于缓存 undo 段为了提升分...【详细内容】
2024-03-27  爱可生开源社区  微信公众号  Tags:MySQL   点击:(18)  评论:(0)  加入收藏
MySQL:BUG导致DDL语句无谓的索引重建
对于5.7.23之前的版本在评估类似DDL操作的时候需要谨慎,可能评估为瞬间操作,但是实际上线的时候跑了很久,这个就容易导致超过维护窗口,甚至更大的故障。一、问题模拟使用5.7.22...【详细内容】
2024-03-26  MySQL学习  微信公众号  Tags:MySQL   点击:(14)  评论:(0)  加入收藏
从 MySQL 到 ByteHouse,抖音精准推荐存储架构重构解读
ByteHouse是一款OLAP引擎,具备查询效率高的特点,在硬件需求上相对较低,且具有良好的水平扩展性,如果数据量进一步增长,可以通过增加服务器数量来提升处理能力。本文将从兴趣圈层...【详细内容】
2024-03-22  字节跳动技术团队    Tags:ByteHouse   点击:(29)  评论:(0)  加入收藏
MySQL自增主键一定是连续的吗?
测试环境:MySQL版本:8.0数据库表:T (主键id,唯一索引c,普通字段d)如果你的业务设计依赖于自增主键的连续性,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不...【详细内容】
2024-03-10    dbaplus社群  Tags:MySQL   点击:(14)  评论:(0)  加入收藏
准线上事故之MySQL优化器索引选错
1 背景最近组里来了许多新的小伙伴,大家在一起聊聊技术,有小兄弟提到了MySQL的优化器的内部策略,想起了之前在公司出现的一个线上问题,今天借着这个机会,在这里分享下过程和结论...【详细内容】
2024-03-07  转转技术  微信公众号  Tags:MySQL   点击:(33)  评论:(0)  加入收藏
MySQL数据恢复,你会吗?
今天分享一下binlog2sql,它是一款比较常用的数据恢复工具,可以通过它从MySQL binlog解析出你要的SQL,并根据不同选项,可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。主要...【详细内容】
2024-02-22  数据库干货铺  微信公众号  Tags:MySQL   点击:(54)  评论:(0)  加入收藏
如何在MySQL中实现数据的版本管理和回滚操作?
实现数据的版本管理和回滚操作在MySQL中可以通过以下几种方式实现,包括使用事务、备份恢复、日志和版本控制工具等。下面将详细介绍这些方法。1.使用事务:MySQL支持事务操作,可...【详细内容】
2024-02-20  编程技术汇    Tags:MySQL   点击:(54)  评论:(0)  加入收藏
MySQL数据库如何生成分组排序的序号
经常进行数据分析的小伙伴经常会需要生成序号或进行数据分组排序并生成序号。在MySQL8.0中可以使用窗口函数来实现,可以参考历史文章有了这些函数,统计分析事半功倍进行了解。...【详细内容】
2024-01-30  数据库干货铺  微信公众号  Tags:MySQL   点击:(55)  评论:(0)  加入收藏
mysql索引失效的场景
MySQL中索引失效是指数据库查询时无法有效利用索引,这可能导致查询性能显著下降。以下是一些常见的MySQL索引失效的场景:1.使用非前导列进行查询: 假设有一个复合索引 (A, B)。...【详细内容】
2024-01-15  小王爱编程  今日头条  Tags:mysql索引   点击:(88)  评论:(0)  加入收藏
相关文章
    无相关信息
站内最新
站内热门
站内头条