做开发这些年SQL函数说简单也简单说复杂也真能让人栽跟头。我见过不少同事写了好几年SQL常用的函数就那么五六个一碰到“分组取每组最新一条”“解析JSON字段”“统计连续登录天数”就卡壳跑去搜索搜出来的答案不是语法不对就是数据库版本不符越看越乱。这篇文章是我的SQL函数学习笔记也是踩坑记录核心是把SQL函数的底层逻辑、高频用法、进阶技巧和优化思路一次讲透。如果你刚入门建议按章节顺着看如果你已经有基础直接跳到第3章、第4章和第5章我猜多少能帮你补上几个盲区。全文案例以SQL Server和MySQL为主语法差异我会明确标注通用场景尽量用标准SQL。先说一句最重要的认知前提SQL函数不是程序语言里的函数别拿Python、JavaScript那套思路来套不然后面全是坑。接下来我按学习顺序一篇篇拆给你看。1. SQL函数学习前的三个底层认知1.1 SQL函数不是程序语言里的函数很多人把SQL函数和Java、Python、JavaScript里的函数画等号这是第一个认知障碍。程序语言里的函数是一段可复用逻辑定义参数、执行过程、返回结果你可以把它当作对象传来传去JS的函数本身就是对象可以用箭头函数简化写法可以定义回调函数。SQL函数表面上也用括号传参数、也有返回值但底层逻辑完全不同。SQL是声明式语言你写SELECT COUNT(*) FROM orders并不是“调用了一个循环计数的函数”而是向数据库声明“我要订单总数”数据库自己决定扫表还是走索引。SQL函数输入的是行或集合输出的往往是标量、聚合值或经过变换的结果集它不是一段你能控制的流程代码。如果你是从Python的def、JavaScript的箭头函数、C的函数重载那边过来的最需要放弃的就是“控制执行顺序”的念头。SQL里的函数更像Excel里的公式放在表达式里对整列数据做变换UPPER(name)不是让你对“一个变量”调用方法而是对这一行的这个字段做大小写转换SUM(amount)则是对分组内所有行的字段做聚合。明白“输入集合、输出结果”这一点再学窗口函数和JSON函数时就不会觉得它们有什么神秘。1.2 先建立函数分类框架别急着背函数名背函数名是学习SQL函数效率最低的方式没有之一。我的建议是先搭一个分类框架把所有函数按职责归档用到哪类去哪类找。总体可以分为七类字符串函数处理文本重点掌握CONCAT、SUBSTRING、CHARINDEX/LOCATE、REPLACE、LEN/LENGTH、TRIM。数值函数ROUND、CEILING/CEIL、FLOOR、ABS、POWER、MOD注意负数边界和取整方向。日期时间函数GETDATE/NOW、DATEADD/DATE_ADD、DATEDIFF、DATEPART/EXTRACT、FORMAT/DATE_FORMAT跨库差异最大的就是这一类。转换与判空函数CAST/CONVERT、ISNULL/COALESCE、NULLIF处理NULL时优先想清楚行为细节。聚合函数SUM、COUNT、AVG、MIN、MAX别忘了COUNT(DISTINCT col)这种去重计数。窗口函数ROW_NUMBER、RANK、DENSE_RANK、NTILE、LAG、LEAD、SUM() OVER(...)这是进阶分水岭。系统与元数据函数查数据库版本、当前用户、对象ID等不同数据库差异太大现查现用即可。这个框架的价值有两层。第一层是检索路径遇到“排序后取前几名”想窗口函数遇到“文本提取”想字符串函数而不是从头翻文档。第二层是把发散的零散函数收敛成知识树每个分类只要吃透五六个代表函数就能覆盖绝大多数业务。我这些年维护笔记从来不按字母背函数而是按业务场景记用法这样记得住也用得上。1.3 SELECT执行顺序函数报错的一大来源讲函数之前必须讲执行顺序因为太多函数报错根本不是函数本身的问题而是用错了位置。标准SQL的逻辑执行顺序大致是FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT/OFFSET。这个顺序意味着什么WHERE阶段还不能引用SELECT里生成的别名因为别名在SELECT阶段才算出来WHERE也不能直接写聚合函数条件COUNT(*) 1这种判断得放到HAVING里窗口函数在SELECT投影阶段计算所以想在WHERE里过滤窗口函数结果必须包一层子查询。很多新人写SELECT name, COUNT(*) AS cnt FROM t WHERE cnt 1数据库报“列名cnt不存在”原因就是WHERE执行时cnt根本不存在。理解了执行顺序这类报错一眼就能看出来。执行顺序还解释了为什么ORDER BY可以引用SELECT别名而WHERE不行为什么GROUP BY之后的SELECT项只能是分组列或聚合函数。这一套规则是SQL函数使用的大前提任何函数学起来都绕不开它。2. 高频必用的基础函数从会用到底层逻辑2.1 字符串函数提取、清洗、拼接的套路字符串函数是日常使用频率最高的也是最容易通过组合玩出花样的。第一个高频场景是“按分隔符截取片段”核心套路是用CHARINDEX先定位分隔符位置再用SUBSTRING截取。举一个例子订单号格式是ORD-20240101-001想取最后的三位序列号写成SUBSTRING(order_no, CHARINDEX(-, order_no, CHARINDEX(-, order_no) 1) 1, 10)。这个写法初见有点吓人拆开看就是先找第一个横杠的位置再从他后面找第二个横杠最后从第二个横杠后面开始取长度10的字符串。注意CHARINDEX在SQL Server里参数顺序是CHARINDEX(substr, str)MySQL里对应的LOCATE也是LOCATE(substr, str)方向一致但如果你在MySQL里用了INSTR(str, substr)参数顺序是反的这个细节不知道坑了多少跨库开发。第二个场景是清洗脏数据最朴素也最有效的做法是嵌套REPLACE。比如电话号码字段里有空格也有短横线写成REPLACE(REPLACE(phone, , ), -, )。要记住REPLACE只做精确子串替换不做正则匹配真要做正则SQL Server没有内置正则函数MySQL 8.0可以用REGEXP_REPLACE但大部分简单清洗场景用几层REPLACE加TRIM就够了没必要引入正则的复杂度。第三个场景是字符串拼接这里的坑非常典型。SQL Server用拼接时只要参与拼接的任一字符串是NULL整个结果就是NULL这是报表字段“神秘消失”的头号凶手SQL Server 2012之后提供的CONCAT函数会把NULL当作空串处理行为不一样。而MySQL的CONCAT遇到NULL参数返回NULL不是跳过只有CONCAT_WS会跳过NULL值。我把这三者行为整理成了一张对比表建议存一下。写法SQL Server行为MySQL行为a NULL返回NULL按数值运算NULL参与返回NULLCONCAT(a, NULL)返回a2012返回NULLCONCAT_WS(,, a, NULL)SQL Server无此函数返回a跳过NULL跨库写拼接逻辑时先把这张表刻在脑子里。别靠记忆写个一次性验证脚本跑一遍再上线比什么都稳。2.2 日期与数值函数同名不同义的坑日期时间函数是跨库差异的重灾区最典型的就是DATEDIFF。SQL Server的DATEDIFF支持年、月、日、小时等多种粒度但它计算的是“边界跨越次数”不是精确的时间差。举例说DATEDIFF(YEAR, 2022-12-31, 2023-01-01)返回1因为跨了一个年的边界哪怕实际只差了1秒。这也是为什么很多人用DATEDIFF(YEAR, birthdate, GETDATE())算年龄会得出偏大的结果——它根本不管生日过没过。MySQL的DATEDIFF不同它只支持按天且只取日期部分相减要做精确的年差、月差得用TIMESTAMPDIFF(YEAR, birthdate, CURDATE())。同一个函数名两种用法初学者特别容易踩。数值函数里比较隐蔽的坑在ROUND。SQL Server的ROUND(x, n)默认四舍五入但它还有第三个参数传非0值表示“截断”不做四舍五入。比如ROUND(3.14159, 2, 0)返回3.14正常但ROUND(3.14159, 2, 1)也返回3.14它是直接截断不是舍入这类细节文档里有但很多人没注意到。另外ROUND对浮点类型做舍入时可能受到浮点表示误差影响做金额计算尽量先把数据转成DECIMAL再谈取整。CEILING和FLOOR的取整方向也要注意负数场景CEILING(-1.2)返回-1因为它向正无穷方向取整很多人第一次写负数用例就被绕晕。写数值相关的单元测试时把负数、0、边界值都覆盖到能省下不少排查时间。2.3 去重到底怎么选DISTINCT、GROUP BY、ROW_NUMBER“SQL语句去重”是搜索频率极高的问题说明很多人都被它缠过。先给结论想对查询结果整体去重用SELECT DISTINCT想按某几列分组并顺带做聚合统计用GROUP BY想“每个分组保留某一条完整记录”用窗口函数ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) 1。三者的定位完全不同。DISTINCT是对所有返回列做组合去重NULL也被当作一个值参与比较它不会做任何聚合返回的每一行都是原始行的一种组合。GROUP BY会把多行合并成一行SELECT子句里除了分组键只能出现聚合函数它天然保证每组一行但你拿不到组内其他非分组字段的完整值。ROW_NUMBER不减少行数它给每一行编一个窗口内序号然后再由外层查询过滤这样既能保留原始行所有字段又能做到“每组取一条”。典型面试题“每个用户最近一笔订单”的标准解法SELECT * FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY create_time DESC) rn FROM orders ) t WHERE rn 1;把这个写法吃透去重类需求基本就通关了。3. 窗口函数进阶分水岭面试常客3.1 窗口函数的工作机制窗口函数是普通SQL和进阶SQL的分界线。和GROUP BY最本质的区别是GROUP BY会把多行合并成一行窗口函数则是在不减少行数的前提下对每一行计算一个基于窗口分组的结果。打个比方GROUP BY像把所有学生按班级分组然后只给每个班交一张汇总成绩单窗口函数则是给每个学生的成绩单旁边都附上一列“全班平均分”你既能看自己的分又能看自己跟平均分的差距。窗口函数的语法核心是OVER()子句里面有三件套PARTITION BY负责分组ORDER BY负责窗口内排序ROWS/RANGE负责框定具体的窗口范围。很多初学者把窗口函数当成普通的聚合函数区别就在于有没有OVER()SUM(amount)是普通聚合合并行SUM(amount) OVER(PARTITION BY user_id)是窗口聚合保留每一行。版本上提醒一句MySQL从8.0开始才支持窗口函数SQL Server从2012开始支持如果你的生产库是MySQL 5.7及以下这一整套都用不了。3.2 三类高频窗口函数第一类是排名函数三兄弟ROW_NUMBER、RANK、DENSE_RANK。它们都在分组内按排序字段编号区别是并列时怎么处理。ROW_NUMBER直接给连续序号1、2、3、4哪怕分数一样也要分个先后RANK遇到并列会把后面的排名跳过比如并列第一占了两个名额下一个就是3DENSE_RANK不跳号并列第一之后还是2。做唯一行号用ROW_NUMBER做竞赛排名用RANK做连续排名层级用DENSE_RANK面试官问区别也就是考这个。函数数据分数结果排名ROW_NUMBER100, 90, 90, 801, 2, 3, 4RANK100, 90, 90, 801, 2, 2, 4DENSE_RANK100, 90, 90, 801, 2, 2, 3第二类是偏移函数LAG和LEAD分别取当前行前面一行或后面一行的某个字段。最经典的应用是算环比比如本月销售额对比上月LAG(amount, 1) OVER(ORDER BY month)就能拿到上个月的数然后直接相减算增长率。注意第二个参数是偏移量默认1第三个参数是找不到时的默认值写成LAG(amount, 1, 0)可以避免第一行返回NULL导致后续计算出错。第三类是聚合函数配窗口最常用的就是累计求和SUM(amount) OVER(ORDER BY month)会从第一行累加到当前行这个写法在财务报表里特别常见。3.3 实战分组TopN、累计求和、连续登录分组TopN的固定套路已经在上文出现过内层用窗口函数编号外层包一层子查询过滤。如果允许名次并列把ROW_NUMBER换成DENSE_RANK如果只要前三名过滤条件改成WHERE rn 3。累计求和要注意分组边界。假设按年统计每个月累计销售额写法是SELECT year, month, amount, SUM(amount) OVER(PARTITION BY year ORDER BY month) AS monthly_cumulative FROM sales;这里PARTITION BY year让每一年的累计值独立ORDER BY month保证从一月开始累加。漏掉PARTITION BY会把不同年份的销售额混在一起累计数据直接算错。连续登录天数是一道经典面试题解法非常巧妙先给每个用户的登录日期按时间排序编号再用登录日期减去编号得到一个“组标识日期”。因为连续日期的序号差值恒定同一组的天数一定是连续的。完整思路是先用ROW_NUMBER生成排序号再用DATEADD把日期和序号相减最后按用户和组标识分组统计数量。这个解法依赖窗口函数和日期函数配合使用千万别把窗口函数当独立知识点它要和子查询、日期计算串起来才能解决真实问题。4. JSON查询函数与高级函数现代SQL的必修课4.1 JSONValue还是JSON_QUERY现在很多业务表直接存JSON字符串SQL Server从2016开始支持原生JSON函数MySQL从5.7开始支持JSON类型和JSON函数。SQL Server里最常用的两个是JSON_VALUE和JSON_QUERY它们的区别一句话就能说清JSON_VALUE取标量值JSON_QUERY取JSON对象或数组。取了对象却用JSON_VALUE会返回NULL因为对象不是标量。这个坑特别隐蔽我帮人排查过好几次最后发现都是函数选错不是数据有问题。举个例子字段profile里存的是{name:张三,address:{city:上海},tags:[sql,db]}。取姓名用JSON_VALUE(profile, $.name)取address整个对象用JSON_QUERY(profile, $.address)取tags数组也用JSON_QUERY但如果你想取tags数组的第一个元素那就又回到标量了用JSON_VALUE(profile, $.tags[0])。路径语法以$开头数组下标从0开始这条规则在SQL Server和MySQL里都一样。MySQL的等价写法是JSON_EXTRACT配合JSON_UNQUOTE也可以用简化的-和-运算符比如profile-$.name返回带引号的JSON字符串profile-$.name直接返回文本。如果你把JSON字段当普通字符串用LIKE去模糊匹配数据量一大性能就崩能用JSON函数就别用字符串函数硬扛。4.2 空值函数ISNULL、COALESCE、NULLIF的配合空值处理是SQL函数里最容易被轻视的一块。COALESCE(col1, col2, 默认值)返回第一个非NULL值参数可以多个ISNULL是SQL Server的简化版只接受两个参数。两者最大的区别是类型处理ISNULL会按左边参数的数据类型做隐式转换COALESCE按所有参数里的最高优先级类型决定结果类型。比如ISNULL(price, 0)里的price是decimal0会被转成decimalCOALESCE(price, 0)如果price是varchar结果就可能变成varchar。跨库时这个差异经常导致报表字段类型不对排错时记得先看类型。NULLIF(a, b)在a等于b时返回NULL最经典的用途是防除零。比如计算客单价SUM(amount) / NULLIF(COUNT(*), 0)当订单数为0时结果是NULL而不是报除零错误外面再包一层COALESCE(..., 0)就能输出0或提示文案。这三个函数单独看都简单组合使用时顺序会影响最终结果建议写查询时专门用一个包含NULL和0的样本数据做验证把NULL分支和类型分支都测一遍再上线。4.3 字符串聚合STRING_AGG与FOR XML PATH把多行字符串拼成一行这个需求平时看着冷门做报表和接口导出时却非常香。SQL Server 2017之后可以直接用STRING_AGG(name, ,)还支持WITHIN GROUP (ORDER BY id)指定拼接顺序。老版本没有这个函数大家只能用FOR XML PATH()的写法SELECT STUFF( (SELECT , name FROM users ORDER BY id FOR XML PATH()), 1, 1, );这写法晦涩难懂里面FOR XML PATH()把结果转成XML字符串STUFF再去掉第一个逗号。MySQL则用GROUP_CONCAT注意GROUP_CONCAT默认有长度限制受group_concat_max_len参数控制超长会自动截断不会报错——我在这上面吃过亏导出标签时发现少了一截查了半天才发现是参数限制。如果拼接的字段可能很长先SET SESSION group_concat_max_len 102400;再查询。字符串聚合可以和窗口函数配合做到“每个分组只拼本组”这类组合用法才是真正拉开效率差距的地方。5. 慢SQL优化实战函数是索引杀手5.1 WHERE条件里用函数索引为什么不走SQL函数放在SELECT列表里顶多是多消耗一点CPU放在WHERE条件里对列套函数往往就是索引失效的头号原因。比如WHERE UPPER(name) TOM数据库必须先把这一列所有值都算一遍大写转换才能拿去和TOM比较没法直接走name字段上的索引。同理WHERE DATE(order_time) 2024-01-01也会让时间索引失效因为数据库要对每一行做日期转换。正确写法是改范围条件WHERE order_time 2024-01-01 00:00:00 AND order_time 2024-01-02 00:00:00这样既保住索引语义也没有任何变化。还有一个隐性杀手是隐式类型转换字符串列和数字常量比较时SQL Server有时会把列转成数值再比较一样杀索引。我排查慢SQL时第一件事就是看条件里有没有对字段套函数或发生隐式转换这一步成本最低命中率却高得吓人。5.2 慢SQL排查执行计划、等待类型、并行优化慢SQL排查的标准动作是先抓执行计划看有没有Table Scan或Clustered Index Scan有没有超出预期的Key Lookup再看表的统计信息是否过期、索引缺失提示是什么最后看等待类型。SQL Server的等待类型里WRITELOG代表日志文件写入等待出现频率高通常意味着日志文件所在磁盘太慢或者事务过于频繁且单事务写入量大PAGEIOLATCH是数据页物理IO等待多半和索引缺失或内存压力有关CXPACKET是并行等待很多人一看到就想上并行优化但实际经验是过度并行经常让CPU打满而查询没变快。这时候要做的不是加并行度而是用MAXDOP限制并行或者提高并行阈值。另外CTE和子查询的写法差异也影响性能。CTE在部分情况下会被重复物化如果你在一个SQL里放了大结果集的CTE还反复去JOIN它性能大概率好不了。遇到这类写法先试着把CTE改成临时表或派生表看看执行计划是否有变化。慢SQL优化几乎是后端和数据岗位的必考内容掌握“函数拆索引”和“看等待类型”这两招比背一堆索引规则有用得多。5.3 一次真实慢查询的修正过程给你看一个我实际处理过的报表查询。原本的写法是SELECT customer_id, SUM(amount) FROM orders WHERE YEAR(create_date) 2024 AND MONTH(create_date) 1 GROUP BY customer_id;这个查询在百万级数据量的表上跑了6秒多。执行计划里全是表扫描原因就是YEAR和MONTH函数包在了索引列上。改成SELECT customer_id, SUM(amount) FROM orders WHERE create_date 2024-01-01 AND create_date 2024-02-01 GROUP BY customer_id;再配合一个(create_date, customer_id, amount)的复合索引查询直接降到300毫秒。整个优化过程SQL函数没换几个只是把“函数判断”改成“范围判断”。这件事我在笔记里记了特别大的注释SQL函数是用来算结果的不是用来包在索引列上做条件的。你手头如果有慢SQL先按这个思路过一遍多半能白捡几十倍的性能提升。6. 常见报错与排查速查表新人必存6.1 SQL Server安装部署与密码到期关于SQL Server的安装和授权我先给一条态度明确的建议不要去找所谓的“激活码”或者来路不明的密钥风险不只是版权问题很多所谓破解工具都捆绑了恶意代码。正规的做法是从微软官网获取Express版或Developer版Express免费且足够学习Developer版免费但仅限开发和测试用途生产环境按正规授权流程走。安装报错时最常见原因是权限不足、防火墙拦截或之前安装残留。先看安装日志通常位于C:\Program Files\Microsoft SQL Server\下的Setup Bootstrap\Log目录搜索“Failure”字段比百度靠谱得多。装完之后顺手用s sqlcmd验证连接sqlcmd -S localhost -U sa -P your_password -Q SELECT VERSION能通就说明基本没问题。“SQL Server密码到期”是另一个高频问题。SQL Server登录名默认没有过期策略但如果你在Windows服务器安全策略里开了密码过期或者有人设置了MUST_CHANGE就会出现连不上、提示密码已过期的状况。解决办法是用Windows身份或sa登录执行ALTER LOGIN your_login WITH PASSWORD new_password; ALTER LOGIN your_login WITH CHECK_EXPIRATION OFF, CHECK_POLICY OFF;学习环境直接关掉密码过期策略省心生产环境交给统一密码策略管理但绝对不要在代码里硬编码账号密码。6.2 调试时SQL长度大于复制长度与SSMS截断“sql长度大于复制长度”这个报错字面意思容易让人困惑实际是SSMS里查询结果和复制行为对文本长度有限制。默认情况下SSMS的“结果到网格”最大检索字符数是65535超过部分会被静默截断不会报错“结果到文本”的每列显示最大字符数默认是256你复制一段长文本可能只复制了前256个字符。数据核对时最容易中招看起来字段是全的实际粘出来的只是截断版本。解决办法在菜单“工具—选项—查询结果—SQL Server”里结果到网格的“最大检索字符数”调大到8192或更大结果到文本的“每列显示的最大字符数”一样调大。这个坑不报错只静默截断排查起来很费劲提前设置好能省很多事。另外SSMS里调试超长SQL脚本偶尔也会卡顿多半是编辑器自身性能问题把脚本分段执行比硬扛一整段更靠谱。6.3 与函数无关但总混在一起的命令行报错开发数据库环境时经常遇到一类跟SQL完全不沾边的报错“pnpm : 无法将‘pnpm’项识别为 cmdlet、函数、脚本文件或可运行程序的名称”make命令同理。出现原因基本都是环境变量PATH里没有Node.js或GNU工具的路径或者安装后没重开终端。解决办法先确认命令的实际位置Windows用where pnpmLinux/macOS用which make再把对应文件夹加入PATH。VS Code里C函数和变量无法跳转则多半是IntelliSense配置问题跟语言本身无关。遇到报错先分清来源再动手别把环境问题当成SQL问题一路排查到底。报错现象常见原因解决动作pnpm/make不是cmdletPATH未配置或终端未重启配置环境变量重开终端SQL Server密码过期Windows策略或MUST_CHANGEALTER LOGIN关闭过期检查SSMS复制长文本被截断网格结果默认限制调大最大检索字符数SSMS报“sql长度大于复制长度”结果到文本列长度限制调大每列最大字符数SQL安装失败权限/残留/防火墙看Setup Bootstrap日志7. SQL注入防御视角函数在安全里的正确用法7.1 拼接SQL为什么是大忌SQL注入是最常见的Web漏洞之一核心原因是开发者在拼接SQL时把用户输入当成了代码执行。比如登录查询用字符串拼接用户输入攻击者在输入框里放一段能被拼接执行的恶意字符串登录逻辑就可能被改写。CTF比赛SWPUCTF新生赛、ctfshow这类经常拿SQL注入出题目的是让你站在攻击者视角理解漏洞的形成原理从而写出更安全的代码。我这里不会展开任何绕过语句只讲防御纪律学习注入原理是为了防不是为了攻。7.2 参数化查询才是正解防御SQL注入最有效的方式就一条参数化查询/预编译语句永远不要用字符串拼接SQL。在C#的ADO.NET里用SqlParameter在Java的MyBatis里用#{}绑定参数而不是${}拼接表名或字段。函数在这个场景里是配角可以对入参做白名单校验、去除首尾空格、限制长度但绝不能拿“过滤危险字符”来替代参数化因为过滤总有遗漏。存储过程里的动态SQL如果万不得已要拼接也要给参数化留好接口不能用前端传进来的字符串直接拼进去。再加一条最小权限原则数据库账号的权限按需分配查询用户不该有删表权限连接字符串不该写sa账号。一个真正会写SQL的人必须知道哪些写法会把整张表交给攻击者这不是危言耸听是基本功。最后说点我自己的体会。SQL函数的学习真的不建议抱着文档从A到Z背一遍那样遗忘速度极快。我自己常年维护一份“场景速查笔记”遇到一个需求先想清楚想要什么输出再去定位函数类别做完之后把用法和坑记下来。这份笔记其实就是我的错题本比任何教程都管用。如果你正在学SQL我建议你也从今天开始把处理过的每个需求、每个函数、每次报错都按场景归档。半年之后你再回头看函数图谱会从零散的点连成网到那时窗口函数、JSON函数、慢SQL优化这些所谓进阶内容都不会再让你觉得为难。