在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习惯用CONCATSQLite虽然从3.44.0版本开始也支持CONCAT函数但它更经典的写法是用||操作符。SELECT 姓名: || 张三; -- 姓名: 张三 SELECT CONCAT(姓名: , 张三); -- 姓名: 张三SQLite 3.44 才支持很多老版本SQLite不支持CONCAT为了兼容性写||是最稳的。1.2 从使用频率和场景出发SQLite常用函数可以分成五类我在整理这篇文章时没有按字母序罗列全部函数而是按真实项目里的使用频率和解决问题的场景来划分。这样安排有一个实际好处你读完每一类就能直接对应到一个真实业务场景而不是背函数签名。函数类别典型代表主要解决场景数值函数ABSROUNDCEILFLOORRANDOM数值计算、取整、随机数生成字符串函数UPPERLOWERTRIMLENGTHSUBSTRREPLACEINSTR数据清洗、格式统一、模糊匹配日期时间函数DATETIMEDATETIMEJULIANDAYSTRFTIME时间戳转换、日期计算、按时间分组聚合函数COUNTSUMAVGMAXMINGROUP_CONCAT统计报表、分组汇总、数据降维条件与空值函数CASE WHENIFNULLCOALESCENULLIFIIF逻辑判断、空值兜底、等级划分这五类刚好覆盖了我日常能遇到的九成SQL场景。后面的章节按这个顺序展开每一类我都配了真实可跑的SQL语句和注意事项。1.3 用DB Browser for SQLite快速验证函数的好习惯既然热搜词里反复出现db browser for sqlite这里就多写两句。DB Browser for SQLite简称DB4S是我目前用过最顺手的SQLite图形客户端免费开源Windows、macOS、Linux都有对应版本官网直接下载即可。它的SQL执行面板支持多标签页我习惯这样调试函数打开数据库文件后点执行SQL标签页。输入类似SELECT date(now, -7 day);这样的验证语句点执行。结果会以表格形式展示如果返回的不是预期值可以马上调整参数再执行不需要重启。验证函数时我强烈推荐用这种无表查询的方式尤其对日期时间这类需要反复试参数的函数效率能提升好几倍。等验证出正确写法再把它嵌到业务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、INTEGERUnix时间戳秒或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是日期时间函数里的瑞士军刀它的格式符非常多这里只列实际项目里用得上的格式符含义示例输出%Y四位年份2024%m两位月份05%d两位日03%H24小时制小时14%M分钟08%S秒09%w星期几0周日1%j一年中的第几天123%W一年中的第几周22%sUnix秒级时间戳1688112000举个例子业务上需要把时间统一成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;结果类似order_idproduct_listitem_count1001手机壳、钢化膜、数据线3GROUP_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不是空字符串也不是0SQLite对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不等于1SQLite有个非常独特的性质列类型推荐性type affinity它不像MySQL那么严格——理论上你可以在INTEGER列里存文本。这意味着你写SELECT 1 1;结果是多少是0FALSE。因为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里一般有两种报错和一种非预期值情况语法错误、类型错误、结果不符合预期。我的调试顺序很固定用无表查询确认函数签名执行SELECT 函数名(测试参数);如果报参数数量不对立刻能看出来。确认参数类型很多返回NULL不是因为逻辑错而是参数本身就是NULL。比如SUBSTR(NULL, 1, 2)返回NULL。遇到非预期结果先检查列是否存在NULL。缩小数据范围在SQL里加LIMIT 10或者WHERE id 某个已知行把输出控制在一两行眼睛就不容易花。分步验证写完复杂嵌套函数后把内层结果单独查出来确认再套外层。比如先SELECT SUBSTR(created_at, 1, 10) FROM users LIMIT 5;确认输出正常再拿它的结果去计算。DB Browser还有个很方便的功能是保存SQL语句。我会把常用的验证语句存成独立标签页下次遇到问题直接改参数跑省得重新敲。7.4 关于SQLite版本差异的提醒最后必须强调SQLite版本对函数行为的显著影响。前面提到的CEIL/FLOOR3.35.0、IIF3.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的函数体系虽然不大但每个函数都扎根在具体的业务场景里。我写这篇笔记最大的体会是别试图背下全部函数把常用函数的适用条件、参数含义、边界行为搞清楚遇到需求时知道该用哪一类、去哪里查就已经能解决绝大多数问题了。剩下的交给真实数据去验证。