SQLite 扩展机制怎么用？自定义函数和虚拟表怎么写？

SQLite 扩展机制的核心作用，是在不改 SQLite 源码的前提下，把业务需要的函数、聚合、排序规则或虚拟表挂进数据库引擎。常见场景包括：给 SQL 增加一个字符串清洗函数、接入全文搜索或空间索引、把外部文件伪装成表查询，甚至接入加密、压缩、地理计算这类 SQLite 默认不负责的能力。

扩展有哪些使用方式？

SQLite 扩展主要有两种接入方式：动态加载和静态链接。动态加载适合桌面工具、内部脚本、可控的服务端环境；静态链接适合移动端、嵌入式或安全策略比较严格的应用。

动态加载通常这样写：

sql
SELECT load_extension('/path/to/my_extension');
SELECT my_function('hello');

不少运行环境默认会关闭扩展加载，因为它本质上是在进程里加载本地动态库。命令行工具可以用 .load，C API 里要显式调用 sqlite3_enable_load_extension(db, 1)。如果是线上应用，别把扩展路径暴露给用户输入，否则很容易变成安全事故。

自定义标量函数怎么写？

标量函数是一进一出或多进一出的函数，最容易落地。下面这个例子把字符串转成小写，重点是入口函数名必须符合约定：sqlite3_xxx_init。

c
#include <sqlite3ext.h>
SQLITE_EXTENSION_INIT1

static void lower_safe(sqlite3_context *ctx, int argc, sqlite3_value **argv) {
  if (argc != 1 || sqlite3_value_type(argv[0]) == SQLITE_NULL) {
    sqlite3_result_null(ctx);
    return;
  }
  const unsigned char *s = sqlite3_value_text(argv[0]);
  char buf[256];
  snprintf(buf, sizeof(buf), "%s", s);
  for (char *p = buf; *p; ++p) *p = (char)tolower(*p);
  sqlite3_result_text(ctx, buf, -1, SQLITE_TRANSIENT);
}

int sqlite3_myext_init(sqlite3 *db, char **err, const sqlite3_api_routines *api) {
  SQLITE_EXTENSION_INIT2(api);
  return sqlite3_create_function(db, "lower_safe", 1, SQLITE_UTF8, 0, lower_safe, 0, 0);
}

实际开发里要注意两个坑：一是返回字符串时别把栈内存交给 SQLite 后马上失效，通常用 SQLITE_TRANSIENT；二是参数类型要检查，NULL、BLOB、非法 UTF-8 都可能出现。

聚合函数和虚拟表适合什么场景？

聚合函数适合“多行合成一个结果”，比如自定义中位数、百分位数、业务评分。它由 step 和 final 两段组成，step 逐行收集状态，final 输出结果。

c
typedef struct { double sum; int n; } AvgCtx;

static void avg_step(sqlite3_context *ctx, int argc, sqlite3_value **argv) {
  AvgCtx *p = sqlite3_aggregate_context(ctx, sizeof(AvgCtx));
  if (sqlite3_value_type(argv[0]) != SQLITE_NULL) {
    p->sum += sqlite3_value_double(argv[0]);
    p->n++;
  }
}

static void avg_final(sqlite3_context *ctx) {
  AvgCtx *p = sqlite3_aggregate_context(ctx, 0);
  p && p->n ? sqlite3_result_double(ctx, p->sum / p->n) : sqlite3_result_null(ctx);
}

虚拟表更重，它不是补一个函数，而是实现一套表接口。FTS5、RTREE、CSV 虚拟表都属于这个方向。只有当你确实想把外部数据源当表查，或者需要深度参与查询计划时，才值得写虚拟表；普通业务逻辑用函数或预处理数据更省心。

常用内置扩展怎么取舍？

FTS5 适合全文搜索，JSON1 适合处理 JSON 字段，RTREE 适合二维范围检索，STAT4 能改善部分复杂查询的统计信息。不要一看到扩展就自己写 C，SQLite 已经内置或可编译开启的扩展优先级更高。

移动端尤其要谨慎。iOS 和 Android 对动态库加载、包体大小、审核策略都有约束，很多团队会选择静态编译 SQLite，并在构建阶段打开需要的扩展。服务端脚本则可以动态加载，但要锁死扩展目录和文件白名单。

追问

动态加载和静态链接该怎么选？

动态加载的优势是灵活，扩展可以单独升级，适合内部工具、数据清洗脚本和可控服务器。静态链接的优势是可部署性和安全性更好，适合移动端、嵌入式设备和不允许运行时加载动态库的环境。取舍点在于“是否需要运行时替换扩展”：如果不需要，静态链接通常更稳。踩坑最多的是本地能 load，线上容器或 App 沙盒里找不到动态库路径。

自定义函数为什么要特别注意内存管理？

SQLite 的 C API 不会替你判断返回值生命周期。比如把栈上的 char buf[256] 返回出去，如果没有用 SQLITE_TRANSIENT，调用结束后结果可能变成脏数据。更稳的做法是明确告诉 SQLite 复制结果，或者使用 SQLite 提供的内存分配函数。边界情况还包括超长输入、NULL 参数和编码不一致，这些都要在函数入口处理。

什么情况下不该写 SQLite 扩展？

如果逻辑只服务一两个查询，而且可以在应用层完成，通常不必写扩展。扩展会增加编译、发布、跨平台调试成本，尤其 C 代码崩溃会直接拖垮宿主进程。只有当逻辑需要贴近查询执行、复用频率很高，或者应用层处理会造成大量数据搬运时，扩展才划算。很多性能问题先用索引、CTE、临时表就能解决，不要过早上虚拟表。

虚拟表开发最容易踩什么坑？

虚拟表的坑主要在 xBestIndex。如果没有正确告诉优化器哪些约束可用、成本大概多少，SQLite 可能全表扫描你的外部数据源，慢到像卡死。另一个边界是事务和并发：外部数据源未必支持 SQLite 的事务语义，写入型虚拟表尤其麻烦。建议先做只读虚拟表，并用 EXPLAIN QUERY PLAN 观察查询计划。

扩展会影响数据库安全性吗？

会，而且影响很直接。动态扩展是本地代码，权限和宿主进程一样，不能把 load_extension 开给不可信 SQL。线上系统一般禁用任意路径加载，只允许白名单扩展，甚至直接静态链接。另一个踩坑点是 SQL 注入：即使扩展函数本身安全，拼接 SELECT load_extension(...) 的路径也可能被利用。