sqlx库使用指南

sqlx库使用指南
在项目中我们通常可能会使用database/sql连接MySQL数据库。本文借助使用sqlx实现批量插入数据的例子，介绍了sqlx中可能被你忽视了的sqlx.In和DB.NamedExec方法。
sqlx介绍
在项目中我们通常可能会使用database/sql连接MySQL数据库。sqlx可以认为是Go语言内置database/sql的超集，它在优秀的内置database/sql基础上提供了一组扩展。这些扩展中除了大家常用来查询的Get(dest interface{}, ...) error和Select(dest interface{}, ...) error外还有很多其他强大的功能。

代码：

创建你数据表

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;

代码的增删改查；

package mainimport ("fmt"_ "github.com/go-sql-driver/mysql""github.com/jmoiron/sqlx"
)type user struct {Id   intAge  intName string
}var db *sqlx.DBfunc initDB() (err error) {dsn := "root:root123456@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 insertRowDemo() {sqlStr := "insert into user(name, age) values (?,?)"ret, err := db.Exec(sqlStr, "沙河小王子", 19)if err != nil {fmt.Printf("insert 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("insert success, the id is %d.\n", theID)
}// 更新数据
func updateRowDemo() {sqlStr := "update user set age=? where id = ?"ret, err := db.Exec(sqlStr, 39, 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 = ?"ret, err := db.Exec(sqlStr, 1)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)
}// 查询单条数据示例
func queryRowDemo() {sqlStr := "select id, name, age from user where id=?"var u usererr := db.Get(&u, sqlStr, 2)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 := "select id, name, age from user where id > ?"var users []usererr := db.Select(&users, sqlStr, 0)if err != nil {fmt.Printf("query failed, err:%v\n", err)return}fmt.Printf("users:%#v\n", users)
}
func main() {initDB()insertRowDemo()queryRowDemo()deleteRowDemo()fmt.Println("==============")queryRowDemo()fmt.Println("==============")queryMultiRowDemo()
}