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

陪她去流浪 桃子 2020年07月18日 编辑 阅读次数:5477

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

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

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

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

本文环境:

共享缓存模式

设置 cacheshared 模式:官方文档

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

1
2
3
4
5
6
7
8
9
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())

最大连接数

1
db.SetMaxOpenConns(1)

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

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

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

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

不区分大小写

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

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

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

记得关闭查询结果

1
2
3
rows, err := db.Query(query, ...)

defer rows.Close()

否则可能导致死锁。

获得原始 sqlite3.SQLiteConn

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

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

我是用以下方式来获取原始连接的,还算比较好使(官方的不太优雅):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
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 就一个数据文件,但是不能直接复制文件的方式来达到备份的目的。因为有其它连接可能正在更新文件。

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
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
}

标签:sqlite · MySQL · Go