SQLite常用函数一篇讲透字符串、日期、聚合、类型转换与真实性能体验做SQLite开发的人先回答一个问题你统计总行数是用SELECT COUNT(*) FROM 表名还是先查出所有数据再用代码循环计数如果选了后者那这篇关于SQLite常用函数的博文可能就是你要补的课。SQLite别看它体积小、是嵌入式数据库函数体系其实相当完整从字符串处理到日期计算从聚合统计到条件逻辑完全能支撑日常开发。但它的函数和MySQL、SQL Server这类数据库差别很大很多从别的数据库转过来的朋友第一步就栽在函数不兼容上。这篇把SQLite常用函数按类型拆开讲每个函数都配真实例子最后还会聊一下十万条数据场景下函数和索引的实际表现给正在评估SQLite或者准备迁移数据库的同学一个参考。1. SQLite常用函数先搞清楚它和MySQL的差异1.1 为什么单独开一篇讲函数SQLite的函数体系在数据库圈子里是出了名的“小而全”。它的下载包才一兆左右但内置的函数支持字符串、数学计算、日期时间、聚合分析、类型判断等等几乎所有常规业务都能覆盖。我见过不少团队把SQLite当原型数据库后期再迁到MySQL或者PostgreSQL结果迁移时一大半问题都出在函数名不兼容上。比如MySQL里算当天日期的CURDATE()在SQLite里是DATE(now)MySQL里的NOW()SQLite里是DATETIME(now, localtime)。这些差异如果不在前期了解清楚迁移脚本就得重写一半。更关键的是SQLite的很多函数逻辑和别的数据库不完全一样。最典型的是ROUND()函数SQLite的实现在某些版本里采用了和多数数据库不同的舍入策略直接照搬经验会得到意外结果。另外SQLite的类型系统是“动态类型”列可以存任何类型的数据这导致一些隐式类型转换的行为也跟MySQL不一样。所以才需要一篇专门的博文把这些常用函数和它们背后的行为逻辑讲清楚避免“看起来差不多的SQL跑出来结果不对”这种尴尬。1.2 SQLite函数分类与特性概览SQLite内置函数大致可以分成五类字符串函数、数学函数、日期时间函数、聚合函数和条件/逻辑函数。此外还有一小类“类型转换与务实函数”比如CAST()、typeof()、length()、changes()这些在实际调试和写业务逻辑时也非常有用。下表是这五类函数的代表性成员和典型用途函数类别代表函数典型用途字符串函数substr、replace、trim、length、upper、lower、printf文本清洗、字段拼接、格式化输出数学函数abs、round、random、min、max、sqrt、pow数值计算、随机抽样、取整处理日期时间函数date、time、datetime、julianday、strftime时间戳转换、日期格式化、时间差计算聚合函数count、sum、avg、min、max、total、group_concat统计报表、分组汇总、字符串聚合条件/逻辑函数iif、coalesce、ifnull、nullif、CASE WHEN空值兜底、条件判断、数据映射需要注意SQLite没有类似MySQLIF()那种内置函数条件判断主要靠CASE WHEN或IIF()。IIF()是SQLite 3.32.0开始提供的简单条件函数类似其他语言里的三元运算符写起来比CASE简练但嵌套复杂逻辑时还是CASE更清晰。2. 字符串函数实操拼接、截取、替换与正则2.1 字符串拼接与大小写处理SQLite的字符串拼接符号是||这和很多编程语言一样但和MySQL的CONCAT()不同。MySQL里写SELECT a || b在默认配置下返回的是数值0因为MySQL把||当逻辑或而SQLite里SELECT 运 || 维 || 猫;能直接得到字符串“运维猫”。这是两套完全不同的习惯混合写必踩坑。大小写转换在SQLite里是upper(X)和lower(X)注意这两个函数默认只处理ASCII字符。中文没有大小写概念所以不受影响但如果你存的是带重音的拉丁字符比如“café”upper()可能不会把它变成“CAFÉ”。业务上有国际化需求时大小写转换最好在应用层做。还有一个特别实用的函数是length(X)它返回的是字符个数不是字节数。中文字符算1个这和MySQL的CHAR_LENGTH()一致但和LENGTH()返回字节数不同。写检查“用户名不得少于3个字符”这类逻辑时用length就对了。字符串截取用的最多的两个是substr(X, Y, Z)和substring(X, Y, Z)两者行为一致都返回从第Y个字符开始、长度Z的子串。很多人第一次用容易犯迷糊的是索引从1开始不是0。substr(sqlite, 2, 3)返回的是qli不是sql。如果要取末尾若干字符可以用负数起始位置substr(博客系统, -2, 2)返回“系统”。这个负数偏移的用法在别的数据库里不一定有属于SQLite的小彩蛋。2.2 截取、替换与LIKE/正则匹配文本清洗是SQLite开发里最常见的操作之一trim(X)、ltrim(X)、rtrim(X)分别去掉字符串两端、左端、右端的空白字符。这三个函数还能带第二个参数指定要去掉的其他字符比如trim(xx运维猫xx, x)返回“运维猫”。从外部导入的数据经常带换行符和回车符这种情况下推荐用replace(X, Y, Z)做替换replace(原字段, char(10), )就能把换行符全部去掉。注意char()函数也可以用来构造特殊字符SQLite的char(13)是回车、char(10)是换行这在清洗Windows和Linux混合换行的CSV文件时非常有用。代替LIKE做模糊匹配时要注意SQLite的LIKE默认不区分大小写但这只针对ASCII字母。LIKE的百分号和下划线行为跟MySQL类似但ESCAPE子句的写法一样WHERE name LIKE 100!% ESCAPE !能匹配以“100%”开头的字符串。如果业务需要更复杂的正则匹配SQLite默认不开启正则函数必须自己定义或加载扩展。编译时如果带了SQLITE_ENABLE_REGEXP选项内置的REGEXP运算符才能用。如果你的SQLite没有正则扩展又必须在库内做正则可以在初始化时注册自定义函数示例代码如下static void regexp_function(sqlite3_context *ctx, int argc, sqlite3_value **argv) { const char *pattern (const char *)sqlite3_value_text(argv[0]); const char *text (const char *)sqlite3_value_text(argv[1]); regex_t re; int rc regcomp(re, pattern, REG_EXTENDED); if (rc 0) { rc regexec(re, text, 0, NULL, 0); sqlite3_result_int(ctx, rc 0 ? 1 : 0); regfree(re); } else { sqlite3_result_error(ctx, invalid regex, -1); } }注册之后就能写WHERE email REGEXP ^[a-zA-Z0-9._%-][a-zA-Z0-9.-]\\.[a-zA-Z]{2,4}$这种SQL了。如果没有正则扩展普通场景先评估一下LIKE加GLOB能不能覆盖需求。GLOB是SQLite特有的、区分大小写的模式匹配函数星号*相当于LIKE的百分号%问号?相当于下划线_。注意LIKE和GLOB在字段有索引时可以分别配合前缀匹配、后缀匹配使用索引优化器会尝试范围扫描但以通配符开头的模式通常无法命中索引数据量大时会变成全表扫描。3. 数学函数与类型隐式转换3.1 常用数学函数与ROUND精度坑SQLite的数学函数包括abs(X)、round(X)、random()、randomblob(N)、min(a, b, ...)、max(a, b, ...)以及sqrt、pow等sqrt和pow需要SQLite 3.35.0以上或者编译时开启了数学扩展。abs(-3)返回3round(3.14159, 2)返回3.14random()返回-9223372036854775808到9223372036854775807之间的随机整数。想生成0到1之间的随机浮点数可以写成abs(random()) / 9223372036854775807.0注意分母必须带.0才能触发浮点除法。ROUND()是SQLite里最具迷惑性的函数。默认情况下round(3.5)在多数数据库返回4SQLite也返回4看起来没毛病。但测试一下round(2.5)SQLite返回2不是3。SQLite的ROUND()用的是“四舍六入五成双”银行家舍入精确位数的5是否进位要看前一位奇偶。这是因为SQLite内部用的是IEEE 754二进制浮点运算2.5在二进制里并不能精确表示实际存储值是2.4999999999999998所以向下取整。如果业务要求严格“四舍五入”就别用ROUND()在应用层用Decimal类型处理或者先把数值乘以10的N次方、用CAST为整数再除以10的N次方-- 精确四舍五入到两位的替代方案 SELECT CAST(3.145 * 100 0.5 AS INTEGER) / 100.0;这招不完全通用但对金额计算、统计人数这类场景比直接靠ROUND()靠谱得多。3.2 类型转换CAST与存储类函数SQLite是动态类型数据库但每个值都有五种存储类之一NULL、INTEGER、REAL、TEXT、BLOB。函数typeof(X)返回值的存储类调试时特别好用。比如从CSV导入的手机号被读成数值时会丢失前导零加一个检查SELECT phone, typeof(phone) FROM users WHERE typeof(phone) real;就能快速定位问题数据。类型转换的核心函数是CAST(X AS type)type可以是INTEGER、REAL、TEXT、BLOB或NUMERIC。CAST(123 AS INTEGER)得到整数123CAST(3.99 AS INTEGER)得到3CAST(2024-01-15 AS INTEGER)得不到时间戳会得到0因为SQLite不会智能解析日期文本为整数。日期转换要用专门的日期函数不能靠CAST。另一个容易忽略的是CAST(abc AS INTEGER)不报错而是返回0。这个行为在很多数据库里不可想象但SQLite为了宽松处理隐式转换故意这么设计的。判断字段是否为数字可以用CAST(x AS REAL)后再比较SELECT * FROM logs WHERE CAST(level AS INTEGER) 0 AND level ! 0;再加一个printf函数说一下它承担了SQLite的格式化输出职责类似C语言的sprintf。printf(%04d-%02d-%02d, 2024, 3, 5)返回“2024-03-05”补零功能在生成批次号、文件名时非常好用。4. 日期时间函数SQLite里最“反直觉”的一页4.1 没有DATE类型一切都是TEXT/INTEGER初用SQLite的人都会问一个问题为什么SELECT DATE(2024-01-15)能执行但建表时CREATE TABLE t (d DATE)也不会报错原因在于SQLite根本没有原生的日期类型。日期可以用TEXT存储“YYYY-MM-DD”格式可以用INTEGER存Unix时间戳甚至用REAL存儒略日。所谓DATE类型在建表时会被当成NUMERIC亲和性实际存储啥类型都行。这种设计很灵活但也意味着你必须自己约定存储格式否则同一列里可能混着“2024-01-15”和时间戳排序、比较就会乱套。SQLite支持五种时间字符串格式YYYY-MM-DD、YYYY-MM-DD HH:MM、YYYY-MM-DD HH:MM:SS、YYYY-MM-DD HH:MM:SS.SSS和带时区的YYYY-MM-DDTHH:MM。前四种时间格式的“T”分隔符也是合法的2024-01-15T10:30:00能被正确解析。写代码时最好统一用“YYYY-MM-DD HH:MM:SS.SSS”可读性强也能直接按字典序排序这是SQLite日期存储的最大优点文本格式就是有序的ORDER BY date_text就是时间排序。4.2 strftime、datetime、julianday的应用套路SQLite的日期时间函数核心是五个date()、time()、datetime()、julianday()和strftime()。其中前四个都是strftime()的语法糖真正强大的是strftime(格式, 时间值, 修饰符...)支持的时间格式符包括%Y、%m、%d、%H、%M、%S、%j年中的第几天、%w星期几0表示周日、%W年中的第几周等等。时间值后面可以接修饰符这是SQLite日期函数最灵活的机制。datetime(now, 1 day)返回明天的当前时刻date(now, -3 days)返回昨天之前的日期date(now, start of month)返回当月第一天。修饰符还能叠加使用date(2024-02-29, 1 year)在绝大部分版本里返回2024-02-28因为2025年没有2月29日处理闰日时会自动回退。实际开发中我经常用这一招算“上个月第一天”和“下个月最后一天”-- 上个月的第一天 SELECT date(now, start of month, -1 month); -- 下个月的最后一天 SELECT date(now, start of month, 1 month, -1 day);julianday()返回儒略日天数两个日期的差就是julianday(d1) - julianday(d2)精确到天、小时、分钟甚至秒。计算两个时间点相差多少秒标准写法是SELECT CAST((julianday(2024-03-01 12:00:00) - julianday(2024-03-01 10:00:00)) * 86400 AS INTEGER);julianday计算结果是带小数的天数乘以86400再取整就是秒数。这个公式在统计在线时长、视频播放进度这类场景里几乎是必用的。如果你需要计算“今天是星期几”strftime(%w, now)返回0到60是周日strftime(%u, now)返回1到71是周一。前端显示时要注意两者语义差异。注意datetime(now)返回的是UTC时间不是本地时间。如果业务时间要按北京时间展示必须写datetime(now, localtime)。这个坑在夏天和冬天表现还不一样一些海外部署的服务器如果不加localtime日志时间会和本地时间差出好几个小时。5. 聚合、排序与条件逻辑函数的组合拳5.1 聚合函数与GROUP BY评测场景聚合函数是SQLite报表统计的核心。COUNT(*)统计行数COUNT(列名)统计该列非NULL值个数COUNT(DISTINCT 列名)统计去重后的个数。SUM(列)、AVG(列)自动忽略NULL但如果所有值都是NULLSUM()返回NULL而AVG()返回NULL这点和MySQL一致。TOTAL(列)也是求和但它永远返回浮点数全NULL时返回0.0不会返回NULL。如果业务上希望“没有数据时显示0”用TOTAL()或COALESCE(SUM(列), 0)都行。GROUP_CONCAT(value, separator)是SQLite比较有特色的聚合函数能把分组内的多个值拼接成一个字符串默认分隔符是逗号。比如把某个用户的全部标签拼起来SELECT user_id, GROUP_CONCAT(tag, |) AS tags FROM user_tags GROUP BY user_id;如果GROUP_CONCAT里没有指定字段顺序拼接顺序是不确定的。想要确定顺序SQLite 3.44.0开始支持在聚合函数里加ORDER BY子句GROUP_CONCAT(tag ORDER BY created_at DESC, |)。如果SQLite版本比较老可以先在子查询里排好序再聚合否则拼接结果每次查询可能都不一样。聚合时最常犯的错误是把非聚合列直接放进SELECT比如-- 这种SQL在MySQL严格模式下会报错在SQLite里却能运行 SELECT user_id, name, SUM(amount) FROM orders GROUP BY user_id;SQLite会返回这个分组第一条记录对应的name但这个“第一条”不是确定性的运行结果可能让人摸不着头脑。如果希望每个分组里取特定记录的值比如最近订单的客户名要借助窗口函数或者先排序再聚合。SQLite从3.25.0开始支持窗口函数比如SELECT user_id, name, amount FROM ( SELECT user_id, name, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM orders ) WHERE rn 1;5.2 条件处理CASE WHEN、IFNULL、COALESCESQLite的条件逻辑函数知识点不多但用法非常高频。IFNULL(a, b)是空值兜底如果a是NULL返回b否则返回a。注意IFNULL只能接两个参数三个及以上参数要交给COALESCE(a, b, c, ...)它从左到右返回第一个非NULL值。NULLIF(a, b)则相反当a和b相等时返回NULL否则返回a。这个函数在防除零时非常有用比如计算两个字段的比率SELECT total, amount, total / NULLIF(amount, 0) AS ratio FROM orders;CASE WHEN是SQLite条件逻辑的核心支持CASE表达式两种写法表-- 简单写法匹配精确值 SELECT CASE status WHEN 1 THEN 待支付 WHEN 2 THEN 已支付 ELSE 已取消 END FROM orders; -- 搜索写法支持范围条件 SELECT CASE WHEN amount 1000 THEN 大单 WHEN amount 100 THEN 中单 ELSE 小单 END FROM orders;IIF(条件, 真值, 假值)适合最简单的二选一逻辑。注意IIF第三个参数是必需的没有“缺省默认”的写法不能像IFNULL那样简写。遇到“空串和NULL都算缺省”的场景最稳妥的写法是NULLIF(TRIM(字段), )先统一成NULL再套COALESCESELECT COALESCE(NULLIF(TRIM(remark), ), 无备注) FROM orders;这套组合拳在清洗脏数据时几乎是必用的。6. 十万条数据的函数查询真实体验6.1 无索引、有索引与函数表达式的性能差异热搜里有个问题特别常见SQLite查询十万条数据需要多久我直接说结论单纯查十万行SQLite基本可以做到几十毫秒内返回如果是带条件的聚合查询关键看索引和函数是否阻挡了索引使用。拿一张典型订单表举例表结构如下CREATE TABLE orders ( id INTEGER PRIMARY KEY, user_id INTEGER, amount REAL, created_at TEXT );插入十万条测试数据后SELECT COUNT(*) FROM orders;的耗时通常低于20毫秒这得益于SQLite在扫描时做了页面级的遍历优化。SELECT * FROM orders WHERE user_id 123;如果user_id没有索引扫描十万行大约需要30到80毫秒具体看磁盘速度。一旦加上CREATE INDEX idx_user ON orders(user_id);同理条件的查询时间会降到1毫秒以内肉眼感知不出来。但函数会影响索引命中。比如WHERE user_id * 2 246这种写法哪怕user_id上建了索引也白搭因为表达式计算后无法直接匹配索引。写成WHERE user_id 246 / 2才能用上索引。同样对文本列做WHERE lower(email) abcexample.com如果email列有普通索引优化器也不会用因为索引存的是原始大小写。要提升性能建一个表达式索引CREATE INDEX idx_email_lower ON users(lower(email));SQLite是支持表达式索引的三元一局。十万条数据的聚合查询比如SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;如果没有索引耗时在100毫秒上下勉强够用。如果这个查询要高频执行建上(user_id, amount)的复合索引后可以走覆盖索引直接把索引当数据表用减少回表性能往往能提升一半以上。SQLite官方文档建议写复杂报表SQL前先用EXPLAIN QUERY PLAN看看执行计划确认是否USING INDEX避免“看起来SQL没变但数据涨了之后突然变慢”。我用一个简单表格总结了不同条件下的实测参考值沙盒环境SSD十万行订单表仅供参考查询场景是否有索引参考耗时SELECT COUNT(*) FROM orders无需索引10-20msSELECT * FROM orders WHERE user_id 123无30-80msSELECT * FROM orders WHERE user_id 123有1msSELECT user_id, SUM(amount) FROM orders GROUP BY user_id无80-150ms同上覆盖索引有20-40ms十万行全表排序ORDER BY amount DESC无60-120ms注意SQLite的写入性能也是很多人关心的十万行INSERT批量插入如果不开启事务每行都是一次独立提交可能要好几秒用事务包起来单条INSERT或者INSERT INTO ... VALUES批量插入十万行能压到三五百毫秒级别。这也是用SQLite做本地数据落盘时最值得优化的点。6.2 MySQL迁移到SQLite时函数兼容自查清单从MySQL转SQLite函数不兼容是重灾区。我在实际迁移项目里几乎每次都要改一批SQL这里整理一份常用函数对照表功能MySQL写法SQLite写法当前日期CURDATE()DATE(now)当前时间时间戳NOW()DATETIME(now, localtime)字符串拼接CONCAT(a, b)a || b取字符串长度字符数CHAR_LENGTH(s)LENGTH(s)空值兜底IFNULL(a, b)IFNULL(a, b)同名多参数兜底COALESCE(a, b, c)COALESCE(a, b, c)同名条件分支IF(条件, 真, 假)IIF(条件, 真, 假)或CASE WHEN日期加减DATE_ADD(d, INTERVAL 1 DAY)DATE(d, 1 day)两个日期差天数DATEDIFF(d1, d2)JULIANDAY(d1) - JULIANDAY(d2)分组拼接GROUP_CONCAT(s)GROUP_CONCAT(s)同名但排序语法不同模糊匹配不区分大小写LIKE默认忽略大小写取决于排序规则LIKE只忽略ASCII大小写迁移时还要留意两个MySQL常用但SQLite没有的函数SUBSTRING_INDEX()和DATE_FORMAT()。SUBSTRING_INDEX(a,b,c, ,, 2)在SQLite里没有直接对应物可以用substr配合instr实现或者写自定义函数。DATE_FORMAT()在SQLite里对应strftime(%Y-%m-%d %H:%i:%s, 时间值)但格式化符号差别不小MySQL的%i表示分钟SQLite的%M是月份、%m是月份数字、%S是秒、%H是24小时制迁移时有些字符还得批量替换。建议写迁移SQL时先做一轮“函数体检”把不兼容的地方单独挑出来映射。7. SQLite常用函数的坑修正字段类型与工具推荐7.1 修改字段类型的三种办法热搜里“sqlite修改字段的类型”也是个高频词。SQLite对ALTER TABLE的支持非常有限只支持加列、改表名不支持直接改列的数据类型。但实际上SQLite的“类型”不是强制的列主要起“亲和性”作用。想真正改一个字段的类型可以直接用重建表的方式。假设把orders.amount从TEXT改成REAL-- 第一步新建临时表 CREATE TABLE orders_new ( id INTEGER PRIMARY KEY, user_id INTEGER, amount REAL, created_at TEXT ); -- 第二步复制数据 INSERT INTO orders_new (id, user_id, amount, created_at) SELECT id, user_id, CAST(amount AS REAL), created_at FROM orders; -- 第三步删除旧表并改名 DROP TABLE orders; ALTER TABLE orders_new RENAME TO orders;注意这种方式在数据量大时要注意业务写入窗口最好在低峰时段执行或者提前停机维护。SQLite官方文档建议用BEGIN TRANSACTION包裹重建过程保证中途出错可以回滚。还要留意外键约束如果orders被其他表引用直接DROP TABLE会破坏外键关系需要先把引用表一并处理。重建完记得重新执行ANALYZE更新统计信息让查询优化器重新评估索引使用。如果只是想利用SQLite的动态类型不改建表也可以直接更新某一行把任意类型塞进任意列。SQLite唯一表达“我想要这个类型”的就是建表时的类型声明和亲和性转换但实际存的数据类型不会被限制。这也是改类型问题区别于其他数据库的最大不同点有时候不是改表结构而是改数据本身。7.2 工具推荐与常用调试技巧光靠命令行写SQL测试函数结果其实不太方便。我平时最常用的是DB Browser for SQLite免费开源Windows/macOS/Linux都有安装包。它最大的优点是左侧直接展示数据库表结构和数据预览中间区域写SQL并执行结果以表格形式返回适合验证SQLite函数行为。调试strftime和julianday这类时间函数时直接写上SQL跑一遍就能看到结果比在命令行里用.headers on、.mode column手调输出格式快得多。命令行方面Linux下装SQLite用系统包管理器就行Debian/Ubuntu用apt install sqlite3Rocky Linux/CentOS用dnf install sqlite。装完可以在sqlite3命令行里用.help查看全部点命令.schema查看表结构.timer on打开查询耗时显示这是在命令行里评估函数查询性能的利器。如果在C#项目里用SQLite常用的驱动是Microsoft.Data.Sqlite。它有几个基于SQLite函数的注意事项参数化查询里的NULL处理、DateTime到SQLite文本的转换格式默认是yyyy-MM-dd HH:mm:ss、以及布尔值存储为0/1的问题。用db.ExecuteScalar(SELECT strftime(%Y, now, localtime))拿到年份时要记得Convert.ToInt32做显式转换因为驱动有时会返回的类型不够直观。C#开发时如果遇到SQLite函数在单元测试里跑不出预期结果先确认你引用的是内置SQLite版本还是系统SQLite版本版本差异也可能会导致函数行为不一致。个人实操中的几点体会通读了SQLite这些常用函数之后最大的感受是SQLite的函数体系其实比很多人想象得完整但它独特的设计逻辑要求开发者主动适应。最典型的三个点日期时间没有原生类型而是靠文本和时间函数组合类型系统是动态的函数结果有时会“出乎意料”地宽松很多函数行为和MySQL等数据库有明显差异迁移前必须做一轮函数兼容自查。我在实际项目里踩过最多坑的就是ROUND()的舍入逻辑和datetime(now)的UTC时区这两处都建议在项目初始化时就在技术文档里标注出来后面接手的同事就不会再踩一遍。还有个经验是要经常用typeof()检查导入数据的实际存储类型很多时候业务表现异常不是SQL写错了而是数据从一开始就存成了错误类型。SQLite函数虽多但高频用到的不超过三十个把这三十个的边界行为和兼容性摸透日常开发和数据迁移都会顺畅很多。