golang连接sqlx库的操作使用指南

本文整理自网络，侵删。

目录

• sqlx库使用指南
• sqlx介绍
• 安装sqlx
• 基本使用
• 连接数据库
• 查询
• 插入、更新和删除
• NamedExec
• NamedQuery
• 事务操作
• sqlx.In
• sqlx.In的批量插入示例
• 表结构
• 结构体
• bindvars（绑定变量）
• 自己拼接语句实现批量插入
• 使用sqlx.In实现批量插入
• 使用NamedExec实现批量插入
• sqlx.In的查询示例
• in查询
• in查询和FIND_IN_SET函数

sqlx库使用指南

在项目中我们通常可能会使用database/sql连接MySQL数据库。本文借助使用sqlx实现批量插入数据的例子，介绍了sqlx中可能被你忽视了的sqlx.In和DB.NamedExec方法。

sqlx介绍

在项目中我们通常可能会使用database/sql连接MySQL数据库。sqlx可以认为是Go语言内置database/sql的超集，它在优秀的内置database/sql基础上提供了一组扩展。这些扩展中除了大家常用来查询的Get(dest interface{}, ...) error和ｓｅｌｅｃｔ(dest interface{}, ...) error外还有很多其他强大的功能。

安装sqlx

go get github.com/jmoiron/sqlx

基本使用

连接数据库

var db *sqlx.DBfunc initDB() (err error) {dsn := "user:password@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"// 也可以使用MustConnect连接不成功就panicdb, err = sqlx.Connect("mysql", dsn)if err != nil {fmt.Printf("connect DB failed, err:%v\n", err)return}db.SetMaxOpenConns(20)db.SetMaxIdleConns(10)return}

查询

查询单行数据示例代码如下：

// 查询单条数据示例func queryRowDemo() {sqlStr := "ｓｅｌｅｃｔ id, name, age from user where id=?"var u usererr := db.Get(&u, sqlStr, 1)if err != nil {fmt.Printf("get failed, err:%v\n", err)return}fmt.Printf("id:%d name:%s age:%d\n", u.ID, u.Name, u.Age)}

查询多行数据示例代码如下：

// 查询多条数据示例func queryMultiRowDemo() {sqlStr := "ｓｅｌｅｃｔ id, name, age from user where id > ?"var users []usererr := db.ｓｅｌｅｃｔ(&users, sqlStr, 0)if err != nil {fmt.Printf("query failed, err:%v\n", err)return}fmt.Printf("users:%#v\n", users)}

插入、更新和删除

sqlx中的ｅｘｅｃ方法与原生sql中的ｅｘｅｃ使用基本一致：

// 插入数据func insertRowDemo() {sqlStr := "ｉｎｓｅｒｔ into user(name, age) values (?,?)"ret, err := db.ｅｘｅｃ(sqlStr, "沙河小王子", 19)if err != nil {fmt.Printf("ｉｎｓｅｒｔ failed, err:%v\n", err)return}theID, err := ret.LastInsertId() // 新插入数据的idif err != nil {fmt.Printf("get lastinsert ID failed, err:%v\n", err)return}fmt.Printf("ｉｎｓｅｒｔ success, the id is %d.\n", theID)}// 更新数据func updateRowDemo() {sqlStr := "ｕｐｄａｔｅ user set age=? where id = ?"ret, err := db.ｅｘｅｃ(sqlStr, 39, 6)if err != nil {fmt.Printf("ｕｐｄａｔｅ 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("ｕｐｄａｔｅ success, affected rows:%d\n", n)}// 删除数据func deleteRowDemo() {sqlStr := "ｄｅｌｅｔｅ from user where id = ?"ret, err := db.ｅｘｅｃ(sqlStr, 6)if err != nil {fmt.Printf("ｄｅｌｅｔｅ 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("ｄｅｌｅｔｅ success, affected rows:%d\n", n)}

NamedExec

DB.NamedExec方法用来绑定SQL语句与结构体或map中的同名字段。

func insertUserDemo()(err error){sqlStr := "ｉｎｓｅｒｔ INTO user (name,age) VALUES (:name,:age)"_, err = db.NamedExec(sqlStr,map[string]interface{}{"name": "七米","age": 28,})return}

NamedQuery

与DB.NamedExec同理，这里是支持查询。

func namedQuery(){sqlStr := "ｓｅｌｅｃｔ * FROM user WHERE name=:name"// 使用map做命名查询rows, err := db.NamedQuery(sqlStr, map[string]interface{}{"name": "七米"})if err != nil {fmt.Printf("db.NamedQuery failed, err:%v\n", err)return}defer rows.Close()for rows.Next(){var u usererr := rows.StructScan(&u)if err != nil {fmt.Printf("scan failed, err:%v\n", err)continue}fmt.Printf("user:%#v\n", u)}u := user{Name: "七米",}// 使用结构体命名查询，根据结构体字段的 db tag进行映射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 usererr := rows.StructScan(&u)if err != nil {fmt.Printf("scan failed, err:%v\n", err)continue}fmt.Printf("user:%#v\n", u)}}

事务操作

对于事务操作，我们可以使用sqlx中提供的db.Beginx()和tx.ｅｘｅｃ()方法。示例代码如下：

func transactionDemo2()(err error) {tx, err := db.Beginx() // 开启事务if err != nil {fmt.Printf("begin trans failed, err:%v\n", err)return err}defer func() {if p := recover(); p != nil {tx.Rollback()panic(p) // re-throw panic after Rollback} else if err != nil {fmt.Println("rollback")tx.Rollback() // err is non-nil; don't change it} else {err = tx.Commit() // err is nil; if Commit returns error ｕｐｄａｔｅ errfmt.Println("commit")}}()sqlStr1 := "ｕｐｄａｔｅ user set age=20 where id=?"rs, err := tx.ｅｘｅｃ(sqlStr1, 1)if err!= nil{return err}n, err := rs.RowsAffected()if err != nil {return err}if n != 1 {return errors.New("ｅｘｅｃ sqlStr1 failed")}sqlStr2 := "ｕｐｄａｔｅ user set age=50 where i=?"rs, err = tx.ｅｘｅｃ(sqlStr2, 5)if err!=nil{return err}n, err = rs.RowsAffected()if err != nil {return err}if n != 1 {return errors.New("ｅｘｅｃ sqlStr1 failed")}return err}

sqlx.In

sqlx.In是sqlx提供的一个非常方便的函数。

sqlx.In的批量插入示例

表结构

为了方便演示插入数据操作，这里创建一个user表，表结构如下：

CREATE TABLE `user` (    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,    `name` VARCHAR(20) DEFAULT '',    `age` INT(11) DEFAULT '0',    PRIMARY KEY(`id`))ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

结构体

定义一个user结构体，字段通过tag与数据库中user表的列一致。

type User struct {Name string `db:"name"`Age  int    `db:"age"`}

bindvars（绑定变量）

查询占位符?在内部称为bindvars（查询占位符）,它非常重要。你应该始终使用它们向数据库发送值，因为它们可以防止SQL注入攻击。database/sql不尝试对查询文本进行任何验证；它与编码的参数一起按原样发送到服务器。除非驱动程序实现一个特殊的接口，否则在执行之前，查询是在服务器上准备的。因此bindvars是特定于数据库的:

• MySQL中使用?
• PostgreSQL使用枚举的$1、$2等bindvar语法
• SQLite中?和$1的语法都支持
• Oracle中使用:name的语法

bindvars的一个常见误解是，它们用来在sql语句中插入值。它们其实仅用于参数化，不允许更改SQL语句的结构。例如，使用bindvars尝试参数化列或表名将不起作用：

// ？不能用来插入表名（做SQL语句中表名的占位符）db.Query("ｓｅｌｅｃｔ * FROM ?", "mytable")// ？也不能用来插入列名（做SQL语句中列名的占位符）db.Query("ｓｅｌｅｃｔ ?, ? FROM people", "name", "location")

自己拼接语句实现批量插入

比较笨，但是很好理解。就是有多少个User就拼接多少个(?, ?)。

// BatchInsertUsers 自行构造批量插入的语句func BatchInsertUsers(users []*User) error {// 存放 (?, ?) 的slicevalueStrings := make([]string, 0, len(users))// 存放values的slicevalueArgs := make([]interface{}, 0, len(users) * 2)// 遍历users准备相关数据for _, u := range users {// 此处占位符要与插入值的个数对应valueStrings = append(valueStrings, "(?, ?)")valueArgs = append(valueArgs, u.Name)valueArgs = append(valueArgs, u.Age)}// 自行拼接要执行的具体语句stmt := fmt.Sprintf("ｉｎｓｅｒｔ INTO user (name, age) VALUES %s",strings.Join(valueStrings, ","))_, err := DB.ｅｘｅｃ(stmt, valueArgs...)return err}

使用sqlx.In实现批量插入

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

func (u User) Value() (driver.Value, error) {return []interface{}{u.Name, u.Age}, nil}

使用sqlx.In实现批量插入代码如下：

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

使用NamedExec实现批量插入

注意 ：该功能需1.3.1版本以上，并且1.3.1版本目前还有点问题，sql语句最后不能有空格和;，详见issues/690。

阅读剩余部分

相关阅读 >>

Sqlite教程（三）：数据表和视图简介

详解android数据存储之sqlcipher数据库加密

django基础之数据库操作方法(详解)

为Sqlite3提供一个ansi到utf8的互转函数

支持多类型数据库的c#数据库模型示例

Sqlite3 api 编程手册

python3 tkinkter + Sqlite实现登录和注册界面

Sqlitestudio优雅调试android手机数据库Sqlite（推荐）

asp.net与Sqlite数据库通过js和ashx交互（连接和操作）

android中Sqlite数据库知识点总结

更多相关阅读请进入《Sqlite》频道 >>

书籍

数据库系统概念 第6版

￥41.1元   机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。