操作数据库

目前为止，我们能接收到用户提交过来的数据，且对这些数据做验证。也已经开发完成验证错误的逻辑。那么这一节我们就要开始学习如何将数据存入数据库。

操作 MySQL 数据库

在本项目中所选用的数据库为MySQL，使用GO操作MySQL等数据库，一般有两种方法：

• 一是利用 database/sql 接口，直接在代码里硬编码 sql 语句；
• 二是使用 ORM，具体一点是 GORM，以对象关系映射的方式在抽象地操作数据库。

database/sql

database/sql 包通过提供统一的编程接口，实现了对不同数据库驱动的抽象。

大致原理

1. Driver 接口定义：database/sql/driver 包中定义了一个 Driver 接口，该接口用于表示一个数据库驱动。驱动开发者需要实现该接口来提供与特定数据库的交互能力。
2. Driver 注册：驱动开发者需要在程序初始化阶段，通过调用 database/sql 包提供的 sql.Register() 方法将自己的驱动注册到 database/sql 中。这样，database/sql 就能够识别和使用该驱动。
3. 数据库连接池管理：database/sql 维护了一个数据库连接池，用于管理数据库连接。当通过 sql.Open() 打开一个数据库连接时，database/sql 会在合适的时机调用注册的驱动来创建一个具体的连接，并将其添加到连接池中。连接池会负责连接的复用、管理和维护工作，并且这是并发安全的。
4. 统一的编程接口：database/sql 定义了一组统一的编程接口供用户使用，如 Prepare()、Exec() 和 Query() 等方法，用于准备 SQL 语句、执行 SQL 语句和执行查询等操作。这些方法会接收参数并调用底层驱动的相应方法来执行实际的数据库操作。
5. 接口方法的实现：驱动开发者需要实现 database/sql/driver 中定义的一些接口方法，以此来支持上层 database/sql 包提供的 Prepare()、Exec() 和 Query() 等方法，以提供底层数据库的具体实现。当 database/sql 调用这些方法时，实际上会调用注册的驱动的相应方法来执行具体的数据库操作。

通过以上的机制，database/sql 包能够实现对不同数据库驱动的统一封装和调用。用户可以使用相同的编程接口来进行数据库操作，无需关心底层驱动的具体细节。这种设计使得代码更具可移植性和灵活性，方便切换和适配不同的数据库。

特点

database/sql 具有如下特点：

• 统一的编程接口：database/sql 库提供了一组统一的接口，使得开发人员可以使用相同的方式操作不同的数据库，而不需要学习特定数据库的 API。
• 驱动支持：通过导入第三方数据库驱动程序，database/sql 可以与多种常见的关系型数据库系统进行交互，如 MySQL、PostgreSQL、SQLite 等。
• 预防 SQL 注入：database/sql 库通过使用预编译语句和参数化查询等技术，有效预防了 SQL 注入攻击。
• 支持事务：事务是一个优秀的 SQL 包必备功能。

连接数据库

与数据库建立连接的代码非常简单，只需调用 sql.Open() 函数即可。它接收两个参数：

• 驱动名称

这里驱动名称为 mysql，database/sql 之所以能够识别这个驱动名称，是因为在匿名导入 github.com/go-sql-driver/mysql 时，这个库内部调用了 sql.Register 将其注册给了 database/sql。

func init() {
  sql.Register("mysql", &MySQLDriver{})
}

在 Go 语言中，一个包的 init 方法会在导入时会被自动调用，这里完成了驱动程序的注册。这样在调用 sql.Open() 时才能找到 mysql 驱动。

• DSN

第二个参数 DSN 全称 Data Source Name，数据库的源名称，其格式如下：

// [用户名[:密码]@][协议(数据库服务器地址)]]/数据库名称?参数列表
[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]

为了更加直观，我们可以使用 mysql.Config 来创建连接信息：

// 设置数据库连接信息
config := mysql.Config{
    User:                 "homestead",
    Passwd:               "secret",
    Addr:                 "127.0.0.1:3306",
    Net:                  "tcp",
    DBName:               "goblog",
    AllowNativePasswords: true,
}

sql.Open() 调用后将返回一个 *sql.DB 类型，可以用来操作数据库。

另外，我们调用 defer db.Close() 来释放数据库连接。其实这一步操作也可以不做，database/sql 底层连接池会帮我们处理。一旦关闭了连接，就不可以再继续使用这个 db 对象了。

*sql.DB 的设计是用来作为长连接使用的，所以不需要频繁的进行 Open 和 Close 操作。如果我们需要连接多个数据库，则可以为每个不同的数据库创建一个 *sql.DB 对象，保持这些对象为 Open 状态，不必频繁使用 Close 来切换连接。

值得注意的是，其实 sql.Open() 并没有真正建立数据库连接，它只是准备好了一切，以备后续使用，连接将在第一次被使用时延迟建立。

这样的设计虽然合理，可也有些违反直觉，sql.Open() 甚至不会校验 DSN 参数的合法性。不过我们可以使用 db.Ping() 方法来主动检查连接是否能被正确建立。

if err := db.Ping(); err != nil {
    log.Fatal(err)
}

连接池设置

使用 sql.Open() 并不会建立一个唯一的数据库连接，事实上，database/sql 会维护一个连接池。

我们可以通过如下方法，控制连接池的一些参数：

// 设置最大连接数
db.SetMaxOpenConns(100)

// 设置最大空闲连接数
db.SetMaxIdleConns(25)

// 设置每个链接的过期时间
db.SetConnMaxLifetime(5 * time.Minute)

SetMaxOpenConns 最大连接数

设置连接池最大打开数据库连接数，<= 0 表示无限制，默认为 0。

• 应该设置多大？
  • 实验表明，在高并发的情况下，将值设为大于 10，可以获得比设置为 1 接近六倍的性能提升。而设置为 10 跟设置为 0（也就是无限制），在高并发的情况下，性能差距不明显。
• 是否越大越好？
  • 需要考虑的是不要超出数据库系统设置的最大连接数。另外，还需要注意这个值是整个系统的，如有其他应用程序也在共享这个数据库，这个可以合理地控制小一点。

SetMaxIdleConns 空闲连接数

设置连接池最大空闲数据库连接数，<= 0 表示不设置空闲连接数，默认为 2。

• 应该设置多大？
  • 实验表明，在高并发的情况下，将值设为大于 0，可以获得比设置为 0 超过 20 倍的性能提升。
  • 这是因为设置为 0 的情况下，每一个 SQL 连接执行任务以后就销毁掉了，执行新任务时又需要重新建立连接。很明显，重新建立连接是很消耗资源的一个动作。
  • 设置空闲连接数，当有新任务进来时，直接使用这些随时待命的连接传输数据，以此达到节约资源，提高执行效率的目的。
• 是不是数值越大越好？
  • 首先此值不能大于 SetMaxOpenConns 的值，大于的情况下 mysql 驱动会自动将其纠正。
  • 其次需要考虑的是，长时间打开大量的数据库连接需要占用系统的内存和 CPU 资源。
  • 还有一个情况是 MySQL 会有一个 wait_timeout 的设置，连接超过这个时间就会被自动关闭，默认情况下是 8 个小时。当 MySQL 关闭连接时，sql.DB 请求到的就是一个坏的连接，虽然 sql 包里已经做了处理，当请求到坏连接时会自动重连。但是在这种情况下，单次请求相当于建立了两次连接，消耗比设置为 0 还大，得不偿失。
  • 所以回答上面的问题，不是越大越好，应根据实际情况选择合理的值。

SetConnMaxLifetime 过期时间

设置连接池里每一个连接的过期时间，过期会自动关闭。理论上来讲，在并发的情况下，此值越小，连接就会越快被关闭，也意味着更多的连接会被创建。

• 应该设置多大？
  • 设置的值不应该超过 MySQL 的 wait_timeout 设置项（默认情况下是 8 个小时）。
  • 此值也不宜设置过短，关闭和创建都是极耗系统资源的操作。
  • 设置此值时，需要特别注意 SetMaxIdleConns 空闲连接数的设置。假如设置了 100 个空闲连接，过期时间设置了 1 分钟，在没有任何应用的 SQL 操作情况下，数据库连接每 1.6 秒就销毁和新建一遍。
  • 这里的推荐，比较保守的做法是设置五分钟

创建

`*sq

l.DB 提供了 Exec 方法来执行一条 SQL 命令，可以用来创建、更新、删除表数据等。

语法如下：

func (db *DB) Exec(query string, args ...interface{}) (Result, error)

示例：

db.Exec(`INSERT INTO user(name, email, age, birthday, salary) VALUES(?, ?, ?, ?, ?)`, user.Name, user.Email, user.Age, user.Birthday, user.Salary)

其中 ? 作为参数占位符，不同数据库驱动程序的占位符可能不同，可以参考数据库驱动的文档。

我们将这 5 个参数顺序传递给 db.Exec 方法，即可完成用户的创建。

db.Exec 方法调用后将返回 sql.Result 保存结果以及一个 error 来标记错误。

sql.Result 是一个接口，它包含两个方法：

• LastInsertId() (int64, error)：返回新插入的用户 ID。只用在 INSERT 语句且数据表有自增 ID 时才有返回自增 ID 值，否则返回 0。
• RowsAffected() (int64, error)：返回当前操作受影响的行数，我们以此来判断 SQL 语句是否执行成功。

接口具体实现有数据库驱动程序来完成。

此外，database/sql 还提供了预处理方法 *sql.DB.Prepare 创建一个准备好的 SQL 语句，在循环中使用预处理，则可以减少与数据库的交互次数。

比如我们需要创建两个用户，则可以先使用 db.Prepare 创建一个 *sql.Stmt 对象，然后多次调用 *sql.Stmt.Exec 方法来插入数据。

db.Prepare 是预先将一个数据库连接和一个条 SQL 语句绑定并返回 *sql.Stmt 结构体，它代表了这个绑定后的连接对象，是并发安全的。

通过使用预处理，可以避免在循环中执行多次完整的 SQL 语句，从而显著减少了数据库交互次数，这可以提高应用程序的性能和效率。

使用预处理，会在 db.Prepare 时从连接池获取一个连接，之后循环执行 stmt.Exec，最终释放连接。

如果使用 db.Exec，则每次循环时都需要：获取连接-执行 SQL-释放连接，这几个步骤，大大增加了与数据库的交互次数。

不要忘记调用 stmt.Close() 关闭连接，这个方法是密等的，可以多次调用。

查询

在创建数据库并插入相应值后，我们就可以进行查询操作了。

因为 Exec 方法只会执行 SQL，不会返回结果，所以不适用于查询数据。*sql.DB 提供了 Query 方法执行查询操作：

func (db *DB) Query(query string, args ...interface{}) (*Rows, error)

如下获取所有文章的例子：：

rows, err := db.Query("SELECT * from articles")

Query() 方法返回一个 sql.Rows 结构体，代表一个查询结果集。

Query 和 Exec 都可以执行 SQL 语句，那他们的区别是什么呢？

Exec 只会返回最后插入 ID 和影响行数，而 Query 会返回数据表里的内容（结果集）。

或者可以这么记：

Query 中文译为 查询，而 Exec 译为 执行。想查询数据，使用 Query。想执行命令，使用 Exec。

• rows.Next() 方法用来判断是否还有下一条结果，可以用于 for 循环，如果存在下一条结果，rows.Next() 将返回 true。
• rows.Scan() 方法可以将结果扫描到传递进来的指针对象。rows.Scan() 会将一行记录分别填入指定的变量中，并且会自动根据目标变量的类型处理类型转换的问题，比如数据库中是 varchar 类型，会映射成 Go 中的 string，但如果与之对应的目标变量是 int，那么转换失败就会返回 error。

如果是读取一行数据，可以使用 QueryRow()，语法定义如下：

func (db *DB) QueryRow(query string, args ...interface{}) *Row

返回的是一个 sql.Row 对象，与其相关的调用有：

func (r *Row) Scan(dest ...interface{}) error

sql.Row 没有 Close 方法，当我们调用 Scan() 时就会自动关闭 SQL 连接。所以为了防止忘记关闭而浪费资源，一般需要养成连着调用 Scan() 习惯。

更新

更新操作同创建一样可以使用 *sql.DB.Exec 方法来实现，不过这里我们将使用 *sql.DB.ExecContext 方法来实现。

ExecContext 方法与 Exec 方法在使用上没什么两样，只不过第一个参数需要接收一个 context.Context，它允许你控制和取消执行 SQL 语句的操作。使用上下文可以在需要的情况下设置超时时间、处理请求取消等操作。

三个常用的 SQL 请求方法都有其支持上下文的版本，如下：

func (db *DB) Exec(query string, args ...interface{}) (Result, error)
func (db *DB) ExecContext(ctx context.Context, query string, args ...interface{}) (Result, error)
func (db *DB) Query(query string, args ...interface{}) (*Rows, error)
func (db *DB) QueryContext(ctx context.Context, query string, args ...interface{}) (*Rows, error)
func (db *DB) QueryRow(query string, args ...interface{}) *Row
func (db *DB) QueryRowContext(ctx context.Context, query string, args ...interface{}) *Row

支持 Context 上下文的方法传参标准库 context 里的 context.Context 对象实例。

在一些特殊场景里，我们需要 SQL 请求在执行还未完成时，我们可以取消他们（cancel），或者为请求设置最长执行时间（timeout），就会用到这些方法。

另外需要知道的是，所有的请求方法底层都是用其上下文版本的方法调用，且传入默认的上下文，例如 Exec() 的源码：

func (db *DB) Exec(query string, args ...interface{}) (Result, error) {
    return db.ExecContext(context.Background(), query, args...)
}

底层调用的是 ExecContext() 方法。context.Background() 是默认的上下文，这是一个空的 context ，我们无法对其进行取消、赋值、设置 deadline 等操作。

删除

同更新的语句是一样的，只需要把语句稍作更改即可。

事务处理

如果没有开启事务，当其中某个语句执行错误，则前面已经执行的 SQL 语句无法回滚。对于一些要求比较严格的业务逻辑来说，如付款、转账等，应该在同一个事务中提交多条 SQL 语句，避免发生执行出错无法回滚事务的情况。

使用以下可以开启事务：

func (db *DB) Begin() (*Tx, error)
func (db *DB) BeginTx(ctx context.Context, opts *TxOptions) (*Tx, error)

Begin() 和 BeginTx() 方法返回一个 sql.Tx 结构体，他支持以上我们提到过的几种查询方法：

func (tx *Tx) Exec(query string, args ...interface{}) (Result, error)
func (tx *Tx) ExecContext(ctx context.Context, query string, args ...interface{}) (Result, error)
func (tx *Tx) Query(query string, args ...interface{}) (*Rows, error)
func (tx *Tx) QueryContext(ctx context.Context, query string, args ...interface{}) (*Rows, error)
func (tx *Tx) QueryRow(query string, args ...interface{}) *Row
func (tx *Tx) QueryRowContext(ctx context.Context, query string, args ...interface{}) *Row

// 预编译 Prepare
func (tx *Tx) Stmt(stmt *Stmt) *Stmt
func (tx *Tx) StmtContext(ctx context.Context, stmt *Stmt) *Stmt
func (tx *Tx) Prepare(query string) (*Stmt, error)
func (tx *Tx) PrepareContext(ctx context.Context, query string) (*Stmt, error)

使用这同一个 sql.Tx 对数据库进行操作，就会在同一个事务中提交。

当使用 sql.Tx 的操作方式操作数据后，需要使用 sql.Tx 的 Commit() 方法提交事务，如果出错，则可以使用 sql.Tx 中的 Rollback() 方法回滚事务，保持数据的一致性，下面是这两个方法的定义：

func (tx *Tx) Commit() error
func (tx *Tx) Rollback() error

下面是个简单的示例：

func (s Service) DoSomething() (err error) {
    // 1. 创建事务
    tx, err := s.db.Begin()
    if err != nil {
        return
    }
    // 2. 如果请求失败，就回滚所有 SQL 操作，否则提交
    //    defer 会在当前方法的最后执行
    defer func() {
        if err != nil {
            tx.Rollback()
            return err
        }
        err = tx.Commit()
    }()

    // 3. 执行各种请求
    if _, err = tx.Exec(...); err != nil {
        return err
    }
    if _, err = tx.Exec(...); err != nil {
        return err
    }
    // ...
    return nil
}

需要注意的是，所有 SQL 操作都必须使用 tx 来操作，才能支持事务，如果中间使用 db.Exec() 那这条语句是无法回滚的。

集成 GORM

什么是 ORM ？

ORM 全称是：Object Relational Mapping (对象关系映射)，其主要作用是在编程中，把面向对象的概念跟数据库中表的概念对应起来。举例来说就是，我定义一个对象（结构体），那就对应着一张表，这个对象的实例，就对应着表中的一条记录。

为什么要使用 GORM？

本项目使用 GORM 的理由：

1. 现代化，面对对象
2. 多数据库支持，为高负载做好准备
3. 提高项目安全性
4. 提升开发效率和项目的可维护性

我们都知道，在正式环境中直接使用 SQL 来查询数据库是很危险的，处理不好就有被注入式攻击的风险。而且组装 SQL 语句也容易出错和减低代码的可维护性。所以需要一个工具来管理数据库语句的组装和操作。

GORM 是目前比较成熟的 Go 语言数据库管理库，它可以很方便的把 Go 的结构体和数据库表绑定，从而简化获取数据的操作。

GORM 功能包括下面：

• 全功能 ORM
• 关联 (Has One，Has Many，Belongs To，Many To Many，多态，单表继承)
• Create，Save，Update，Delete，Find 中钩子方法
• 支持 Preload、Joins 的预加载
• 事务，嵌套事务，Save Point，Rollback To Saved Point
• Context，预编译模式，DryRun 模式
• 批量插入，FindInBatches，Find/Create with Map，使用 SQL 表达式、Context Valuer 进行 CRUD
• SQL 构建器，Upsert，数据库锁，Optimizer/Index/Comment Hint，命名参数，子查询
• 复合主键，索引，约束
• Auto Migration
• 自定义 Logger
• 灵活的可扩展插件 API：Database Resolver（多数据库，读写分离）、Prometheus…
• 每个特性都经过了测试的重重考验
• 开发者友好

以上列表熟悉下即可，后面的项目开发中使用到自然就会记住了，脱离实战记忆没有意义。

连接数据库

与database/sql操作类似，使用gorm.Open()函数即可，该函数同样有两个参数：

• 驱动名称
• DSN

*gorm.DB 对象有一个方法 DB() 可以直接获取到 database/sql 包里的 *sql.DB 对象。GORM 底层也是使用 database/sql 来管理连接池。

创建

db.Create方法可以新建一条数据，传入的是一个表结构体指针，返回值是一个gorm.DB,即我们的数据库连接。如下：

db.Create(&ormdemo.UserInfo{
    Name:   "jaylog",
    Gender: "man",
    Hobby:  "pingpong",
})

查询

GORM 提供了 First、Take、Last 方法，以便从数据库中检索单个对象。当查询数据库时它添加了 LIMIT 1 条件，且没有找到记录时，它会返回 ErrRecordNotFound 错误。

// 获取第一条记录（主键升序）
db.First(&user)
// SELECT * FROM users ORDER BY id LIMIT 1;

// 获取一条记录，没有指定排序字段
db.Take(&user)
// SELECT * FROM users LIMIT 1;

// 获取最后一条记录（主键降序）
db.Last(&user)
// SELECT * FROM users ORDER BY id DESC LIMIT 1;

如果你想避免ErrRecordNotFound错误，你可以使用Find，比如db.Limit(1).Find(&user)，Find方法可以接受struct和slice的数据。

对单个对象使用Find而不带limit，db.Find(&user)将会查询整个表并且只返回第一个对象，这是性能不高并且不确定的。

更新

Save 会保存所有的字段，即使字段是零值

db.First(&user)

user.Name = "jinzhu 2"
user.Age = 100
db.Save(&user)

当使用 Update 更新单列时，需要有一些条件，否则将会引起ErrMissingWhereClause 错误，当使用 Model 方法，并且它有主键值时，主键将会被用于构建条件，例如：

// 根据条件更新
db.Model(&User{}).Where("active = ?", true).Update("name", "hello")
// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE active=true;

// User 的 ID 是 `111`
db.Model(&user).Update("name", "hello")
// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111;

// 根据条件和 model 的值进行更新
db.Model(&user).Where("active = ?", true).Update("name", "hello")
// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111 AND active=true;

Updates 方法支持 struct 和 map[string]interface{} 参数。当使用 struct 更新时，默认情况下GORM 只会更新非零值的字段

// 根据 `struct` 更新属性，只会更新非零值的字段
db.Model(&user).Updates(User{Name: "hello", Age: 18, Active: false})
// UPDATE users SET name='hello', age=18, updated_at = '2013-11-17 21:34:10' WHERE id = 111;

// 根据 `map` 更新属性
db.Model(&user).Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
// UPDATE users SET name='hello', age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;

删除

删除一条记录时，删除对象需要指定主键，否则会触发批量删除：

// Email 的 ID 是 `10`
db.Delete(&email)
// DELETE from emails where id = 10;

// 带额外条件的删除
db.Where("name = ?", "jinzhu").Delete(&email)
// DELETE from emails where id = 10 AND name = "jinzhu";

GORM 允许通过主键(可以是复合主键)和内联条件来删除对象：

db.Delete(&User{}, 10)
// DELETE FROM users WHERE id = 10;

db.Delete(&User{}, "10")
// DELETE FROM users WHERE id = 10;

db.Delete(&users, []int{1,2,3})
// DELETE FROM users WHERE id IN (1,2,3);

总结

至此，我们已经完成地了解了如何使用两种不同方法来操作数据库。数据库的操作是本项目的重中之重，好好理解这一节的内容对面是有着不小的帮助，因为笔者的第一次面试就是挂在关于数据库的问题上的。

参考文章

• GORM
• database/sql