SQLite常用函数实操笔记：从字符串、日期到性能优化

在SQLite这个轻量级数据库的日常使用中，我最常被问到的一句话就是："某某函数到底怎么用来着？"。这次我把SQLite常用函数整理成一篇完整的实操笔记，把我自己在实际项目里反复用过、验证过的那些函数一次讲清楚——不只是列个清单，而是把每个函数的适用场景、参数含义、容易踩的坑都写上。无论你是刚接触SQLite的新手，还是已经在用但碰到具体问题需要查方案的开发者，这篇文章都值得收藏，需要的时候翻出来就能直接用。

先交代一下我的使用背景：我主要用SQLite做本地工具和项目原型的存储层，搭配DB Browser for SQLite做可视化调试。SQLite没有独立服务器进程，整个数据库就是一个文件，部署零成本，但正因为轻量，很多习惯用MySQL、PostgreSQL的人容易在函数、类型处理上踩坑。下面这些内容全部来自我实际调试过的场景，有的甚至是在生产环境上出过问题后才总结出来的，希望对你有用。

1. SQLite函数体系速览：先搞懂五大门类，后面才不慌

1.1 SQLite函数的调用方式与普通SQL的区别

在深入每个具体函数之前，先把最基础的概念捋一遍。SQLite里的函数调用方式跟其他数据库差别不大，就是在SELECT语句里写上函数名(参数)：

SELECT UPPER('hello'); -- HELLO SELECT ROUND(3.14159, 2); -- 3.14 SELECT LENGTH('SQLite'); -- 6

但有个初学者容易忽略的细节：SQLite允许你不带FROM子句直接调用函数。这意味着你可以在DB Browser for SQLite的SQL执行器里直接输入SELECT date('now');来验证日期函数，而不必先建一张表。这个特性对调试特别方便，我后面几乎每个例子都是这样先验证、再套用到真实查询里的。

另一个容易和MySQL混淆的点是字符串连接符。MySQL习惯用CONCAT，SQLite虽然从3.44.0版本开始也支持CONCAT函数，但它更经典的写法是用||操作符。

SELECT '姓名: ' || '张三'; -- 姓名: 张三 SELECT CONCAT('姓名: ', '张三'); -- 姓名: 张三（SQLite 3.44+ 才支持）

很多老版本SQLite不支持CONCAT，为了兼容性，写||是最稳的。

1.2 从使用频率和场景出发，SQLite常用函数可以分成五类

我在整理这篇文章时，没有按字母序罗列全部函数，而是按真实项目里的使用频率和解决问题的场景来划分。这样安排有一个实际好处：你读完每一类，就能直接对应到一个真实业务场景，而不是背函数签名。

这五类刚好覆盖了我日常能遇到的九成SQL场景。后面的章节按这个顺序展开，每一类我都配了真实可跑的SQL语句和注意事项。

1.3 用DB Browser for SQLite快速验证函数的好习惯

既然热搜词里反复出现db browser for sqlite，这里就多写两句。DB Browser for SQLite（简称DB4S）是我目前用过最顺手的SQLite图形客户端，免费开源，Windows、macOS、Linux都有对应版本，官网直接下载即可。它的SQL执行面板支持多标签页，我习惯这样调试函数：

1. 打开数据库文件后，点"执行SQL"标签页。
2. 输入类似SELECT date('now', '-7 day');这样的验证语句，点执行。
3. 结果会以表格形式展示，如果返回的不是预期值，可以马上调整参数再执行，不需要重启。

验证函数时我强烈推荐用这种"无表查询"的方式，尤其对日期时间这类需要反复试参数的函数，效率能提升好几倍。等验证出正确写法，再把它嵌到业务SQL里，能省下大量盲目拼SQL再调试的时间。

2. 数值与字符串处理：日常查询出镜率最高的函数

2.1 数值函数逐个拆解

先看数值函数，这组函数在计算金额、处理百分比、生成测试数据时非常常用。

• ABS(X)：返回X的绝对值。
• ROUND(X, Y)：把X四舍五入到小数点后Y位。Y省略时取整。
• CEIL(X)/FLOOR(X)：向上取整/向下取整，SQLite 3.35.0及以上版本才支持。
• RANDOM()：返回一个-9223372036854775808到9223372036854775807之间的随机整数。
• MOD(X, Y)：返回X除以Y的余数，支持浮点数。

我举个真实例子。之前做一个小工具，要按评分区间给用户打标签，评分是浮点数，但业务上要"向下取整到整数档位"。一开始我用CAST(score AS INT)，发现负数时行为完全不对——CAST(-2.7 AS INT)的结果是-2，而不是-3，因为它是向零取整。后来改成FLOOR(score)才得到正确结果：FLOOR(-2.7)等于-3。

提示：CAST(x AS INT)是向零取整，FLOOR(x)是向下取整。处理正数时看起来一样，一旦出现负数，结果完全不同。这是数值函数里最容易踩的坑。

ROUND也有一个有意思的行为：ROUND(2.5)在SQLite里返回2，而不是3。这是因为SQLite遵循"四舍六入五成双"的银行家舍入法（round half to even），跟小学数学教的四舍五入并不完全一样。如果你需要严格的"四舍五入"，可以这样绕过去：

-- 把0.5这个边界值调整一下 SELECT ROUND(x + 0.000001, 0);

但说实话，绝大多数业务场景用默认的ROUND就行，只有对精度极其敏感的财务计算才需要考虑这个差异。

2.2 字符串函数：清洗数据的必备武器

字符串函数是我在SQLite里用得最频繁的一类。处理用户输入、清洗导入数据、格式化输出都靠它们。

• UPPER(X)/LOWER(X)：转大写/小写。
• TRIM(X)/LTRIM(X)/RTRIM(X)：去两端空格/去左端空格/去右端空格。
• LENGTH(X)：返回字符串字符数，中文按一个字符算。
• SUBSTR(X, Y, Z)：从第Y个字符开始截取Z个字符。注意：SQLite的索引从1开始，不是从0开始。这是很多从其他语言转过来的朋友最先踩的坑。
• REPLACE(X, A, B)：把字符串X中的A替换成B。
• INSTR(X, Y)：返回Y在X中第一次出现的位置，查不到返回0。
• ||：连接多个字符串，优先级较低，拼接时建议加括号。

举一个我最常给团队演示的例子——清洗手机号。外部导入的数据经常带有空格、横线、区号前缀，需要统一格式：

SELECT phone, REPLACE(REPLACE(TRIM(phone), '-', ''), ' ', '') AS cleaned_phone, LENGTH(REPLACE(REPLACE(TRIM(phone), '-', ''), ' ', '')) AS phone_len FROM contacts;

这段代码先用TRIM去掉首尾空格，再用两层REPLACE去掉横线和空格，最后用LENGTH校验长度是否合法。一个查询就把清洗核心逻辑完成了。

2.3 PRINTF格式化：比一层层嵌套拼接更优雅

很多人不知道SQLite内置了PRINTF函数，语法跟C语言的sprintf一致。当你需要拼出带前导零、带小数位数、带千分位的字符串时，PRINTF比||字符串拼接优雅得多。

-- 把数字1格式化成001 SELECT PRINTF('%03d', 1); -- 001 -- 把金额保留两位小数 SELECT PRINTF('%.2f', 123.5); -- 123.50 -- 日期补零 SELECT PRINTF('%04d-%02d-%02d', 2024, 5, 3); -- 2024-05-03

提示：PRINTF非常适合生成带格式的文件名、订单号、批次号。比如PRINTF('ORD-%05d', order_id)就可以生成统一长度的订单编号。

2.4 实例：用字符串函数做一次完整的用户名片清洗

下面用一段综合例子，把上面的函数串起来。假设有一张users表，里面的bio字段既有大小写不统一的问题，又有首尾空格和多余换行：

SELECT id, UPPER(TRIM(LEFT(name, 1))) AS first_letter, REPLACE(TRIM(bio), CHAR(10), ' ') AS cleaned_bio, INSTR(bio, '@') AS at_pos, SUBSTR(bio, INSTR(bio, '@') + 1, LENGTH(bio)) AS after_at FROM users WHERE bio IS NOT NULL AND bio != '';

这里的CHAR(10)是换行符的写法，SUBSTR配合INSTR实现了从某个字符位置后面截取的功能，比固定偏移量灵活得多。这段SQL一次完成了首字母提取、换行清理、特殊字符定位三个任务。

3. 日期时间函数：时间戳、格式转换与日期运算的正确打开方式

3.1 五种原生日期时间函数的适用场景

SQLite的日期时间函数是使用上最容易混乱的模块，因为它的设计跟MySQL不太一样。SQLite没有专门的日期时间类型，日期值通常存成TEXT（格式YYYY-MM-DD HH:MM:SS）、INTEGER（Unix时间戳，秒）或REAL（儒略日）。那它为什么还能做日期计算？靠的就是这五个函数：

• DATE(timestring, modifier, ...)：返回日期部分，格式YYYY-MM-DD。
• TIME(timestring, modifier, ...)：返回时间部分，格式HH:MM:SS。
• DATETIME(timestring, modifier, ...)：返回日期和时间，格式YYYY-MM-DD HH:MM:SS。
• JULIANDAY(timestring, modifier, ...)：返回儒略日数，一个连续的数字日期，可以直接做加减。
• STRFTIME(format, timestring, modifier, ...)：最灵活，按自定义格式输出。

这里有个关键点：所谓timestring（时间字符串），SQLite能识别的格式包括'YYYY-MM-DD'、'YYYY-MM-DD HH:MM:SS'、'YYYY-MM-DDTHH:MM'、以及直接的Unix秒数（如1688112000），还有特殊关键字'now'。

3.2 STRFTIME格式符详解：构造任意格式的关键

STRFTIME是日期时间函数里的瑞士军刀，它的格式符非常多，这里只列实际项目里用得上的：

举个例子，业务上需要把时间统一成2024年05月03日 14:08:09这种中文格式：

SELECT STRFTIME('%Y年%m月%d日 %H:%M:%S', 'now');

STRFTIME的优势在于它既是格式化工具，也是提取工具。比如要拿到年份，可以直接STRFTIME('%Y', created_at)。

3.3 时间戳与可读时间的相互转换

这个场景太常用了，因为很多语言（比如Python、C#）默认把时间存成Unix时间戳（秒），但查数据时我们希望看到可读格式。

-- 时间戳转可读时间（注意：这里1688112000对应2023-06-30） SELECT DATETIME(1688112000, 'unixepoch'); -- 结果: 2023-06-30 08:00:00 -- 可读时间转时间戳 SELECT STRFTIME('%s', '2023-06-30 08:00:00'); -- 结果: 1688112000 -- 如果你存的是毫秒级时间戳，需要先除以1000 SELECT DATETIME(1688112000000 / 1000, 'unixepoch');

这里有个极其常见的坑：很多程序语言（尤其是Java和JavaScript）默认生成的是毫秒级时间戳，而SQLite的unixepoch修饰符默认按秒处理。如果直接把13位的毫秒数丢进去，出来的日期会是1970年附近的荒谬值。我见过不下三次这样的线上事故，所以强烈建议你在数据库里建立"时间戳统一用秒"的约定，或者写入前先除以1000。

另外，unixepoch修饰符只在SQLite 3.38.0及以上版本可用。如果你用的是老版本，处理时间戳时需要用DATETIME(timestamp, 'localtime')，靠本地时区偏移量间接换算，效果一样但语义晦涩一些。

3.4 日期运算：按天、月、年加减的实战写法

SQLite的日期加减全靠modifier（修饰符）实现。下面这些是我验证过的最常用组合：

-- 当前日期 SELECT DATE('now'); -- 2024-05-03 -- 今天的日期往前推7天 SELECT DATE('now', '-7 day'); -- 当前月份第一天 SELECT DATE('now', 'start of month'); -- 下个月第一天 SELECT DATE('now', 'start of month', '+1 month'); -- 本年度第一天 SELECT DATE('now', 'start of year'); -- 30分钟前的时间 SELECT DATETIME('now', '-30 minutes'); -- 当前时间转换成UTC格式（如果不加修饰符，'now'就是UTC） SELECT DATETIME('now', 'localtime'); -- 按本地时区显示

这里需要说明一下：'now'默认返回的是UTC时间。如果你的应用跑在中国标准时间（东八区），直接拿DATETIME('now')显示会比北京时间慢8小时。解决方式是加'localtime'修饰符：

SELECT DATETIME('now', 'localtime'); -- 当前本地时间

这条几乎是我每条涉及时间的SQL里都会带上的修饰符。

举个完整的业务例子。一家店铺要统计"最近30天有下单记录的用户"，时间字段created_at存的是秒级时间戳：

SELECT COUNT(DISTINCT user_id) FROM orders WHERE created_at >= STRFTIME('%s', DATE('now', '-30 day'));

注意这里把DATE('now', '-30 day')转成了时间戳然后在整数层面对比。这样既能利用索引（避免在created_at上套函数），又保证了语义正确。如果你写成DATE(created_at, 'unixepoch') = DATE('now', ...)，虽然逻辑也对，但没法用索引，数据量一大就会明显变慢，这个在第6章会再展开。

4. 聚合函数与分组统计：报表分析的看家本领

4.1 五大聚合函数与DISTINCT的配合

聚合函数是SQLite统计能力的核心。几乎任何"总览""汇总""明细归因"类需求都离不开这一组函数：

• COUNT(*)：统计行数，包括NULL。
• COUNT(column)：统计该列非NULL的行数。
• COUNT(DISTINCT column)：统计该列去重后的非NULL值个数。
• SUM(column)：求和。
• AVG(column)：平均值。
• MAX(column)/MIN(column)：最大/最小值，对文本列按字典序比较。

COUNT(*)和COUNT(column)的区别我多说一句：前者统计物理行数，后者忽略NULL。如果你的表结构设计得不好，某一列大量为NULL，只数这一列会导致结果偏小。这也是"为什么Excel里数出来100行，SQL里COUNT出来只有80行"的常见原因。

SUM在SQLite里有个小特性：它默认把整数相加返回整数，但如果你混入了文本类型的数字，也能自动转换后相加。稳妥起见，统计前先用CAST统一类型。

4.2 GROUP_CONCAT：把多行数据拼成一行的利器

这是个冷门但极其实用的函数，在MySQL里它叫GROUP_CONCAT，在SQLite里同名。它能把分组内的多行值拼成一个字符串，非常适合做"一对多"关系的汇总展示。

假设订单表order_items里一个订单有多条商品记录，我们想在一行里看到这个订单的所有商品名：

SELECT order_id, GROUP_CONCAT(product_name, '、') AS product_list, COUNT(*) AS item_count FROM order_items GROUP BY order_id;

结果类似：

GROUP_CONCAT如果不传第二个参数，默认用逗号,连接。如果你要拼接的字段里本身含有逗号，一定要显式指定分隔符，比如上面用的'、'，不然结果解析时会非常痛苦。

还有一个细节：GROUP_CONCAT拼接结果的长度受SQLite上限约束（默认约10亿字符），单条记录一般远达不到，但如果数据量极大，需要留意。

4.3 分组统计：GROUP BY与HAVING的配合

谈到聚合函数就必须讲GROUP BY和HAVING。GROUP BY负责分组，WHERE负责在分组前过滤，HAVING负责在分组后过滤。这个先后顺序我经常用一句话给团队讲明白："WHERE管的是进组之前的筛选，HAVING管的是组内聚合之后再筛一次。"

SELECT city, COUNT(*) AS user_count, AVG(age) AS avg_age FROM users WHERE status = 'active' GROUP BY city HAVING COUNT(*) >= 100 ORDER BY user_count DESC;

这条SQL的语义是：先筛选活跃用户，再按城市分组，算出每组的用户数和平均年龄，最后只保留用户数不少于100的城市。在报表展示"哪些城市达到运营阈值"时，这条语句就能一步到位。

4.4 实例：用一条SQL统计订单的多维度汇总

聚合函数最有价值的场景是把多条统计SQL合并成一条。以前我常写三个查询分别统计总单量、总用户数、总退款量，后来用聚合加条件表达式一次性搞定：

SELECT COUNT(*) AS total_orders, COUNT(DISTINCT user_id) AS active_users, SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refund_count, AVG(amount) AS avg_amount, MAX(amount) AS max_amount, MIN(amount) AS min_amount FROM orders WHERE created_at >= STRFTIME('%s', DATE('now', 'start of month'));

这里有个老手常用的小技巧：用SUM(CASE WHEN ... THEN 1 ELSE 0 END)来统计满足条件的行数。在SQLite里也可以写成SUM(status = 'refunded')，因为布尔表达式在SQLite里会返回0或1，作为整数参与求和完全没问题。不过可读性稍微差一些，我建议团队里统一用CASE WHEN写法，后期维护的人不会懵。这条SQL把整月订单的总量、活跃用户数、退款量、客单价、最高单、最低单全部展示出来，一次查询完成。

5. 条件逻辑与空值处理：CASE WHEN、IFNULL、COALESCE的正确打开方式

5.1 先纠正一个误区：NULL不是空字符串，也不是0

SQLite对NULL的处理和其他数据库一致，但在跟其他语言对比时最容易出问题。NULL表示"未知、缺失"，它与空字符串''和数值0是完全不同的东西：

SELECT NULL = ''; -- 结果是 NULL，不是 1，也不是 0 SELECT NULL = 0; -- 结果是 NULL SELECT NVL(NULL, 'default');

在WHERE条件里，NULL的判断必须用IS NULL或IS NOT NULL，不能写= NULL。很多在PostgreSQL里可以用!=排查NULL的习惯，到了SQLite里就失效，因为NULL != 'abc'的结果还是NULL，而NULL在WHERE里等同于FALSE。这是筛选结果"莫名变少"的头号原因。

5.2 CASE WHEN的两种写法

CASE WHEN是SQLite条件逻辑的核心，有两种写法。

第一种是"简单表达式"，把一个字段等于具体值时做映射：

SELECT product_name, CASE category_id WHEN 1 THEN '电子' WHEN 2 THEN '家居' ELSE '其他' END AS category_name FROM products;

第二种是"搜索表达式"，可以写复杂条件：

SELECT order_id, amount, CASE WHEN amount >= 1000 THEN '大额订单' WHEN amount >= 500 THEN '中等订单' ELSE '小额订单' END AS order_level FROM orders;

我实际项目中两种都用：固定字典映射用第一种，区间判断用第二种。注意CASE表达式里所有条件按顺序执行，一旦匹配到第一个为真的WHEN就停止，所以条件顺序很重要。上面的例子必须先判断大额再判断中等，如果顺序反了，>=500的档次永远不会命中大额。

5.3 IFNULL与COALESCE的区别与选择

IFNULL和COALESCE都是返回第一个非NULL值，它们的区别在于参数个数：

• IFNULL(X, Y)：两个参数，X为NULL时返回Y，否则返回X。
• COALESCE(X, Y, Z, ...)：参数不限，从左往右返回第一个非NULL值。

SELECT IFNULL(NULL, 'unknown'); -- unknown SELECT COALESCE(NULL, NULL, 'a', 'b'); -- a

业务上一个很典型的场景是用户资料补全：用户可能填了昵称nickname，但没填真实姓名real_name，展示时要优先取真实姓名，再取昵称，最后默认"匿名用户"：

SELECT id, COALESCE(real_name, nickname, '匿名用户') AS display_name FROM users;

COALESCE的另一个价值是类型自动提升：当参数中有数字有文本时，它会优先返回数值类型的结果。这在后续计算中能省一次CAST。我用COALESCE做多字段回填的次数，比IFNULL多得多，因为它更灵活。

5.4 IIF：一种轻量级的三目运算替代

SQLite从3.32.0版本开始支持IIF(condition, true_value, false_value)，语法跟Excel的IF函数几乎一致。当你只是简单的二选一时，IIF比CASE WHEN更简洁：

SELECT product_name, stock, IIF(stock > 0, '有货', '缺货') AS stock_status FROM products;

这个函数的语义是按条件返回两个值之一，本质上是CASE WHEN condition THEN true_value ELSE false_value END的简写。如果分支超过两个，还是老老实实用CASE WHEN，可读性更好。

5.5 实例：在查询中直接完成等级划分和空值兜底

把上面的函数综合起来，做一个更真实的案例。用户在商城里有积分字段points和会员等级字段level，但有些用户level为空字符串''（不是NULL），我们要根据积分自动补全等级，同时把空的手机号显示为"未绑定"：

SELECT id, username, COALESCE(NULLIF(TRIM(level), ''), CASE WHEN points >= 10000 THEN '钻石会员' WHEN points >= 5000 THEN '黄金会员' WHEN points >= 1000 THEN '白银会员' ELSE '普通会员' END) AS final_level, COALESCE(phone, '未绑定') AS display_phone FROM users;

注意两点：第一，NULLIF把空字符串转换成NULL（因为NULLIF(a, b)在a等于b时返回NULL），这样COALESCE才能兜底判断；第二，手机号如果存的是空字符串而不是NULL，这个写法会失灵，所以建议在写入层统一把空字符串转成NULL，这样查询层的COALESCE逻辑才可靠。这个小细节是排查"为什么COALESCE没生效"时最常遇到的根因。

6. 面向实战的进阶用法：十万行数据下的函数性能真相

6.1 SQLite的类型亲和性：为什么1不等于'1'

SQLite有个非常独特的性质：列类型推荐性（type affinity），它不像MySQL那么严格——理论上你可以在INTEGER列里存文本。这意味着你写：

SELECT 1 = '1';

结果是多少？是0（FALSE）。因为1 = '1'中一个是整数，一个是文本，SQLite会比较它们的存储类型，没有自动把'1'转成数字。但如果你写SELECT 1 = CAST('1' AS INTEGER);结果就是1。

这个特性直接影响函数的参数行为。比如LENGTH(12345)返回5，因为SQLite先自动把数字转成了文本再算长度。但SUBSTR(12345, 2, 1)的结果你可能预料不到——它返回'2'。SQLite在必要的时候会做隐式类型转换，但这种隐式转换有时带来意外。我的经验是：凡是涉及函数参数和比较运算，都手动用CAST显式声明类型，宁可多写两行，也不要依赖SQLite的"智能"。

6.2 函数包裹索引列是性能杀手

热搜词里有一条"十万条数据，sqlite查询需要多久"，这个问题跟是否走索引强相关。SQLite十万行全表扫描在普通NVMe固态硬盘上通常也就几十毫秒，但如果你的查询把函数套在索引列上，SQLite就无法使用B树索引，被迫全表扫描，耗时可能暴涨到几百毫秒甚至秒级。

-- 无法使用索引：对索引列套函数 SELECT * FROM orders WHERE STRFTIME('%Y', created_at) = '2024'; -- 可以使用索引：把函数作用在常量上 SELECT * FROM orders WHERE created_at >= STRFTIME('%s', '2024-01-01') AND created_at < STRFTIME('%s', '2025-01-01');

第一条SQL的意图是"查2024年的订单"，但因为把created_at套进了STRFTIME，索引直接失效。第二条SQL把函数放在常量上，让比较发生在原始时间戳列和计算好的边界值之间，索引就能正常工作。这两种写法在语义上等价，性能却可能相差一个数量级。

同样的原则适用于SUBSTR、UPPER、LOWER等一切作用于列的函数。判断原则就一句话：能对常量用函数，就绝不对列用函数。

6.3 LIKE通配符与ESCAPE转义

SQLite的LIKE是模糊匹配的常用手段，配合%（任意多个字符）和_（单个字符）使用：

-- 以"张"开头的用户名 SELECT * FROM users WHERE username LIKE '张%'; -- 包含"abc"的记录 SELECT * FROM users WHERE content LIKE '%abc%'; -- 第二个字符是数字5 SELECT * FROM users WHERE phone LIKE '_5%';

LIKE有个不直观的地方：通配符%和_如果出现在数据本身里，会造成误匹配。比如你要找的是包含100%这个文本的记录，LIKE '%100%%'会错误匹配到所有带100的行。解决办法是用ESCAPE子句指定转义字符：

SELECT * FROM products WHERE name LIKE '%100\%%' ESCAPE '\';

这里\%表示字面量百分号，\作为转义符。这个语法密度高，容易写错，建议每次用到都先跑一条验证语句确认结果。

6.4 CAST显式转换的时机

CAST函数在SQLite里非常简单：

SELECT CAST('123' AS INTEGER); -- 123 SELECT CAST(3.7 AS INTEGER); -- 3（向零取整） SELECT CAST('abc' AS INTEGER); -- 0（转换失败返回0） SELECT CAST('2024-05-03' AS TEXT); -- '2024-05-03'

它的核心价值在于控制类型，避免隐式转换带来的意外。以下几个场景我建议无条件使用CAST：

• 拼接字符串时，把数字显示转换：CAST(order_id AS TEXT) || '-detail'。
• 做数值比较时，确保两边类型一致：CAST(age AS INTEGER) >= 18。
• 时间戳从秒转毫秒或反向操作时，明确数值类型。

同时要注意，CAST('abc' AS INTEGER)返回0而不是报错，这个"宽容"行为在数据质量校验时很容易掩盖脏数据。如果导入的数据里有非数字内容，CAST会静默转成0，你可能在统计时才发现平均值被拉低了。

6.5 实例：一次完整的真实数据清洗任务

最后用一个完整案例把第6章的知识串起来。假设有一张从CSV导入的用户表，created_at字段是文本类型，存的是'2024/05/03 14:08'这种格式，而且有些行的时间字段是空的。我们要做三件事：统一时间格式、过滤掉无效记录、按月份统计新用户数。

-- 第一步：把不规范的文本时间转换为标准格式 SELECT id, CASE WHEN TRIM(created_at) = '' THEN NULL ELSE STRFTIME('%Y-%m-%d %H:%M', REPLACE(REPLACE(TRIM(created_at), '/', '-'), ' ', ' ')) END AS standard_time FROM users; -- 第二步：统计每月注册人数 SELECT STRFTIME('%Y-%m', standard_time) AS reg_month, COUNT(*) AS user_count FROM ( SELECT CASE WHEN TRIM(created_at) = '' THEN NULL ELSE STRFTIME('%Y-%m-%d %H:%M', REPLACE(REPLACE(TRIM(created_at), '/', '-'), ' ', ' ')) END AS standard_time FROM users ) WHERE standard_time IS NOT NULL GROUP BY reg_month ORDER BY reg_month;

这里我用子查询先做清洗，再在外层统计。REPLACE(REPLACE(...))把斜杠替换成横线，STRFTIME把字符串解析成标准格式。整个流程不依赖任何外部脚本，一条SQL解决数据清洗+统计两步需求。在实际项目中，这种"清洗+计算"的组合远比分开写多个临时表高效。

7. 我踩过的一些坑与排查技巧

7.1 函数返回值的类型陷阱

SQLite有个让人又爱又恨的特性：函数的返回值类型可能跟预期不一致。

• COUNT(*)返回整数，SUM(integer_column)可能返回整数，但如果列里混有浮点数，返回类型就变成浮点。
• AVG()永远返回浮点数，哪怕你传的全是整数。
• GROUP_CONCAT返回TEXT，即使源列是整数。

这些类型差异影响排序对比。比如你写SUM(amount) = 100.0去过滤总金额，如果全是整数，结果是100，按浮点比较会通过；如果混了小数，结果可能变成100.00000000001之类，比较可能失败。我的建议是：涉及金额总和时，统一用ROUND(SUM(amount), 2)处理精度，再跟业务阈值比较。

7.2 GROUP_CONCAT和子查询的NULL穿透问题

GROUP_CONCAT一个容易忽略的坑：它默认会忽略NULL值，但如果组内全部是NULL，它返回NULL而不是空字符串。这跟很多人预期的"空串"不一样。如果你希望展示为空字符串，需要加上COALESCE兜底：

SELECT order_id, COALESCE(GROUP_CONCAT(product_name, '、'), '') AS product_list FROM order_items GROUP BY order_id;

另一个坑在子查询里：GROUP_CONCAT拼出来的内容如果作为子查询结果再参与IN判断，字符串会整段作为一个值。比如WHERE id IN (SELECT GROUP_CONCAT(id) FROM ...)会失败，因为GROUP_CONCAT返回的是'1,2,3'这样一个字符串，而不是三行数字。正确做法是直接用关联子查询而不聚合。

7.3 用DB Browser for SQLite排查函数的调试思路

函数写错在DB Browser里一般有两种报错和一种非预期值情况：语法错误、类型错误、结果不符合预期。我的调试顺序很固定：

1. 用无表查询确认函数签名：执行SELECT 函数名(测试参数);，如果报参数数量不对，立刻能看出来。
2. 确认参数类型：很多返回NULL不是因为逻辑错，而是参数本身就是NULL。比如SUBSTR(NULL, 1, 2)返回NULL。遇到非预期结果先检查列是否存在NULL。
3. 缩小数据范围：在SQL里加LIMIT 10或者WHERE id = 某个已知行，把输出控制在一两行，眼睛就不容易花。
4. 分步验证：写完复杂嵌套函数后，把内层结果单独查出来确认，再套外层。比如先SELECT SUBSTR(created_at, 1, 10) FROM users LIMIT 5;，确认输出正常，再拿它的结果去计算。

DB Browser还有个很方便的功能是保存SQL语句。我会把常用的验证语句存成独立标签页，下次遇到问题直接改参数跑，省得重新敲。

7.4 关于SQLite版本差异的提醒

最后必须强调SQLite版本对函数行为的显著影响。前面提到的CEIL/FLOOR（3.35.0+）、IIF（3.32.0+）、CONCAT函数（3.44.0+）、unixepoch修饰符（3.38.0+）、NULLS LAST排序（3.30.0+）都跟版本强相关。如果你的项目跑在旧版本的SQLite上（比如某些Linux发行版自带的3.7或3.8），这些特性可能全部不可用，直接报错。

我在生产环境吃过一次亏：本地用的SQLite版本是3.40，代码里顺手用了STRFTIME('%s', date) * 1000转毫秒时间戳，部署到客户机器上一跑就报错，才发现对方的SQLite老版本不支持某些语法。从那以后，我在项目里都会做三件事：

• 启动时执行SELECT sqlite_version();打印版本号。
• 核心SQL写成兼容性更好的写法（用||而不用CONCAT，用CASE WHEN而不用IIF）。
• 在文档里明确标注"本工具要求SQLite 3.38.0及以上版本"。

检查你环境里的SQLite版本很简单：

sqlite3 --version

或者进DB Browser的"关于"弹窗查看。如果你跟我一样经常写一次性脚本验证函数，也建议把所有验证语句里的版本敏感函数统一标注一下，防止将来翻笔记时踩坑。

回头看这趟整理，SQLite的函数体系虽然不大，但每个函数都扎根在具体的业务场景里。我写这篇笔记最大的体会是：别试图背下全部函数，把常用函数的适用条件、参数含义、边界行为搞清楚，遇到需求时知道"该用哪一类、去哪里查"，就已经能解决绝大多数问题了。剩下的，交给真实数据去验证。