go操作数据库 database/sql、sqlx使用

西瓜爸爸

1.database/sql包

golang官方标准库提供了sql包，它提供了有关SQL（或类SQL）数据库的通用操作接口，并不提供数据库驱动。sql包必须与数据库驱动程序一起使用。
我们常用的数据库基本是都有数据库驱动的第三方实现。例如MySQL驱动。
2.使用mysql驱动

地址：https://github.com/go-sql-driver/mysql
2.1 初始化连接

package dao

import (
        "database/sql"
        "fmt"
        _ "github.com/go-sql-driver/mysql"
        "gomysql/config"
        "log"
        "time"
)

var DB *sql.DB

func InitDB() {
        log.Println("初始化数据库")
        dbConfig := config.C.DBConfig
        dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s", dbConfig.Username, dbConfig.Password, dbConfig.Host, dbConfig.Port, dbConfig.DbName)
        //注意，这里不会校验密码是否正确
        db, err := sql.Open("mysql", dsn)
        if err != nil {
                log.Fatalf("connect database fail,err:%v\n", err)
        }
        db.SetConnMaxLifetime(time.Minute * 3)
        db.SetMaxOpenConns(10)
        db.SetMaxIdleConns(10)
        if err := db.Ping(); err != nil {
                log.Fatalf("connect database fail,err:%v\n", err)
        }
        DB = db
}

• db.SetMaxOpenConns：设置与数据库建立连接的最大连接数，默认为0（不限制）。如果n的值小于最大空闲连接数的值，则最大空闲连接数将设置为n。
  

func (db *DB) SetMaxOpenConns(n int)

• db.SetMaxIdleConns：设置连接池最大空闲连接数，默认为2.如果n<=0，则不保留空闲连接。
  func (db *DB) SetMaxIdleConns(n int)
  

-SetConnMaxLifetime：设置连接的最大生存时间。
func (db *DB) SetConnMaxLifetime(d time.Duration)
2.2 CRUD

查询

单行查询
db.QueryRow()执行一次查询，并期望返回一行记录。QueryRow总是返回非nil的值，知道返回值的Scan方法被调用时，才会返回被延迟的错误，如未找到结果。
func (db *DB) QueryRow(query string, args ...any) *Row
具体示例代码：
func GetUser(id int) User {
sql := "select id, username,password,create_time from users where id=?"
var user User
err := dao.DB.QueryRow(sql, id).Scan(&user.Id, &user.Username, &user.Password, &user.CreateTime)
if err != nil {
  log.Printf("scan failed, err:%v", err)
}
return user
}
多行查询
多行查询db.Query()执行一次查询，返回多行结果（即Rows），一般用于执行select命令。参数args表示query中的占位参数。
func (db *DB) Query(query string, args ...any) (*Rows, error)
具体示例代码：
func GetAll() []User {
        sql := "select username, password, create_time from users"
        var users []User
        rows, err := dao.DB.Query(sql)
        if err != nil {
                log.Printf("query failed, err:%v", err)
                return users
        }
        //关闭rows释放持有的数据库连接
        defer rows.Close()
        //循环读取结果集中的数据
        for rows.Next() {
                var user User
                //fmt.Println(rows.Columns())
                err := rows.Scan(&user.Username, &user.Password, &user.CreateTime)
                if err != nil {
                        log.Printf("scan failed,err:%v", err)
                }
                users = append(users, user)
        }
        return users
}插入数据

db.Exec()用来执行插入、更新、删除操作。
func (db *DB) Exec(query string, args ...any) (Result, error)
//返回结果接口
type Result interface {
        LastInsertId() (int64, error)
        RowsAffected() (int64, error)
}参数args表示query中占位符的参数。
func createUser(user *User) error {
        sql := "insert into users(username, password, create_time) values(?, ?, ?)"
        result, err := dao.DB.Exec(sql, user.Username, user.Password, user.CreateTime)
        if err != nil {
                log.Printf("insert failed, err:%v", err)
                return err
        }
        lastId, err := result.LastInsertId()
        if err != nil {
                log.Printf("get last insert id failed, err:%v", err)
                return err
        }
        user.Id = lastId
        return nil
}更新数据

func UpdatePassword(id int, password string) error {
        sql := "update users set password=? where id=?"
        result, err := dao.DB.Exec(sql, password, id)
        if err != nil {
                log.Printf("exec failed, err:%v", err)
                return err
        }
        _, err = result.RowsAffected()
        return err
}删除数据

func DeleteById(userId int) error {
        sql := "delete from users where id=?"
        result, err := dao.DB.Exec(sql, userId)
        if err != nil {
                log.Printf("exec failed, err:%v", err)
                return err
        }
        affected, err := result.RowsAffected()
        if err != nil {
                log.Printf("get rows affected failed, err:%v", err)
                return err
        }
        fmt.Println(affected)
        return nil
}2.3 预处理

Mysql在4.1版本开始就已经支持了预处理。MySQL 官方将 prepare、execute、deallocate 统称为 PREPARE STATEMENT。翻译也就习惯的称其为预处理语句。
预处理优势：

• 一次编译、多次运行，省去了解析优化等过程；
  
• 使用预处理语句，无须在应用程序中处理转义，也大大减少了SQL注入和攻击的风险。
  

语法：
# 定义预处理语句
PREPARE stmt_name FROM preparable_stmt;
# 执行预处理语句
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
# 删除(释放)定义
{DEALLOCATE | DROP} PREPARE stmt_name;具体示例：
prepare pre_user from "select * from users where id=?"
set @id=7
set @id=9
EXECUTE pre_user USING @idgo 预处理：
func GetUserById(id int) User {
        stat, err := dao.DB.Prepare("select id, username,password,create_time from users where id=?")
        if err != nil {
                log.Printf("prepare failed, err:%v", err)
        }
        var user User
        err = stat.QueryRow(id).Scan(&user.Id, &user.Username, &user.Password, &user.CreateTime)
        if err != nil {
                log.Printf("scan failed, err:%v", err)
        }
        fmt.Println(user)
        return user
}2.4 SQL注入问题

任何时候都不应该自己拼接SQL语句！
func sqlInjectDemo(name string) {
        sqlStr := fmt.Sprintf("select id, username from users where username='%s'", name)
        fmt.Printf("SQL:%s\n", sqlStr)
}

func TestInject(t *testing.T) {
        sqlInjectDemo("xxx' or 1=1#")
        //SQL1:select id, username from users where username='xxx' or 1=1#'

        sqlInjectDemo("xxx' union select * from user #")
        //SQL2:select id, username from users where username='xxx' union select * from user #'
       
        sqlInjectDemo("xxx' and (select count(*) from user) <10 #")
        //SQL3:select id, username from users where username='xxx' and (select count(*) from user) <10 #'
}2.4 事务

Go语言中使用以下三个方法实现MySQL中的事务操作。
//开启事务
func (db *DB) Begin() (*Tx, error)
//提交事务
func (tx *Tx) Commit() error
//回滚事务
func (tx *Tx) Rollback() error相关阅读：https://www.liwenzhou.com/posts/Go/mysql/
<hr/>3. sqlx

地址：https://github.com/jmoiron/sqlx
3.1 概念

sqlx是在内置database/sql基础上提供了一组扩展，它是database/sql标准库的超集。对于sql.DB、sql.TX、sql.Stmt等的sqlx版本都保持底层接口不变。
从下面结构体可以看出，sqlx.DB扩展了sql.DB。
type DB struct {
        *sql.DB
        driverName string
        unsafe     bool
        Mapper     *reflectx.Mapper
}3.1.1 使用tag映射数据库字段

定义一个user结构体，字段通过tag与数据库中user表的列一致。
type User struct {
        Name string `db:"name"`
        Age  int    `db:"age"`
}3.1.2 bindvars（绑定变量）

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

• MySQL中使用?
  
• PostgreSQL使用枚举的$1、$2等bindvar语法
  
• SQLite中?和$1的语法都支持
  
• Oracle中使用:name的语法
  注意：bindvars其实仅用于参数化，不允许更改SQL语句的结构。例如，使用bindvars尝试参数化列或表名将不起作用：
  

// ？不能用来插入表名（做SQL语句中表名的占位符）
db.Query("SELECT * FROM ?", "mytable")

// ？也不能用来插入列名（做SQL语句中列名的占位符）
db.Query("SELECT ?, ? FROM people", "name", "location")3.2 句柄类型

sqlx与database/sql具有类似的句柄类型。有4个主要的句柄类型：

• sqlx.DB - 类似sql.DB，表示数据库。
  
• sqlx.Tx - 类似sql.Tx，表示事务。
  
• sqlx.Stmt - 类似sql.Stmt，表示预处理。
  
• sqlx.NamedStmt - 表示支持命名参数的预处理
  

此外还有2个游标类型

• sqlx.Rows - 类似sql.Rows, 它是从Queryx的返回的游标
  
• sqlx.Row - 类似sql.Row, 它来自 QueryRowx的返回结果
  

3.3 连接数据库

安装
$ go get github.com/jmoiron/sqlx
$ go get github.com/go-sql-driver/mysql初始化数据库连接
//helloworld/utlis/db/db.go
package db

import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
"helloworld/config"
"log"
)

var DB *sqlx.DB

func init() {
var err error
dbConfig := config.C.Db
// 参考 https://github.com/go-sql-driver/mysql#dsn-data-source-name 获取详情
dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local", dbConfig.Username, dbConfig.Password, dbConfig.Host, dbConfig.Port, dbConfig.DbName)
//连接数据库，或使用MustConnect，发生错误时会报panic
DB, err = sqlx.Connect("mysql", dsn)
if err != nil {
  log.Fatalf("connect database failed, err:%v\n", err)
}
//设置连接池最大连接数
DB.SetMaxOpenConns(20)
//设置连接池最大空闲连接数
DB.SetMaxIdleConns(10)
}
3.4 CRUD操作

sqlx的句柄类型实现了数据库查询相同的操作语法

• Exec(...) (sql.Result, error) 和database/sql相同
  
• Query(...) (*sql.Rows, error) 和database/sql相同
  
• QueryRow(...) *sql.Row 和database/sql相同
  

对内置语法的扩展

• MustExec() sql.Result -- 对Exec的扩展, 当发生错误时返回panic
  
• Queryx(...) (*sqlx.Rows, error) - 对Query的扩展
  
• QueryRowx(...) *sqlx.Row -- 对QueryRow的扩展
  

还有下列新的语法
Get(dest interface{}, ...) error 获取一条记录，内部调用了QueryRowx
Select(dest interface{}, ...) error 获取多条记录，内部调用了Queryx
我们先来看看什么是scannable：如果select结果只有一列，那么dest被认为是可scannable的。
Get和Select函数在scannable类型上使用rows.Scan获取数据，在非scannable类型上使用rows.StructScan。Get用来获取单个结果然后Scan，Select用来获取结果切片。
p := Place{}
pp := []Place{}

// this will pull the first place directly into p
err = db.Get(&p, "SELECT * FROM place LIMIT 1")

// this will pull places with telcode > 50 into the slice pp
err = db.Select(&pp, "SELECT * FROM place WHERE telcode > ?", 50)

// they work with regular types as well
var id int
err = db.Get(&id, "SELECT count(*) FROM place")

// fetch at most 10 place names
var names []string
err = db.Select(&names, "SELECT name FROM place LIMIT 10")Get和Select在执行完成后会自动关闭打开的Rows，并且出现任何错误都将返回。
Select能够节省大量的开发时间，但是要注意：Select和Queryx是有很大不通的，因为Select会一次性将所有的结果集加载到内存。如果您的查询没有限制大小，那么最好还是使用Queryx/StructScan迭代。
3.4.1 查询

• 单行查询
  

func TestQueryRow(t *testing.T) {
        var user User
        sql := "select id, name, age from users where id = ?"
        err := db.DB.Get(&user, sql, 37)
        if err != nil {
                t.Fatalf("get failed, err:%v\n", err)
        }
        fmt.Printf("user:%v", user)
}

• 多行查询
  

func TestMultiRow(t *testing.T) {
        var levels = []int{35, 36, 37, 38}
        //这里使用了sqlx.In帮助函数，生成查询语句和参数
        query, args, _ := sqlx.In("SELECT id,name,age,create_time FROM users WHERE id IN (?);", levels)
        fmt.Println(query) //SELECT * FROM users WHERE id IN (?, ?, ?, ?);
        fmt.Println(args)  //[4 6 7 8]
        var users []User
        err := db.DB.Select(&users, query, args...)
        if err != nil {
                t.Fatalf("select fail, err:%v\n", err)
        }
        fmt.Printf("users:%#v", users)
}3.4.2 插入

• 插入单条数据
  

func TestInsert(t *testing.T) {
        user := User{
                Name: "wangwu",
                Age:  22,
        }
        _, err := db.DB.NamedExec("INSERT INTO users (name, age) VALUES (:name, :age)", user)
        fmt.Println(err)
}

• 使用http://sqlx.In实现批量插入
  

前提是需要我们的结构体实现driver.Valuer接口。
func (u User) Value() (driver.Value, error) {
        return []interface{}{u.Name, u.Age, u.CreateTime}, nil
}示例代码：
func BatchInsertUsersWithIn(users []interface{}) error {
        if users == nil {
                return nil
        }
        //注意：(?)的数量必须和users切片数据的数量一致
        bindVars := "(?)" + strings.Repeat(",(?)", len(users)-1)
        sql := "INSERT INTO users (name, age, create_time) VALUES " + bindVars
        query, args, err := sqlx.In(
                sql,
                users..., // 如果arg实现了 driver.Valuer, sqlx.In 会通过调用 Value()来展开它
        )
        fmt.Println(query, args, err) // 查看生成的querystring
        _, err = db.DB.Exec(query, args...)
        return err
}

• 使用NamedExec实现批量插入
  

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

func TestUpdate(t *testing.T) {
        user := User{
                Id:   38,
                Name: "wangwu",
                Age:  24,
        }
        //使用？查询占位符
        //sql := "update users set name=?,age=? where id=?"
        //result, err := db.DB.Exec(sql, user.Name, user.Age, user.Id)
        //使用Named语法查询占位符 :param形式
        sql := "update users set name=:name,age=:age where id=:id"
        result, err := db.DB.NamedExec(sql, user)
        if err != nil {
                log.Printf("update fail,err:%v\n", err)
        }
        n, _ := result.RowsAffected()
        fmt.Println("affected rows:", n)
}3.4.4 删除

func TestDelete(t *testing.T) {
        sql := "delete from users where name=?"
        result, err := db.DB.Exec(sql, "wangwu")
        if err != nil {
                log.Printf("delete fail,err:%v\n", err)
        }
        n, _ := result.RowsAffected()
        fmt.Println("affected rows:", n)
}3.5 事务

除了使用database/sql包的事务外，还可以用Beginx和MustBegin()，返回sqlx.Tx，而不是sql.Tx。
sqlx.Tx有sqlx.DB具有的所有方法，而sql.Tx只能使用sql包提供的方法。
tx := db.MustBegin()
tx.MustExec(...)
err = tx.Commit()示例

func testTransaction() (err error) {
        tx := db.DB.MustBegin() //开启事务
        if err != nil {
                fmt.Printf("begin transaction fail,err:%v\n", err)
                return err
        }
        defer func() { //在defer中判断是否异常
                if p := recover(); p != nil {
                        tx.Rollback()
                        panic(p)
                } else if err != nil {
                        tx.Rollback()
                }
        }()
        sql1 := "update users set age=21 where id=?"
        result := tx.MustExec(sql1, 37)
        if affected, _ := result.RowsAffected(); affected == 0 {
                return errors.New("exec sql1 fail, sql:" + sql1)
        }
        sql2 := "update users set age=51 where id=?"
        result = tx.MustExec(sql2, 38)
        if affected, _ := result.RowsAffected(); affected == 0 {
                return errors.New("exec sql2 fail, sql:" + sql2)
        }
        return tx.Commit()
}3.6 预处理

database/sql预处理示例：
stmt, err := db.Prepare(`SELECT * FROM place WHERE telcode=?`)
row = stmt.QueryRow(65)

tx, err := db.Begin()
txStmt, err := tx.Prepare(`SELECT * FROM place WHERE telcode=?`)
row = txStmt.QueryRow(852)sqlx预处理示例：
stmt, err := db.Preparex(`SELECT * FROM place WHERE telcode=?`)
var p Place
err = stmt.Get(&p, 852)3.7 查询Helpers

3.7.1 http://sqlx.In查询

因为database/sql不检查传入的查询参数，而是直接传递给驱动程序，实际上下面例子中传入一个切片是不能工作的。
var levels = []int{4, 6, 7}
rows, err := db.Query("SELECT * FROM users WHERE level IN (?);", levels)http://sqlx.In很方便的解决了这个问题
var levels = []int{4, 6, 7}
query, args, err := sqlx.In("SELECT * FROM users WHERE level IN (?);", levels)
rows, err := db.Query(query, args...)3.7.2 Named 查询

sqlx支持使用命名的占位符参数来代替 ? 占位符参数，任何命名的占位符参数都将替换为arg中的字段。Struct字段的命名约定遵循StructScan，使用NameMapper和db struct标记。
有下列相关Named查询方法：
func (db *DB) NamedExec(query string, arg interface{}) (sql.Result, error)
func (db *DB) NamedQuery(query string, arg interface{}) (*Rows, error)
func (db *DB) PrepareNamed(query string) (*NamedStmt, error)NamedExec例子：
result, err = db.NamedExec("INSERT INTO user (name,age) VALUES(:country,:city)",
  map[string]any{
   "country": "China",
   "city": "Beijing",
  })
NamedQuery例子：
// 使用结构体做命名查询
p := Place{Country: "South Africa"}
rows, err := db.NamedQuery(`SELECT * FROM place WHERE country=:country`, p)

//使用map做命名查询
arg := map[string]interface{}{
    "published": true,
    "authors": []{8, 19, 32, 44},
}
query, args, err := sqlx.Named("SELECT * FROM articles WHERE published=:published AND author_id IN (:authors)", arg)
query, args, err := sqlx.In(query, args...)
query = db.Rebind(query)
db.Query(query, args...)4. 使用gorm

除此之外还可以使用gorm 框架来操作数据库，可以直接查看GORM 官方中文文档。