GORM 多库适配：SQLite / MySQL / Postgres 的方言差异与全文检索

本地用 SQLite 跑得通，上线换成 PostgreSQL 就变了样，这类问题很少是 ORM 的错，而是方言差异藏进了 DSN、迁移、排序规则和全文检索。这篇以自托管博客后端为例，拆开三条驱动分支、AutoMigrate 的能力边界，以及同一句「搜索文章」在三种数据库上的三套实现。

1. 一套代码、三个数据库，第一天就会撞上的四件事

真实场景：在本地用 SQLite 开发，生产切 PostgreSQL。测试全绿，部署后接连四个问题：

中文关键词搜不到，因为 Postgres 的 simple 分词器不切中文；
大小写不同就搜不中，因为 LIKE 的敏感性与 collation 相关；
发布时间少了 8 小时，因为 MySQL 的 datetime 不带时区、Postgres 的 timestamptz 带；
并发上传同一张图没有去重，因为唯一键冲突的错误类型在不同驱动下根本不一样。

先说结论：多库适配要拆成四层——连接（驱动 / DSN）、结构（迁移 / 索引）、查询（可移植子集）、检索（各自实现）。混在一起谈，最后代码里一定散落着 if driver == "mysql"。

2. 连接层：按配置选驱动

pkg/repository.NewDB 是全项目唯一的数据库入口，按配置里的 type 分流：

switch driver := conf.Data.Db.Type; driver {
case "mysql":
	gormConfig.PrepareStmt = true
	db, err = gorm.Open(mysql.Open(dsn), gormConfig)
case "postgres":
	gormConfig.PrepareStmt = true
	db, err = gorm.Open(postgres.New(postgres.Config{DSN: dsn, PreferSimpleProtocol: true}), gormConfig)
case "sqlite":
	// schema 变化后旧 prepared statement 可能失效
	gormConfig.PrepareStmt = false
	db, err = gorm.Open(sqlite.Open(dsn), gormConfig)
default:
	panic("unknown db driver")
}

三个容易被当成样板代码忽略的点：

未知驱动直接 panic。启动期炸掉，好过运行期拿到一个 nil 的 *gorm.DB 才出错。
PrepareStmt 首先是兼容开关。SQLite 在 AutoMigrate 改表后，旧连接缓存的 prepared statement 可能指向已不存在的列，所以必须关；MySQL / Postgres 开着能省解析开销。
PreferSimpleProtocol 与 PrepareStmt 不在同一层：前者影响 pgx 发送 SQL 的协议，后者是 GORM 对 database/sql 语句的缓存。这个组合只在真实 Postgres 上验证过，别拿本地 SQLite 的绿灯推断。

DefaultGormConfig 集中了跨库基线配置，避免测试与生产两套会话行为漂移。其中 NowFunc 统一返回 time.Now().UTC()，是三库能对齐时间的前提——SQLite 把时间存成文本，MySQL 的 datetime 不带时区，Postgres 的 timestamptz 带时区。TranslateError 则刻意关掉：冲突识别不依赖 GORM 的翻译结果，而是收进自己的 IsDuplicateKey。

3. DSN 与连接池：三行配置，三种世界观

同一个 data.db 节点，三种写法：

data:
  db:
    type: sqlite
    dsn: ./blog-test.db?_busy_timeout=5000
    # type: mysql
    # dsn: user:<your-password>@tcp(127.0.0.1:3306)/blog?charset=utf8mb4&parseTime=True&loc=UTC
    # type: postgres
    # dsn: host=postgres user=blog password=<your-password> dbname=blog port=5432 sslmode=disable TimeZone=UTC

差异集中在三处：DSN 形式（路径 + query / URL 风格 / 空格分隔的 key=value）、时区参数（loc vs TimeZone）、字符集（MySQL 必须显式 utf8mb4，Postgres 是库级 encoding）。

busy_timeout 是 SQLite 最容易漏的一项：写锁被占用时，默认立即返回 SQLITE_BUSY；设成 5000 后变成最多等 5 秒。但等待只是缓解，SQLite 的写锁是库级的，maxOpen 大于 1 只会让更多连接排队抢同一把锁。设计文档要求 SQLite 下强制 maxOpen=1，而当前 NewDB 仍是直接使用配置值——这是个还没收口的差异，本地配置里的 8 就该改小。

连接池在三库之间是同一套 database/sql 接口，参数来自 data.pool。注意 maxLifetime 单位是秒，且必须小于数据库侧的空闲回收时间，否则会拿到已被服务端关闭的连接。

4. AutoMigrate：顺序、索引名，以及它不肯做的事

模型注册顺序就是建表顺序：ProvideModels 把 system.Models() 与 business.Models() 首尾相接，先系统表后业务表。当前没有外键，顺序不影响能否建成，但影响「第一次建库失败时留下了什么」——按依赖顺序建，才看得出停在哪一步。

索引是最容易被方言打脸的地方。SQLite 与 PostgreSQL 的索引名在同一个 schema 内共享命名空间，在 a(x) 上建完 idx_x，再在 b(x) 上建同名索引会直接失败；MySQL 的索引名只在表内唯一。所以「不带表名的简写」写出来的 schema 只能在 MySQL 上跑。约定因此是 <uk|idx>_<表名>_<列名>，复合索引还要用 priority 标记列序：

Status      ArticleStatus `gorm:"size:16;not null;default:draft;index:idx_biz_article_status_published_at,priority:1"`
PublishedAt *time.Time    `gorm:"index:idx_biz_article_status_published_at,priority:2,sort:desc"`

size 必须显式声明。GORM 在 size 为 0 且列上无索引、无默认值时，会把 string 映射成 MySQL 的 longtext；在有索引或默认值时又只给 varchar(191)，username 这类列会被悄悄截断。反过来的坑是：SQLite 驱动的 DataTypeOf 对 string 恒返回 text，完全忽略 size。所以「列长度与文档一致」的断言只能落在 GORM schema 层（读 field.Size），在 SQLite 上断言 varchar(255) 永远不会通过。

AutoMigrate 只做增量：加列、加索引可以，删列、改类型、改索引名一律不管。这意味着「模型是唯一事实」只在新建库时成立。已有库的结构演进必须走 deploy/sql/ 下的脚本，并用 make schema-check 比对 AutoMigrate 结果与 schema.sql，否则生产库会安静地停在旧结构上。sort:desc 也一样：MySQL 8 与 Postgres 支持索引方向，SQLite 会忽略但功能不受影响——所以排序一律在 SQL 里显式写 ORDER BY。

5. 方言差异：唯一真正难归一的是唯一键

能力	SQLite	MySQL 8	Postgres 16	项目做法
自增主键	`INTEGER PRIMARY KEY`	`AUTO_INCREMENT`	`IDENTITY`	GORM 抽象
时间类型	TEXT	`datetime`（无时区）	`timestamptz`	应用层统一 UTC
大小写	`LIKE` 对 ASCII 不敏感	取决于 collation	`LIKE` 敏感	比较用 `LOWER()`
唯一冲突	`UNIQUE constraint failed`	errno 1062	SQLSTATE 23505	`IsDuplicateKey` 归一

自增、布尔、时间这三样 GORM 基本能抹平，JSON 列不做库端查询就没有差异。真正难归一的是唯一键冲突，因为它直接决定业务语义：并发时是「去重成功」还是「报 500」。

func IsDuplicateKey(err error) bool {
	var mysqlErr *mysql.MySQLError
	if errors.As(err, &mysqlErr) && mysqlErr.Number == 1062 {
		return true
	}
	var pgErr *pgconn.PgError
	if errors.As(err, &pgErr) && pgErr.Code == "23505" {
		return true
	}
	var sqliteErr sqlite3.Error
	if errors.As(err, &sqliteErr) && isSQLiteUniqueCode(int(sqliteErr.ExtendedCode)) {
		return true
	}
	return strings.Contains(strings.ToUpper(err.Error()), "UNIQUE CONSTRAINT FAILED")
}

三个坑：必须用 errors.As 而非类型断言，驱动错误经常被 GORM 或上层 fmt.Errorf("%w") 包过一层；SQLite 的 lastError 是值类型，断言成指针永远不匹配；最后的文本兜底只服务「只剩字符串」的驱动。isSQLiteUniqueCode 只认唯一约束与主键冲突，避免把 NOT NULL、外键失败误判成重复键。

媒体上传的去重就靠它：先按 hash 查，查不到则插入，插入撞上唯一索引时再回查并标记 Deduplicated。这一层识别不出来，结果不是去重，而是一个 500。

6. 全文检索：一个接口，三套实现

pkg/search 只暴露一个接口，把方言全部关在实现里：

type Searcher interface {
	Ensure(ctx context.Context) error
	Search(ctx context.Context, keyword string, limit, offset int) ([]Result, int64, error)
	Reindex(ctx context.Context) (int64, error)
}

func New(db *gorm.DB) Searcher {
	switch db.Dialector.Name() {
	case "mysql":
		return &mysqlSearcher{db: db}
	case "postgres":
		return &postgresSearcher{db: db}
	default:
		return &sqliteSearcher{db: db}
	}
}

Ensure 在服务启动时建索引（失败只记警告，不阻塞启动）；Search 给业务层；Reindex 挂到管理端手动重建。Result 只带检索需要的五列，不返回正文，避免把大字段塞进列表响应。

6.1 SQLite：FTS5 + trigram + 触发器

SQLite 没有可用的内置全文索引，必须用 FTS5 虚拟表，并用触发器跟主表同步：

CREATE VIRTUAL TABLE IF NOT EXISTS article_search USING fts5(
  article_id UNINDEXED, title, summary, content, tokenize='trigram');

触发器同样在 Ensure 里创建（三个各一），这里只留虚拟表定义。查询用 MATCH 加 bm25() 排序，bm25 越小越相关，默认升序正好是想要的顺序：

base := s.db.WithContext(ctx).Table("article_search AS f").
	Joins("JOIN biz_article a ON a.article_id = f.article_id").
	Where("article_search MATCH ?", keyword).
	Where("a.status = ? AND a.deleted_at IS NULL", "published")

err := base.Order("bm25(article_search)").Limit(limit).Offset(offset).Scan(&rows).Error

三个硬约束：trigram 分词器要求关键词至少 3 个字符（中文按 rune 计），不足就回退 LIKE；关键词必须走绑定参数，不能拼进 MATCH 字符串——FTS5 的查询语法认 *、"、OR，拼字符串等于把查询语法暴露给用户；FTS5 默认不参与 SQLite 编译，驱动是 mattn/go-sqlite3，必须带 -tags sqlite_fts5，Makefile 里的 GO_TAGS 编译标签就是为它准备的。漏掉这个标签，编译能过，启动时 CREATE VIRTUAL TABLE 才炸。

6.2 MySQL：FULLTEXT + ngram 解析器

MySQL 的 Ensure 先查系统表，再决定是否 ALTER，因为 ADD FULLTEXT INDEX 不是幂等操作：

完整语句是 ALTER TABLE biz_article ADD FULLTEXT INDEX ft_article_search (...) WITH PARSER ngram。查询与排序都用 MATCH ... AGAINST，列清单必须与索引列完全一致，少一列就用不上索引。默认分词器不切中文，WITH PARSER ngram 才让「全文检索」这种连续中文可命中，而 ngram_token_size 默认 2，单字搜索仍然无解。InnoDB 的 FULLTEXT 在写入时维护倒排索引，发布文章的写延迟会比 SQLite 高。

6.3 Postgres：GIN(tsvector) + ILIKE 兜底

Postgres 给表达式建 GIN 索引，查询时索引表达式必须一字不差地重写一遍，所以 ts 单独提出来复用：

ts := `to_tsvector('simple', coalesce(title,'') || ' ' || coalesce(summary,'') || ' ' || coalesce(content,''))`
// simple 分词器不切中文，用 ILIKE 覆盖中文/部分匹配；英文仍走 tsvector。
where := "(" + ts + ` @@ plainto_tsquery('simple', ?) OR title ILIKE ? OR summary ILIKE ? OR content ILIKE ?)`

为什么留着 ILIKE：simple 分词器按空白和标点切分，不切中文，纯 tsvector 对中文等于不可用。所以「英文走 tsvector、中文靠 ILIKE」是明确的取舍，不是漏改。代价也很清楚：OR ILIKE 会让优化器退化成扫描，中文检索基本用不上 GIN 索引，数据量上去之后只能换 zhparser 或外部检索引擎。

7. 把「前台语义」收进一个 Scope

检索 SQL 里反复出现「已发布 + 到点 + 未删除」，这是前台查询的统一语义，抽成 GORM Scope：

// PublishedOnly 是前台查询的统一 Scope：已发布且到点（定时发布语义）。
func PublishedOnly(db *gorm.DB) *gorm.DB {
	return db.Where("status = ? AND published_at IS NOT NULL AND published_at <= ?",
		model.ArticleStatusPublished, time.Now())
}

Scope 只负责条件，不负责表名。pkg/search 用的是 Table("biz_article") 而不是 Model(&Article{})，GORM 因此不会自动补 deleted_at IS NULL，三套实现都手写了这一句。这是重构中最容易丢的隐含依赖：改成 Model 会突然多出软删除条件，而 Scan 的目标又不是模型，行为就难解释了。

业务层只认 Searcher，失败回退 LIKE：索引没建、FTS5 没编译进去、MySQL 正在重建索引，这些都不该让搜索接口直接 500。但回退必须留日志，否则线上会长期停在「慢但能跑」的状态，没人发现索引已经坏了。

8. 边界与坑

busy_timeout 只解决等待，不解决并发模型。SQLite 写多读多时必须 maxOpen=1 + WAL，并接受串行写。
迁移顺序：schema.sql 与 seed.sql 不能颠倒；init/db 只建得出已建模的表，schema.sql 里多出的表不会因此出现。
生产禁用 init 入口。POST /api/init/db 没有鉴权却能触发全库 DDL。InitEnabled() 在 production 下默认关闭，但 config/production.example.yml 里仍写着 enable: true，照抄就把建表入口开到公网。
AutoMigrate 不删列、不改类型、不改索引名。改结构走 deploy/sql/，用 make schema-check 防漂移。
FTS5 依赖编译 tag。CI 与本地必须同一套 -tags sqlite_fts5，否则「本地能搜、镜像里不能搜」。
三套检索的「搜得到」边界不同：SQLite 3 字符起、MySQL ngram 2 字符起、Postgres 中文靠 ILIKE。接口文档要写清楚，别让用户以为搜不到就是没有。
OPTIMIZE TABLE 会锁表重建，只能作为管理端动作；UTC 也只在应用层成立，绕过应用写库或用 CURRENT_TIMESTAMP 默认值，都会把本地时区带进来。

9. 小结

多库适配真正可用的结论只有一句：把方言差异收敛到少数几个入口——驱动选择在 NewDB，唯一键识别在 IsDuplicateKey，检索在 Searcher，前台语义在 PublishedOnly。剩下 90% 的代码不该知道自己跑在哪个数据库上。

代价也要认：SQLite 适合起步与单机自托管，Postgres 适合生产，MySQL 是兼容选项。三者不可能在所有能力上等价，能等价的部分用集成测试锁住，不能等价的部分写进文档。

下一篇换个角度：请求进来之后，怎么用 requestId 把访问日志、业务错误和 panic 串成一条能检索的链路。

GORM 多库适配：SQLite / MySQL / Postgres 的方言差异与全文检索

1. 一套代码、三个数据库，第一天就会撞上的四件事

2. 连接层：按配置选驱动

3. DSN 与连接池：三行配置，三种世界观

4. AutoMigrate：顺序、索引名，以及它不肯做的事

5. 方言差异：唯一真正难归一的是唯一键

6. 全文检索：一个接口，三套实现

6.1 SQLite：FTS5 + trigram + 触发器

6.2 MySQL：FULLTEXT + ngram 解析器

6.3 Postgres：GIN(tsvector) + ILIKE 兜底

7. 把「前台语义」收进一个 Scope

8. 边界与坑

9. 小结

Golang 后端

相关文章

数据库演进：SQLite 起步、Postgres 生产、MySQL 兼容

自托管博客技术选型：Go + Astro + React 管理端

请求链路可观测性：用 RequestID 串起访问日志与业务错误

评论

留下评论