用 Go 语言操作 SQLite 的一些注意事项

最近把博客的数据库从 MySQL 迁移到了 SQLite，主要迁移原因有：

• 使博客系统更加轻量，一键部署
• 方便备份，一个普通文件（不能直接拷贝文件）
• 方便部署，MySQL有点重，我不需要那么复杂
• SQLite 官方说其能支撑中小型网站数据库，我信了

代码改动其实很少，但是迁移过程遇到不少的坑，这篇文章简单记一下一些注意事项。

注：公司最近一位同事也恰好也用到了这个 SQLite 数据库，我发现他也有隐藏 BUG，只是因为没有压测，没有发现。

本文环境：

• 编程语言：Go + CGO
• SQLite3：mattn/go-sqlite3

共享缓存模式

设置 cache 为 shared 模式：官方文档。

据官方文档说这样会大大减少并发连接时内存的占用。

v := url.Values{}
v.Set(`cache`, `shared`)
v.Set(`mode`, `rwc`)
u := url.URL{
	Scheme:   `file`,
	Opaque:   url.PathEscape(cfg.Database.SQLite.Path),
	RawQuery: v.Encode(),
}
db, err = sql.Open(`sqlite3`, u.String())

最大连接数

db.SetMaxOpenConns(1)

SQLite3 支持多线程模式，但似乎那个模式只是为了在多个线程之间共享同一个连接，并不是为了支持并发。

如果不开启这个选项，可能会报错：database table is locked.

最大一个连接？会不会影响性能？会。但是为了数据安全不得已这样做。

实测我博客的 QPS 能达到几千，所以还是决定用它替换了 MySQL。

不区分大小写

SQLite3 的 text 类型默认 COLLATE 是 BINARY，即区分大小写。

如果要不区分大小写，应该在字段后面加上 COLLATE NOCASE。

CREATE TABLE IF NOT EXISTS tags (
    `id` INTEGER PRIMARY KEY AUTOINCREMENT,
    `name` TEXT NOT NULL UNIQUE COLLATE NOCASE,
    `alias` INTEGER NOT NULL
);

记得关闭查询结果

rows, err := db.Query(query, ...)

defer rows.Close()

否则可能导致死锁。

获得原始 sqlite3.SQLiteConn

我要获取原始 sqlite3.SQLiteConn 的原因是为了备份。

官方的例子说不能直接拿到原始连接（忘记在哪里说的了）。

我是用以下方式来获取原始连接的，还算比较好使（官方的不太优雅）：

db, err := sql.Open(`sqlite3`, `a.db`)
if err != nil {
	return err
}
defer db.Close()

conn, err := db.Conn(ctx)
if err != nil {
	return err
}
defer conn.Close()

err = conn.Raw(func(dc interface{}) error {
	rawConn := dc.(*sqlite3.SQLiteConn)
	// ...
})
// ...

值得注意的是：Raw() 回调中的 dc 只能在回调中被使用，不能在回调函数退出后继续使用。

数据库备份

虽然 SQLite3 就一个数据文件，但是不能直接复制文件的方式来达到备份的目的。因为有其它连接可能正在更新文件。

以下代码片段来自我博客实现，可以参考一下。

func (s *Service) backupSQLite3(ctx context.Context) (string, error) {
	// 备份到临时文件
	tmpFile, err := ioutil.TempFile(``, `taoblog-*`)
	if err != nil {
		return ``, err
	}
	tmpFile.Close()

	// 目的数据库
	dstDB, err := sql.Open(`sqlite3`, tmpFile.Name())
	if err != nil {
		return ``, err
	}
	defer dstDB.Close()

	dstConn, err := dstDB.Conn(ctx)
	if err != nil {
		return ``, err
	}
	defer dstConn.Close()

	if err := dstConn.Raw(func(dstDC interface{}) error {
		rawDstConn := dstDC.(*sqlite3.SQLiteConn)

		// 源数据库
		srcConn, err := s.db.Conn(ctx)
		if err != nil {
			return err
		}
		defer srcConn.Close()

		if err := srcConn.Raw(func(srcDC interface{}) error {
			rawSrcConn := srcDC.(*sqlite3.SQLiteConn)

			// 备份函数调用
			backup, err := rawDstConn.Backup(`main`, rawSrcConn, `main`)
			if err != nil {
				return err
			}

			// errors can be safely ignored.
			_, _ = backup.Step(-1)

			if err := backup.Close(); err != nil {
				return err
			}

			return nil
		}); err != nil {
			return err
		}

		return nil
	}); err != nil {
		return ``, err
	}

	zap.L().Info(`backuped to file`, zap.String(`path`, tmpFile.Name()))

	return tmpFile.Name(), nil
}