37. 核心标量函数

本节目标：学完本章你能熟练使用 SQLite 内置的字符串、数学、条件三类标量函数，在查询中直接加工数据，而不必先取出再在程序里处理。

回想一下，SQLite 是 serverless（无服务进程）、零配置的嵌入式数据库，整个库就是磁盘上的一个文件。你不需要起一个独立的数据库服务，打开文件就能算。标量函数（Scalar Function）就是那种”吃进去一行里的一两个值、吐出来一个新值”的函数，它对每一行独立计算，结果还是一行，行数不会变多也不会变少。这正是它和普通聚合函数（如 COUNT、SUM 会把多行压成一行）最大的区别。

为什么需要标量函数？打个比方：数据库里存的是”原始素材”，而页面上要展示的是”加工好的成品”。比如用户表里 name 存的是 ” alice “（前后带空格），列表页想直接显示干干净净的 “alice”；又比如 age 是数值，你想按年龄段打标签。这些”就地加工”的事，交给标量函数在 SQL 里一次完成，比查出来再在代码里循环处理要快、也更符合”让数据库干活”的思路。

一、字符串函数

字符串函数是最常见的标量函数家族。下面挑最实用的几个讲。

length(X) 返回字符串的字符个数（注意是字符数，不是字节数）。对 BLOB 则返回字节数。看例子，用我们统一的示例表 users(id INTEGER PRIMARY KEY, name TEXT, age INTEGER, email TEXT)：

SELECT name, length(name) FROM users;

substr(X, Y, Z) 取子串：从第 Y 个字符开始（最左是 1，负数表示从右边数），取 Z 个字符。省略 Z 则取到末尾。

SELECT substr(name, 1, 3) FROM users;

upper(X) 与 lower(X) 把字符串转成全大写或全小写。注意官方文档明确：内置的 lower()/upper() 只处理 ASCII 字母，非 ASCII 字符（如中文）需要用 ICU 扩展才有大小写转换效果。所以别指望用它们给中文”转大小写”。

SELECT upper(name), lower(email) FROM users;

trim(X)、 ltrim(X)、 rtrim(X) 分别去掉字符串两端、左端、右端的空格（或指定字符集）。清理用户输入时极好用：

SELECT trim('   hello   ');  -- 结果 'hello'

重点提示

length() 对字符串返回的是”字符（码点）数”，不是字节数。如果字段可能是 UTF-8 多字节（比如含中文），要字节长度请用 octet_length(X)。两者在纯 ASCII 下相同，遇到中文就有差别。

顺便提一个和”动态类型 + 类型亲和性”强相关的函数 typeof(X)：它返回表达式 X 的存储类，结果是 ‘null’/‘integer’/‘real’/‘text’/‘blob’ 之一。它最能直观说明 SQLite 的真相——声明类型只是”亲和性建议”，真正落盘的存储类由值自己决定：

SELECT typeof(3), typeof(3.0), typeof('3'), typeof(X'00');
-- integer / real / text / blob

实用技巧

想快速看某一列到底以什么存储类存着，直接 SELECT typeof(列名) FROM 表 LIMIT 1;，比猜声明类型可靠得多。这是验证”类型亲和性”概念最直观的手段。

其他常用的字符串函数还有 replace(X,Y,Z)（把 X 中所有 Y 替换成 Z）、instr(X,Y)（返回 Y 在 X 中首次出现的位置，从 1 开始，找不到返回 0）、concat(X,...) 与 concat_ws(分隔符,X,...)（拼接字符串）。建议在练习时逐个试一遍，体会它们的行为。

二、数学函数

数学类标量函数用于在查询里直接做数值运算。

abs(X) 返回数值的绝对值。若 X 为 NULL 则返回 NULL；若传入无法转成数字的字符串，返回 0.0；传入整数 -9223372036854775808 会溢出报错（这是 64 位有符号整数下限，没有对应的正数）。

SELECT abs(-15), abs(5), abs(NULL);  -- 15 / 5 / NULL

random() 返回一个伪随机整数，范围在 -9223372036854775807 到 +9223372036854775807 之间。它刻意避开了那个下限，所以结果永远能安全地传给 abs()。

SELECT random();

常见坑

random() 是”伪随机”，且同一会话里连续调用也会变化，但不要把它当密码学安全随机数用。另外它每次结果都不同，所以含 random() 的查询不能缓存结果。需要生成全局唯一 ID 时，更稳妥的做法是 lower(hex(randomblob(16)))。

想做四舍五入用 round(X, Y)，Y 是保留到小数点后几位（省略为 0）。想取符号用 sign(X)（负返回 -1、零返回 0、正返回 1）。这些都能在 SELECT 列表或 WHERE 条件里直接用。

三、条件函数（重点）

条件函数是把”如果……就……否则……”的逻辑写进 SQL 的关键，强烈建议掌握。

COALESCE(X, Y, ...) 返回参数里第一个不是 NULL 的值，全部为 NULL 才返回 NULL。它至少要有 2 个参数。典型场景：表里 email 可能为 NULL，想在列表里显示”未填写”而不是空白：

SELECT name, COALESCE(email, '未填写') FROM users;

订单表 orders(id INTEGER PRIMARY KEY, user_id INTEGER, amount REAL, created_at TEXT) 里，如果某笔订单金额可能为 NULL，报表里想默认显示 0：

SELECT id, COALESCE(amount, 0) FROM orders;

NULLIF(X, Y) 正好相反：如果 X 和 Y 相等就返回 NULL，不相等则返回 X。常用来”把某个特定值清零”。比如把金额为 0 的行在展示时当成 NULL：

SELECT NULLIF(amount, 0) FROM orders;

CASE 表达式是最强大的条件工具，相当于其他语言里的 if/else。它有两种写法。

简单 CASE：拿一个表达式去和多个值比相等：

SELECT name,
  CASE age
    WHEN 18 THEN '刚成年'
    WHEN 30 THEN '而立'
    ELSE '其他'
  END AS stage
FROM users;

搜索 CASE：每个 WHEN 后面是任意布尔条件，更灵活：

SELECT name,
  CASE
    WHEN age < 18 THEN '未成年'
    WHEN age BETWEEN 18 AND 60 THEN '壮年'
    ELSE '长辈'
  END AS life_stage
FROM users;

CASE 一旦匹配就短路返回，不再判断后面的 WHEN；没匹配且没写 ELSE 时返回 NULL。另外 iif(条件, 真值, 假值) 和 ifnull(X, Y) 是 CASE 的简写形态：ifnull(X,Y) 等价于两参数的 COALESCE(X,Y)，iif 等价于一个 WHEN 的 CASE。

实用技巧

在 ORDER BY 里也能用 CASE，做”自定义排序”非常方便。例如让 VIP 用户永远排在最前：ORDER BY CASE WHEN age > 60 THEN 0 ELSE 1 END, name。这是报表里的高频技巧。

四、类比小结

把标量函数想象成”流水线上的单个加工工位”：原料（一行数据的某些列）从左边进，工位按函数规则加工，成品（新值）从右边出，传送带上的包裹数量（行数）始终不变。字符串工位负责洗剪拼（trim/substr/concat），数学工位负责算（abs/random/round），条件工位负责分拣贴标（COALESCE/NULLIF/CASE）。它们和聚合函数、窗口函数并列，是 SQLite 函数体系的三大支柱；下一章的日期时间函数、第 39 章的窗口函数，本质上也都是”对数据做加工”的思路延伸。

适用场景一句话总结：凡是”每一行各自算一个新值”的需求，优先考虑标量函数，而不是把数据搬回程序里循环处理——既省网络往返，也更符合 SQL”声明式”的表达习惯。