sqlx操作MySQL实战及其ORM原理

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也是这样做的。

XXXScan

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

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

以StructScan为例，其使用方法为：

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

经过sqlx处理后，调用Row.Scan的参数是：

---

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

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

收获更多架构知识，请关注微信公众号 萤火架构。原创内容，转载请注明出处。