sqlx库——在go中写sql

sqlx库——在go中写sql

sqlx可以认为是Go语言内置的database/sql的超集，基于内置的连接数据库的库，sqlx做了非常好的拓展，使用起来更方便快捷，对于有sql基础的，使用起来会比gorm更顺手

下载sqlx依赖

在goland终端中输入下面代码，获取sqlx依赖

go get github.com/jmoiron/sqlx

连接数据库

注：本文使用的是MySQL数据库，由于sqlx不支持创建操作，数据库需要提前在database中创建

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

一定要记得导入MySQL数据库的驱动，使用匿名导入，因为我们没使用到库中的方法，只是利用MySQL驱动

var db *sqlx.DB

func initDB() (err error) {
    // 用户名，密码，端口号以及数据库名称，根据自己需要去修改
    // charset指定编码格式
    // parseTime可以自动解析数据库中的时间数据，方便导入到go语言中
	dsn := "用户名:密码.@tcp(127.0.0.1:3306)/数据库名称?charset=utf8mb4&parseTime=True&loc=Local"
    // 也可以使用MustConnect连接不成功就panic
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return err
	}
	// 连接池最大容量设置
	db.SetMaxOpenConns(20) // 与数据库建立连接的最大数目
	db.SetMaxIdleConns(10) // 连接池中的最大闲置连接数
	return err
}

常用操作

查询

建结构体/建表

首先需要先创建一个结构体，结构体的内容要与数据库表中的字段对应起来，数据类型需要保持一致

// user 结构体对应数据库的表
type user struct {
	Id   uint 	`db:"id"`	// 利用结构体标签，将成员名称与数据库字段名称一一对应
	Name string	`db:"name"`
	Age  uint	`db:"age"`
}

**注意：**结构体内的成员名称，首字母必须大写，保证能通过反射取到该字段

查询单条语句

利用db.Get(&结构体, 查询语句, 1)

// QueryRowDemo 查询单条语句
func QueryRowDemo() {
	sqlStr := "select id,name,age from user where id = ?"
	var u user
	err := db.Get(&u, sqlStr, 1) // 查询一条，这里利用反射，直接把查询出来的数据赋值到结构体的字段中
	if err != nil {
		fmt.Println("db get failed,err:", err)
		return
	}
	fmt.Printf("id=%d,name=%s,age=%d\n", u.Id, u.Name, u.Age)
}

查询多条语句

利用db.Select(&结构体, 查询语句, 0)

// 查询多条数据示例
func queryMultiRowDemo() {
	sqlStr := "select id, name, age from user where id > ?"
	var users []user	// 结构体切片，多个数据
	err := db.Select(&users, sqlStr, 0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	fmt.Printf("users:%#v\n", users)
}

注意：方法内的第一个参数是结构体的指针，确保能对其进行更改，而不是单纯的值拷贝

插入、更新、删除(基础版exec)

利用sqlx中的exec方法，sqlx中的exec方法与原生内置sql的exec方法基本一致

利用Exec方法可以执行插入、更新和删除操作，主要不同点就在于操作所对应的sql语句

// 插入数据
func insertRowDemo() {
	sqlStr := "insert into user(name, age) values (?,?)"
    // 插入一个名字为“lin”，年龄为20的数据
	ret, err := db.Exec(sqlStr, "lin", 20)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	theID, err := ret.LastInsertId() // 获取最新插入数据的id
	if err != nil {
		fmt.Printf("get lastinsert ID failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert success, the id is %d.\n", theID)
}

// 更新数据
func updateRowDemo() {
	sqlStr := "update user set age=? where id = ?"
    // 将id = 6的年龄更新为18
	ret, err := db.Exec(sqlStr, 18, 6)
	if err != nil {
		fmt.Printf("update failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("update success, affected rows:%d\n", n)
}

// 删除数据
func deleteRowDemo() {
	sqlStr := "delete from user where id = ?"
    // 删除id = 6的数据
	ret, err := db.Exec(sqlStr, 6)
	if err != nil {
		fmt.Printf("delete failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("delete success, affected rows:%d\n", n)
}

NamedExec(命名版exec)

由于普通的exec操作，需要在sql中使用“？”作为占位符，当变量很多时，容易出现顺序错误，变量遗漏等问题

使用NamedExec可以用来绑定sql语句中的**“？”和结构体或者map中的同名字段**

使用起来更为变量，且一 一对应

使用**“：变量名”的形式取代“？”，可以更清晰呈现需要操作的字段，保证一比一对应**关系

func insertUserDemo()(err error){
	sqlStr := "INSERT INTO user (name,age) VALUES (:name,:age)"
	_, err = db.NamedExec(sqlStr,
		map[string]interface{}{
			"name": "Tai",
			"age": 20,
		})
	return
}

NamedQuery(命名版查询)

与NamedExec一致，只不过这里换成了查询操作

使用map做命名查询

sqlStr := "SELECT * FROM user WHERE name=:name"
rows, err := db.NamedQuery(sqlStr, map[string]interface{}{"name": "lin"})
if err != nil {
	fmt.Printf("db.NamedQuery failed, err:%v\n", err)
	return
}
defer rows.Close()

使用结构体做命名查询

u := user{
	Name: "lin",
}
rows, err := db.NamedQuery(sqlStr, u)
if err != nil {
	fmt.Printf("db.NamedQuery failed, err:%v\n", err)
	return
}
defer rows.Close()

遍历查询结果

for rows.Next(){
	var u user
	err := rows.StructScan(&u)	// 不能直接使用Scan去映射扫描，
    							// 因为我们传入的只是user中的部分字段，并不是所有字段
	if err != nil {
		fmt.Printf("scan failed, err:%v\n", err)
		continue
	}
	fmt.Printf("user:%#v\n", u)
}

事务操作

利用sqlx中的db.Beginx()和 tx.Exec()方法

例子

func transactionDemo2()(err error) {
	tx, err := db.Beginx() // 开启事务
	if err != nil {
		fmt.Printf("begin trans failed, err:%v\n", err)
		return err
	}
    
    // 利用defer来进行最终事务的提交和回滚判断
	defer func() {
        // 利用recover捕获当前函数可能出现的panic，然后进行恢复
		if p := recover(); p != nil {
			tx.Rollback()
			panic(p) // 先进行事务回滚，再panic
		} else if err != nil {
			fmt.Println("rollback")
			tx.Rollback() // 如果当前函数出现错误，也会进行事务回滚操作
		} else {
			err = tx.Commit() // 如果没panic也没有错误，事务提交
			fmt.Println("commit")
		}
	}()

    // 更改两个人的年龄，必须保证两个人的年龄都更改成功，才提交事务
	sqlStr1 := "Update user set age=20 where id=?"
	rs, err := tx.Exec(sqlStr1, 1)
	if err!= nil{
		return err
	}
	n, err := rs.RowsAffected()	// 受到影响的行数
	if err != nil {
		return err
	}
	if n != 1 {
		return errors.New("exec sqlStr1 failed")
	}
    
	sqlStr2 := "Update user set age=50 where i=?"
	rs, err = tx.Exec(sqlStr2, 5)
	if err!=nil{
		return err
	}
	n, err = rs.RowsAffected()	// 受到影响的行数
	if err != nil {
		return err
	}
	if n != 1 {
		return errors.New("exec sqlStr1 failed")
	}
	return err	//返回的这些错误，会在函数即将结束时，在defer中去进行判断
}

强大的sqlx.In

使用 sqlx.In可以实现批量操作数据

**前提：**需要我们的结构体实现一个 driver.Valuer接口

func (u user) Value() (driver.Value, error) {
	return []interface{}{u.Name, u.Age}, nil	// 返回名称和年龄以及nil
}

使用sqlx.In实现批量插入

// BatchInsertUsers 使用sqlx.In帮我们拼接语句和参数, 注意传入的参数是[]interface{}
func BatchInsertUsers(users []interface{}) error {
	query, args, _ := sqlx.In(
		"INSERT INTO user (name, age) VALUES (?), (?), (?)",
		users..., // 如果arg实现了 driver.Valuer, sqlx.In 会通过调用 Value()来展开它
	)
	fmt.Println(query) // 查看生成的querystring
	fmt.Println(args)  // 查看生成的args
	_, err := db.Exec(query, args...)
	return err
}

使用NamedExec实现批量插入

这个方法极其方便，推荐使用

// BatchInsertUsers2 使用NamedExec实现批量插入
func BatchInsertUsers2(users []*user) error {
	_, err := db.NamedExec("INSERT INTO user (name, age) VALUES (:name, :age)", users)
	return err
}

以上两个插入方法示例

u1 := User{Name: "a", Age: 10}
u2 := User{Name: "b", Age: 20}
u3 := User{Name: "c", Age: 30}

// 方法1
users1 := []interface{}{u1, u2, u3}
err = BatchInsertUsers1(users1)
if err != nil {
	fmt.Printf("BatchInsertUsers1 failed, err:%v\n", err)
}

// 方法2
users2 := []*user{&u1, &u2, &u3}
err = BatchInsertUsers2(users2)
if err != nil {
	fmt.Printf("BatchInsertUsers2 failed, err:%v\n", err)
}

sqlx.In查询

一次性给定多个id，将这些id对应的数据都查出来

// QueryByIDs 根据给定ID查询
// 传入一个待查找的id切片，返回user切片代表多个数据和错误信息err
func QueryByIDs(ids []int)(users []user, err error){
	// 动态填充id
    // 返回query查询语句和args参数
	query, args, err := sqlx.In("SELECT name, age FROM user WHERE id IN (?)", ids)
	if err != nil {
		return
	}    
	// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定。
	query = db.Rebind(query)

	err = db.Select(&users, query, args...)
	return
}

按照指定顺序的sqlx.In查询

sqlx.In默认是按照升序进行查询返回结果，如果需要按照指定顺序进行查询返回结果，可以使用FIND_IN_SET函数

这是利用MySQL中的ORDER BY FIND_IN_SET函数去进行排序

第一个参数是 按什么字段排序

第二个参数是 按什么顺序排序

// QueryAndOrderByIDs 按照指定id查询并维护顺序
func QueryAndOrderByIDs(ids []int)(users []user, err error){
	// 动态填充id
	strIDs := make([]string, 0, len(ids))
	for _, id := range ids {
		strIDs = append(strIDs, fmt.Sprintf("%d", id))
	}
	query, args, err := sqlx.In("SELECT name, age FROM user WHERE id IN (?) ORDER BY FIND_IN_SET(id, ?)", ids, strings.Join(strIDs, ","))
	if err != nil {
		return
	}

	// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它
	query = db.Rebind(query)

	err = db.Select(&users, query, args...)
	return
}

本文介绍了sqlx库的基本使用方法，希望可以对大家有所帮助