7.5 Go 语言中操作 SQLite

简介

SQLite 是一个进程内的、零配置、无服务器的嵌入式 SQL 数据库，整个数据库仅一个文件，非常适合桌面应用、移动端、CLI 工具以及中小型项目。

在 Go 中，我们通过标准库 database/sql 搭配 SQLite 驱动即可完成连接、建表、增删改查、事务等操作。

一、安装驱动

最常用的驱动是 github.com/mattn/go-sqlite3，它基于 C 语言的 SQLite 库实现：

go get github.com/mattn/go-sqlite3

注意：go-sqlite3 依赖 CGO，编译时需要本机具备 C 编译器。

二、连接数据库

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/mattn/go-sqlite3"
)

func main() {
    db, err := sql.Open("sqlite3", "./test.db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    if err := db.Ping(); err != nil {
        log.Fatal(err)
    }
    fmt.Println("成功连接到 SQLite 数据库")
}

几个要点：

• sql.Open 传入驱动名 sqlite3 和数据库文件路径，文件不存在会自动创建。
• 导入驱动使用下划线 _，只触发驱动注册，不直接使用。
• sql.Open 不会真正建立连接，用 db.Ping() 验证可用性。
• defer db.Close() 保证连接在程序退出时释放。

三、建表

用 db.Exec 执行 DDL 语句即可：

func createTable(db *sql.DB) error {
    stmt := `
    CREATE TABLE IF NOT EXISTS users (
        id   INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age  INTEGER
    );`
    _, err := db.Exec(stmt)
    return err
}

四、增删改查

下面的示例统一以函数形式组织，入参都是 *sql.DB，调用者在 main 里复用同一个连接即可，避免重复写开库代码。

1. 插入数据（推荐预处理）

func insertUser(db *sql.DB, name string, age int) (int64, error) {
    res, err := db.Exec("INSERT INTO users(name, age) VALUES(?, ?)", name, age)
    if err != nil {
        return 0, err
    }
    return res.LastInsertId()
}

使用 ? 占位符可以防止 SQL 注入，比字符串拼接安全很多。

2. 查询单条记录

func getUser(db *sql.DB, id int) (name string, age int, err error) {
    err = db.QueryRow("SELECT name, age FROM users WHERE id = ?", id).
        Scan(&name, &age)
    return
}

QueryRow 专门用于"最多一行"的查询，配合 Scan 一步到位。

3. 查询多条记录

type User struct {
    ID   int
    Name string
    Age  int
}

func listUsers(db *sql.DB) ([]User, error) {
    rows, err := db.Query("SELECT id, name, age FROM users")
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var users []User
    for rows.Next() {
        var u User
        if err := rows.Scan(&u.ID, &u.Name, &u.Age); err != nil {
            return nil, err
        }
        users = append(users, u)
    }
    return users, rows.Err() // 别忘了检查迭代过程中的错误
}

4. 更新与删除

func updateAge(db *sql.DB, id, age int) (int64, error) {
    res, err := db.Exec("UPDATE users SET age = ? WHERE id = ?", age, id)
    if err != nil {
        return 0, err
    }
    return res.RowsAffected()
}

func deleteUser(db *sql.DB, id int) (int64, error) {
    res, err := db.Exec("DELETE FROM users WHERE id = ?", id)
    if err != nil {
        return 0, err
    }
    return res.RowsAffected()
}

小结：Exec 返回 sql.Result，可通过 LastInsertId() 取自增 ID，通过 RowsAffected() 取受影响行数。

五、事务处理

事务用来保证一组操作要么全部成功、要么全部回滚。下面这个转账场景是最经典的例子：

func transfer(db *sql.DB, from, to int, amount float64) (err error) {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer func() {
        if p := recover(); p != nil {
            tx.Rollback()
            panic(p)
        } else if err != nil {
            tx.Rollback()
        } else {
            err = tx.Commit()
        }
    }()

    if _, err = tx.Exec("UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, from); err != nil {
        return
    }
    if _, err = tx.Exec("UPDATE accounts SET balance = balance + ? WHERE id = ?", amount, to); err != nil {
        return
    }
    return
}

这种"在 defer 里统一处理提交/回滚/panic"的写法非常通用，避免在每个错误分支手动 tx.Rollback()。

六、启用 WAL 模式提升并发

SQLite 默认使用 Rollback Journal：写入时会对整个库加独占锁，读写互斥，高并发下性能较差。

通过在连接字符串中开启 WAL（Write-Ahead Logging），可以让读写并发：

db, err := sql.Open("sqlite3", "./test.db?_journal_mode=WAL")

两种模式对比：

模式	写入流程	并发能力
Rollback Journal	先写回滚日志 → 改主库 → 删日志	写时独占，读写互斥
WAL	追加写入 .db-wal → 提交标记	读不阻塞写、写不阻塞读；单写多读

WAL 非常适合以读为主、偶尔并发写的场景。注意：同一时间仍然只允许一个写事务。

七、数据库迁移

项目迭代中表结构会不断变化，手动改表既繁琐又容易出错，推荐使用 golang-migrate/migrate 管理迁移。

安装：

go get github.com/golang-migrate/migrate/v4

在 migrations/ 目录下按 版本号_描述.up.sql / .down.sql 成对编写迁移文件：

-- 001_create_users_table.up.sql
CREATE TABLE users (
    id   INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age  INTEGER NOT NULL
);

-- 001_create_users_table.down.sql
DROP TABLE users;

接下来，在 Go 代码中使用 migrate 库进行迁移：：

package main

import (
    "fmt"

    "github.com/golang-migrate/migrate/v4"
    "github.com/golang-migrate/migrate/v4/database/sqlite"
    _ "github.com/golang-migrate/migrate/v4/source/file"
)

func main() {
    dbURL := "test.db"
    driver, err := sqlite.WithInstance(dbURL, &sqlite.Config{})
    if err != nil {
        fmt.Println("创建数据库驱动失败:", err)
        return
    }

    m, err := migrate.NewWithDatabaseInstance(
        "file://migrations",
        "sqlite", driver)
    if err != nil {
        fmt.Println("创建迁移实例失败:", err)
        return
    }

    // 向上迁移
    err = m.Up()
    if err != nil && err != migrate.ErrNoChange {
        fmt.Println("向上迁移失败:", err)
        return
    }
    fmt.Println("向上迁移成功")

    // 向下迁移
    err = m.Down()
    if err != nil && err != migrate.ErrNoChange {
        fmt.Println("向下迁移失败:", err)
        return
    }
    fmt.Println("向下迁移成功")
}

在上述代码中：

1. 使用 sqlite.WithInstance 方法创建一个 SQLite 数据库驱动实例。
2. 使用 migrate.NewWithDatabaseInstance 方法创建一个迁移实例，传入迁移文件的路径和数据库驱动实例。
3. 使用 m.Up 方法进行向上迁移，m.Down 方法进行向下迁移。

八、最佳实践

性能

• 批量写入：用 INSERT INTO t VALUES (), (), () 一次插入多行，或将多条写入放进一个事务，速度可以提升一个数量级。

• 建好索引：对常用查询列建索引，如 CREATE INDEX idx_users_name ON users(name);。

• 连接池参数：SQLite 单写特性下，写连接设为 1 可以避免锁竞争：

  db.SetMaxOpenConns(1) // 仅针对写场景
  db.SetMaxIdleConns(1)

安全

• 永远使用参数化查询（? 占位符 + 参数），禁止字符串拼接 SQL，防止注入。
• 注意数据库文件的文件系统权限，避免越权读写。

资源管理

• Rows、Stmt、Tx 都要 defer Close()/Rollback()，避免连接泄漏。
• rows.Next() 遍历完后检查 rows.Err()，它能捕获迭代过程中的错误。

错误处理

• 业务层用 errors.Is(err, sql.ErrNoRows) 判断"没查到"这种正常情况，不要把它当成错误。
• 统一日志与错误包装（fmt.Errorf("...: %w", err)），便于排查。

九、迁移到 MySQL 的差异

Go 的 database/sql 在设计上将驱动与业务逻辑解耦，业务代码面向标准接口编程。因此，将本文的示例从 SQLite 迁移到 MySQL，增删改查与事务部分的代码几乎无需改动，主要变化集中在驱动、连接字符串以及少量 SQL 方言上。

1. 驱动与连接

MySQL 推荐使用 go-sql-driver/mysql 驱动，其由纯 Go 实现，不依赖 CGO，编译与交叉编译更为便捷：

go get github.com/go-sql-driver/mysql

sql.Open 的驱动名与数据源（DSN）需要相应调整：

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

db, err := sql.Open("mysql",
    "user:password@tcp(127.0.0.1:3306)/mydb?charset=utf8mb4&parseTime=true&loc=Local")

两者的参数差异如下表：

参数	SQLite	MySQL
驱动名	"sqlite3"	"mysql"
数据源	数据库文件路径	DSN：user:pass@tcp(host:port)/dbname?参数

常用 DSN 参数包括：charset=utf8mb4 指定字符集、parseTime=true 将 DATETIME 自动扫描为 time.Time、loc=Local 设置时区。

2. 兼容的 API

得益于 database/sql 的抽象，以下 API 在两种驱动下用法完全一致，业务代码可直接复用：

• 连接管理：db.Ping、db.Close
• SQL 执行：db.Exec、db.Query、db.QueryRow
• 结果遍历：rows.Next、rows.Scan、rows.Err
• 预编译语句：db.Prepare、stmt.Exec
• 事务控制：db.Begin、tx.Commit、tx.Rollback
• 结果元信息：result.LastInsertId、result.RowsAffected
• 占位符：MySQL 与 SQLite 均使用 ?（PostgreSQL 使用 $1）
• 错误判断：errors.Is(err, sql.ErrNoRows)

前文实现的 insertUser、getUser、listUsers、transfer 等函数，均可直接运行于 MySQL 之上。

3. 需要留意的差异

3.1 SQL 方言

建表语句存在细微差异，需按 MySQL 规范调整：

-- SQLite
id INTEGER PRIMARY KEY AUTOINCREMENT

-- MySQL
id INT PRIMARY KEY AUTO_INCREMENT

此外，TEXT 类型长度、BOOLEAN（MySQL 中实际为 TINYINT(1)）、DATETIME 精度等细节也需参照 MySQL 文档处理。

3.2 连接池配置

SQLite 基于本地文件，连接开销极低；而 MySQL 通过网络交互，连接池配置对性能影响显著，建议显式设置：

db.SetMaxOpenConns(50)
db.SetMaxIdleConns(10)
db.SetConnMaxLifetime(time.Hour)

前文针对 SQLite 单写特性给出的 SetMaxOpenConns(1) 建议不适用于 MySQL，切勿照搬。

3.3 WAL 模式不适用

第六节介绍的 _journal_mode=WAL 是 SQLite 特有的并发机制。MySQL 的 InnoDB 引擎采用 redo log 与 undo log 管理事务与并发，无需在应用层进行相应配置。

3.4 驱动特定错误

判断唯一键冲突等场景需断言驱动特定的错误类型：

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

var mysqlErr *mysql.MySQLError
if errors.As(err, &mysqlErr) && mysqlErr.Number == 1062 {
    // 唯一键冲突
}

SQLite 对应的错误码与类型定义均不相同，需按所用驱动分别处理。

3.5 数据库迁移

若使用 golang-migrate，需安装并引入 MySQL 子包：

go get github.com/golang-migrate/migrate/v4/database/mysql

迁移源 DSN 相应改为 mysql://user:pass@tcp(host:port)/dbname。

小结

本文围绕 database/sql + go-sqlite3，介绍了从连接、建表、增删改查、事务、WAL 模式到迁移与最佳实践的完整链路。

SQLite 的定位是"应用内嵌数据库"：部署零成本、读性能优秀、单文件易备份；只要理解它的单写多读特性，配合 WAL 模式与参数化查询，就能在大多数中小型 Go 项目中把它用得又快又稳。

参考资料

1. sqlite.org
2. mattn/go-sqlite3