第16章用户自定义函数（UDF）

课程目录第16章用户自定义函数（UDF）

16、用户自定义函数（UDF）：sqlite3_create_function、标量函数、聚合函数、在C语言中注册UDF

说实话，SQLite 内置的函数已经挺丰富了。但做实际项目时，总会遇到一些「SQL 搞不定」的逻辑。比如解析自定义格式的字符串、做复杂的数学变换，或者对一组数据做特殊的统计。

这时候怎么办？

答案就是：用户自定义函数（UDF）。说白了，就是用 C 语言写一个函数，然后注册到 SQLite 里，让 SQL 语句能直接调用它。

我个人习惯把 UDF 分成两类：标量函数和聚合函数。下面我一个个讲清楚。

16.1 标量函数：一行输入，一行输出

标量函数是最简单的 UDF。它接收若干参数，返回一个值。每行数据调用一次。

举个例子：假设我们有一个字段存的是 JSON 字符串，但 SQLite 没有内置的 JSON 解析函数（3.9.0 之前）。我们可以自己写一个 json_extract_value 函数。

先看 C 语言实现：

#include <sqlite3ext.h>
SQLITE_EXTENSION_INIT1

#include <string.h>
#include <stdlib.h>

static void json_extract_value(
    sqlite3_context *context,
    int argc,
    sqlite3_value **argv
) {
    // 检查参数个数
    if (argc != 2) {
        sqlite3_result_error(context, "需要两个参数：json字符串和key", -1);
        return;
    }

    // 获取输入值
    const char *json = (const char *)sqlite3_value_text(argv[0]);
    const char *key = (const char *)sqlite3_value_text(argv[1]);

    if (json == NULL || key == NULL) {
        sqlite3_result_null(context);
        return;
    }

    // 简单的key查找（仅演示，实际应使用完整JSON解析）
    char search_key[256];
    snprintf(search_key, sizeof(search_key), "\"%s\":", key);

    const char *found = strstr(json, search_key);
    if (found == NULL) {
        sqlite3_result_null(context);
        return;
    }

    // 跳过key和冒号，找到值
    found += strlen(search_key);
    while (*found == ' ' || *found == '\t') found++;

    // 提取值（简化处理，假设是字符串）
    if (*found == '"') {
        found++;
        const char *end = strchr(found, '"');
        if (end == NULL) {
            sqlite3_result_null(context);
            return;
        }
        int len = end - found;
        char *value = sqlite3_malloc(len + 1);
        strncpy(value, found, len);
        value[len] = '\0';
        sqlite3_result_text(context, value, len, sqlite3_free);
    } else {
        // 数字或布尔值
        const char *end = found;
        while (*end != ',' && *end != '}' && *end != '\0') end++;
        int len = end - found;
        char *value = sqlite3_malloc(len + 1);
        strncpy(value, found, len);
        value[len] = '\0';
        sqlite3_result_text(context, value, len, sqlite3_free);
    }
}

嗯，这里要注意：sqlite3_context 是函数与 SQLite 引擎通信的桥梁。通过它，你可以返回结果、设置错误信息，甚至控制内存生命周期。

我的经验： 在 UDF 里分配内存时，一定要用 sqlite3_malloc 而不是 malloc。因为 SQLite 内部有自己的内存管理机制，用 sqlite3_free 作为析构函数，能避免内存泄漏。我曾经在一个项目里混用了 malloc 和 sqlite3_free，结果排查了整整两天才找到原因。

16.2 聚合函数：多行输入，一行输出

聚合函数就更有意思了。它接收多行数据，最终返回一个汇总结果。比如 SUM、AVG 都是聚合函数。

实现一个聚合函数需要三个回调：

step 回调：每行数据调用一次，累加中间结果
final 回调：所有行处理完后调用，返回最终结果
（可选）xStep 和 xFinal2：用于支持 DISTINCT 等高级功能

下面我写一个 STDDEV_POP（总体标准差）的聚合函数：

typedef struct {
    double sum;
    double sum_sq;
    int count;
} StddevContext;

static void stddev_step(
    sqlite3_context *context,
    int argc,
    sqlite3_value **argv
) {
    StddevContext *ctx = (StddevContext *)sqlite3_aggregate_context(context, sizeof(StddevContext));
    if (ctx == NULL) return;

    double val = sqlite3_value_double(argv[0]);
    ctx->sum += val;
    ctx->sum_sq += val * val;
    ctx->count++;
}

static void stddev_final(
    sqlite3_context *context
) {
    StddevContext *ctx = (StddevContext *)sqlite3_aggregate_context(context, 0);
    if (ctx == NULL || ctx->count == 0) {
        sqlite3_result_null(context);
        return;
    }

    double mean = ctx->sum / ctx->count;
    double variance = (ctx->sum_sq / ctx->count) - (mean * mean);
    double stddev = sqrt(variance);

    sqlite3_result_double(context, stddev);
}

你想想看，sqlite3_aggregate_context 这个函数很关键。它会在第一次调用 step 时自动分配内存，后续调用复用同一块内存。final 回调里传 0 表示只获取指针，不重新分配。

避坑指南： 我曾经在 step 回调里用 sqlite3_aggregate_context 分配了很大的结构体，结果处理 1000 万行数据时内存暴涨。后来发现，这个函数每次调用都会分配内存，但 SQLite 会复用。正确的做法是：只在第一次调用时分配，后续用 sqlite3_aggregate_context(context, 0) 获取已有指针。

16.3 注册 UDF：把函数挂到 SQLite 上

函数写好了，怎么让 SQLite 认识它？用 sqlite3_create_function。

看代码：

int register_udfs(sqlite3 *db) {
    int rc;

    // 注册标量函数
    rc = sqlite3_create_function(
        db,                          // 数据库连接
        "json_extract_value",        // SQL 中的函数名
        2,                           // 参数个数（-1 表示可变参数）
        SQLITE_UTF8,                 // 编码
        NULL,                        // 用户数据指针
        json_extract_value,          // 函数指针
        NULL,                        // 聚合 step（标量函数传 NULL）
        NULL                         // 聚合 final（标量函数传 NULL）
    );
    if (rc != SQLITE_OK) return rc;

    // 注册聚合函数
    rc = sqlite3_create_function(
        db,
        "stddev_pop",
        1,                           // 一个参数
        SQLITE_UTF8 | SQLITE_DETERMINISTIC,  // 确定性函数
        NULL,
        NULL,                        // 标量函数传 NULL
        stddev_step,                 // step 回调
        stddev_final                 // final 回调
    );
    if (rc != SQLITE_OK) return rc;

    return SQLITE_OK;
}

这里有个细节：SQLITE_DETERMINISTIC 标志告诉 SQLite，这个函数对于相同的输入总是返回相同的结果。加上这个标志后，SQLite 可以做更多优化，比如缓存结果。我建议只要你的函数是纯函数（没有副作用），就加上这个标志。

16.4 完整示例：在 SQL 中使用 UDF

注册完成后，就可以在 SQL 里直接调用了：

-- 使用标量函数
SELECT json_extract_value('{"name":"Alice","age":30}', 'name');
-- 返回 'Alice'

-- 使用聚合函数
SELECT stddev_pop(score) FROM exam_results;
-- 返回成绩的总体标准差

是不是很自然？就像 SQLite 内置的函数一样。

16.5 知识体系总览

下面这张图总结了 UDF 的核心概念和流程：

16.6 一些实用建议

最后，分享几个我在项目中积累的经验：

参数校验要严格：UDF 被调用时，参数可能来自用户输入。一定要检查 sqlite3_value_type，避免类型不匹配导致崩溃。
善用用户数据指针：sqlite3_create_function 的第五个参数可以传任意指针。我经常用它传递配置信息或缓存对象，避免使用全局变量。
性能考量：标量函数每行调用一次，如果处理逻辑很重，会拖慢整个查询。对于大数据量，尽量在 SQL 层面做过滤，减少 UDF 的调用次数。
测试要全面：UDF 运行在 SQLite 的进程空间里，一个段错误就能让整个应用崩溃。我习惯写单元测试，覆盖 NULL 输入、边界值、大字符串等场景。

核心要点：

标量函数用 xFunc 回调，每行调用一次
聚合函数用 xStep + xFinal 回调，支持多行汇总
注册用 sqlite3_create_function，注意区分标量和聚合
内存管理统一用 SQLite 的分配器
加上 SQLITE_DETERMINISTIC 标志让优化器更智能

UDF 是 SQLite 扩展能力的核心。掌握了它，你就能把 C 语言的强大功能无缝集成到 SQL 查询中。下次遇到 SQL 搞不定的逻辑，不妨想想：能不能写个 UDF 来解决？

公众号:蓝海资料掘金营，微信deep3321

📚 课程目录

16、用户自定义函数（UDF）：sqlite3_create_function、标量函数、聚合函数、在C语言中注册UDF

16.1 标量函数：一行输入，一行输出

16.2 聚合函数：多行输入，一行输出

16.3 注册 UDF：把函数挂到 SQLite 上

16.4 完整示例：在 SQL 中使用 UDF

16.5 知识体系总览

16.6 一些实用建议