14、用户自定义函数（UDF）：sqlite3_create_function、标量函数、聚合函数、C语言实现回调

说实话，SQLite 内置的几十个函数已经挺强大了。但我在实际项目中经常遇到这种情况：业务逻辑需要一种特殊的字符串处理，或者要算一个自定义的统计指标，内置函数根本搞不定。这时候怎么办？

嗯，SQLite 给了我们一条路——用户自定义函数，也就是 UDF。说白了，就是让你用 C 语言写自己的函数，然后注册到 SQLite 引擎里，像调用 length()、sum() 一样在 SQL 里直接调用。

我个人习惯把 UDF 分成两类：标量函数和聚合函数。前者处理一行数据返回一个值，后者处理多行数据返回一个汇总结果。下面咱们一个一个来拆解。

14.1 核心 API：sqlite3_create_function

所有 UDF 的注册都靠这个函数。它的原型长这样：

int sqlite3_create_function(
  sqlite3 *db,               // 数据库连接句柄
  const char *zFunctionName, // 函数名（SQL 里用的名字）
  int nArg,                  // 参数个数（-1 表示可变参数）
  int eTextRep,              // 编码偏好（SQLITE_UTF8 等）
  void *pApp,                // 用户自定义数据指针
  void (*xFunc)(sqlite3_context*, int, sqlite3_value**),  // 标量函数回调
  void (*xStep)(sqlite3_context*, int, sqlite3_value**),  // 聚合 step 回调
  void (*xFinal)(sqlite3_context*)                         // 聚合 final 回调
);

你看，最后一个参数是个联合体——标量函数只用 xFunc，聚合函数用 xStep + xFinal。我刚开始学的时候老搞混，后来记住一句话：标量一次搞定，聚合分两步走。

14.2 标量函数实战：实现一个字符串反转函数

先来个简单的。假设我们需要一个 reverse() 函数，把字符串倒过来。比如 SELECT reverse('hello') 返回 'olleh'。

回调函数的写法其实有固定套路：

static void reverseFunc(sqlite3_context *ctx, int argc, sqlite3_value **argv) {
    // 1. 检查参数
    if (argc != 1) {
        sqlite3_result_error(ctx, "需要1个参数", -1);
        return;
    }
    // 2. 获取输入值
    const unsigned char *input = sqlite3_value_text(argv[0]);
    if (input == NULL) {
        sqlite3_result_null(ctx);
        return;
    }
    // 3. 计算长度并反转
    int len = strlen((const char*)input);
    char *result = sqlite3_malloc(len + 1);
    if (result == NULL) {
        sqlite3_result_error_nomem(ctx);
        return;
    }
    for (int i = 0; i < len; i++) {
        result[i] = input[len - 1 - i];
    }
    result[len] = '\0';
    // 4. 返回结果
    sqlite3_result_text(ctx, result, len, sqlite3_free);
}

注册的时候这样写：

sqlite3_create_function(db, "reverse", 1, SQLITE_UTF8, NULL, reverseFunc, NULL, NULL);

注意最后两个参数传 NULL，因为这是标量函数，不需要 step 和 final。

14.3 聚合函数实战：实现一个自定义中位数函数

聚合函数稍微复杂一点。我拿中位数举例——SQLite 内置没有中位数函数，但实际报表里经常要用。

聚合函数需要维护一个「累加状态」。我一般用结构体来存：

typedef struct MedianCtx {
    double *values;
    int count;
    int capacity;
} MedianCtx;

然后实现三个回调：

Step 回调： 每来一行数据就调用一次，把值存起来。

static void medianStep(sqlite3_context *ctx, int argc, sqlite3_value **argv) {
    MedianCtx *p = (MedianCtx*)sqlite3_aggregate_context(ctx, sizeof(MedianCtx));
    if (p == NULL) return;
    
    // 第一次调用时初始化
    if (p->values == NULL) {
        p->capacity = 64;
        p->values = sqlite3_malloc(p->capacity * sizeof(double));
        p->count = 0;
    }
    // 动态扩容
    if (p->count >= p->capacity) {
        p->capacity *= 2;
        p->values = sqlite3_realloc(p->values, p->capacity * sizeof(double));
    }
    p->values[p->count++] = sqlite3_value_double(argv[0]);
}

Final 回调： 所有行处理完后调用一次，计算最终结果。

static void medianFinal(sqlite3_context *ctx) {
    MedianCtx *p = (MedianCtx*)sqlite3_aggregate_context(ctx, 0);
    if (p == NULL || p->values == NULL) {
        sqlite3_result_null(ctx);
        return;
    }
    // 排序
    qsort(p->values, p->count, sizeof(double), cmpDouble);
    // 取中位数
    double median;
    if (p->count % 2 == 0) {
        median = (p->values[p->count/2 - 1] + p->values[p->count/2]) / 2.0;
    } else {
        median = p->values[p->count/2];
    }
    sqlite3_result_double(ctx, median);
    // 清理内存
    sqlite3_free(p->values);
}

注册时这样写：

sqlite3_create_function(db, "median", 1, SQLITE_UTF8, NULL, NULL, medianStep, medianFinal);

注意这次 xFunc 传 NULL，xStep 和 xFinal 传实际函数。

14.4 回调机制的本质

你想想看，SQLite 执行一条 SQL 时，遇到你的 UDF 会怎么做？

• 标量函数： 每处理一行，就调用一次 xFunc。输入是当前行的列值，输出直接返回。
• 聚合函数： 先对每一行调用 xStep 累加状态，所有行处理完后调用一次 xFinal 输出最终结果。

这个机制说白了就是「回调驱动」。SQLite 引擎不知道你的函数内部怎么算，它只负责在合适的时机调用你注册的函数指针。我在项目中用这个特性做过很多有意思的事，比如实时计算滑动窗口的平均值、自定义的排名算法等等。

下面这张图可以帮你理清整个流程：

14.5 几个实用技巧

1. 错误处理要到位： 回调里如果遇到异常，用 sqlite3_result_error 返回错误信息，不要直接 return 了事。否则 SQLite 可能以为执行成功，返回空值，排查起来很痛苦。
2. 内存管理： 聚合函数里用 sqlite3_aggregate_context 分配的内存，SQLite 会在查询结束后自动释放。但你自己 sqlite3_malloc 出来的内存，一定要在 xFinal 里释放，否则会泄漏。
3. 性能考量： 标量函数会被频繁调用（每行一次），所以里面别做太重的操作。我见过有人在标量函数里打开文件、发起网络请求，结果一条 SQL 跑了几分钟。
4. 线程安全： 如果你的程序用了多线程，注册 UDF 时加上 SQLITE_DETERMINISTIC 标志（SQLite 3.8.3+），告诉 SQLite 这个函数对于相同输入总是返回相同结果，有助于优化器做缓存。

好了，关于 UDF 的核心内容就这些。标量函数和聚合函数的套路其实很固定，你只要记住「标量一次搞定，聚合分两步走」这个口诀，再配合 sqlite3_aggregate_context 管理状态，基本就能应对大部分场景了。

---