1. 开篇为什么写了三年SQL还是要回头啃函数说个真实经历。前阵子帮同事排查一个报表问题他写的查询逻辑完全没问题JOIN条件都对WHERE过滤也正确就是跑出来的结果总差那么几行。我盯着屏幕看了十分钟最后发现他在字符串匹配时用了LIKE % 字段 %而那个字段里恰好有空字符串和空格——就这么一个细节数据就错了。这种场景我相信很多人都不陌生。SQL函数看起来是基础中的基础但恰恰是这些“太基础”的东西平时用的时候不觉得等到排查问题、做性能优化或者接手别人代码时才反应过来自己对函数的理解是碎片化的甚至是有偏差的。我写SQL差不多有十年了从SQL Server到MySQL再到PostgreSQL都折腾过期间踩过不少坑也积累了一些经验。最近打算写一个系列文章把SQL里最常用也最容易出问题的函数和方法系统性地梳理一遍这是第一篇。这篇不会讲什么高深的东西就是围绕字符串处理、数值计算、日期时间、聚合与窗口函数、空值处理这五个大类把每一个常用函数掰开揉碎说清楚它的语法、行为、边界情况和实际工作中的使用场景。这篇内容对谁有用三类人。刚入门SQL没多久的新人可以把这篇当作一份带案例的函数手册写了两三年SQL但都是“能跑就行”的开发者可以对照着检查一下自己的写法有没有隐患带团队的技术负责人可以直接把这篇扔给组里的人当培训材料。接下来进入正题。2. 字符串函数日常开发中使用频率最高的函数家族2.1 字符串拼接与截取不只是CONCAT那么简单字符串拼接是写SQL时躲不开的操作。很多人的第一反应是CONCAT但实际上不同数据库的写法差异很大而且各有各的坑。在SQL Server里标准写法是用加号直接拼接。这里有个很经典的坑如果参与拼接的字段里有NULL整个结果会变成NULL而不是你期望的跳过空值。比如你拼一个地址字段SELECT 省 市 区 详细地址 AS 完整地址 FROM 用户表;只要详细地址是NULL那完整地址就整个是NULL。我见过太多人在这一步栽跟头。正确做法是用ISNULL或COALESCE包一层SELECT ISNULL(省, ) ISNULL(市, ) ISNULL(区, ) ISNULL(详细地址, ) AS 完整地址 FROM 用户表;MySQL那边则是用CONCAT和CONCAT_WS。CONCAT会把参数里的NULL直接跳过这一点和SQL Server完全不同。CONCAT_WS多了一个分隔符参数比如拼多个标签字段用CONCAT_WS(,, 标签1, 标签2, 标签3)就非常方便不会出现中间缺分隔符的问题。截取函数方面SUBSTRING系列是最常用的。SQL Server和MySQL都支持SUBSTRING(字段, 起始位置, 长度)但要注意起始位置的索引是从1开始的不是从0。写代码写惯了的人特别容易在这上面吃暗亏——截出来结果总比预期少一位。还有一个相关操作是LEFT和RIGHT取左边或右边N个字符这两个函数在清洗固定格式的编码字段时特别好用比如手机号脱敏SELECT CONCAT(LEFT(手机号, 3), ****, RIGHT(手机号, 4)) AS 脱敏手机号 FROM 用户表;2.2 字符串替换与清理数据清洗环节的看家本领做数据分析的人对REPLACE应该非常熟悉。REPLACE(字段, 旧值, 新值)在所有主流数据库里语法都一样作用就是把字段里的指定字符串整体替换成新值。实际工作中最常见的用途就是清洗脏数据比如把用户输入里的多余空格清理掉把全角字符转成半角或者把旧编码批量换成新编码。清洗数据时还有一个容易被忽视的组合拳LTRIMRTRIM。这俩函数分别去掉字符串左侧和右侧的空格。很多数据库自带TRIM函数可以同时去两边但SQL Server的TRIM在旧版本里不支持所以我习惯了用LTRIM和RTRIM组合。不过这里要提醒一句这三个函数默认只处理普通空格对制表符、换行符是无能为力的需要的话得配合REPLACE先把特殊字符换掉。我处理过一次从老系统导出的数据里面充满了\r\n换行符直接TRIM完全没反应最后是先用REPLACE(字段, CHAR(13), )清掉回车再用REPLACE(字段, CHAR(10), )清掉换行才把数据弄干净。判断一个字段是否包含某个子串不同数据库的写法差异也很明显。SQL Server可以用CHARINDEXMySQL用INSTR或LOCATE但它们的参数顺序不一样写惯了一个数据库再切到另一个就很容易搞混。我的经验是如果只是想判断包含关系直接用LIKE关键字其实最直观也最兼容SELECT * FROM 文章表 WHERE 标题 LIKE %SQL%;但如果想在查询里拿到“包含位置”这个信息或者在一个复杂表达式中嵌套使用那还是得老老实实用对应的函数。2.3 大小写转换与格式处理看起来简单坑却不少UPPER和LOWER是所有数据库都支持的函数分别把字符串转成大写和小写。这个功能在业务上的常见用途是统一用户输入的验证比如登录时忽略邮箱大小写或者做去重时先把数据统一成小写再比较。逻辑很简单但我遇到过一个实际案例某个系统的用户表里邮箱字段有些存的是大写有些是小写做数据merge的时候需要以邮箱为关联键直接关联匹配率只有六成。最后加了一列LOWER(邮箱)作为关联键才把问题解决。大小写相关的还有一个INITCAPPostgreSQL支持把每个单词首字母转成大写其余转成小写。这个函数在处理人名、地名的显示格式时很实用但需要注意它对空格、连字符、括号等分隔符的处理行为不同数据库实现并不完全一致。MySQL里没有直接的INITCAP需要用CONCAT和UPPER、SUBSTRING组合自己写或者干脆在应用层处理。字符串格式化还有一个不得不提的是FORMAT函数。SQL Server 2012 支持FORMAT可以直接用 .NET的格式字符串来格式化日期和数字比如FORMAT(日期, yyyy-MM-dd)。这个函数写起来很直观但性能极差因为它在底层要调用CLR。我之前在给一个报表优化SQL时发现某个查询里对一个十万行的表用了FORMAT结果那一步就占了整个查询60%的执行时间。后来改成CONVERT(varchar, 日期, 23)执行时间直接从十几秒降到了不到两秒。所以我的建议是FORMAT在小数据量或者一次性脚本里用用无妨但在核心查询路径上能不用就不用。3. 数值与日期函数报表开发的核心基石3.1 数值函数取整、取余与随机数的认知纠偏数值函数给人的感觉是没什么好讲的但实际用起来仍然有几个容易踩坑的地方。ROUND函数是最典型的一个。SQL Server和MySQL的ROUND(数值, 小数位数)都是四舍五入但行为存在细微差别。SQL Server的ROUND在小数位数为负数时会对整数部分进行四舍五入比如ROUND(1234.56, -2)返回的是1200。MySQL也有类似行为。但如果你在PostgreSQL里用ROUND(1234.56, -2)会直接报错因为PostgreSQL要求第二参数必须是非负整数。另外ROUND是四舍五入但浮点数在计算机中的存储精度会导致一些意外的结果比如ROUND(2.675, 2)在某些数据库里得到的是2.67而不是2.68这个问题本质上是浮点数表示误差导致的。CEILING和FLOOR是另外两个常用函数分别向上取整和向下取整。这里有一个业务场景很容易搞错计算分页总数时总页数应该用CEILING(总记录数 / 每页条数)而不是ROUND或者直接整除了事。我之前接手的项目里有个人写的是总记录数 / 每页条数结果每页10条、总共31条记录时显示只有3页第4页的数据永远点不出来。ABS、MOD、POWER、SQRT这些基础函数没什么特别要说的但RAND需要单拎出来讲。SQL Server里RAND()不带参数时每次调用返回同一个序列的值这意味着在同一行数据里多次调用RAND()会得到相同的结果。我做随机抽样时踩过这个坑本来想取每组里随机一条记录写了ORDER BY RAND()结果发现每次运行得到的分组结果完全一样因为RAND()在同一批次里生成的随机数序列是固定的。解决办法是给RAND加一个随机的种子参数比如RAND(NEWID())这样每次都会重新生成随机序列。MySQL的RAND()行为则不同每次调用都会重新生成随机数所以ORDER BY RAND()在MySQL里是有效的随机排序方式但大数据量下性能堪忧。3.2 日期函数格式化、计算与时间区间问题的实战日期函数是报表开发中绕不开的主战场这里的坑也比字符串函数更多而且不同数据库之间的差异巨大。先说一下日期获取类。SQL Server的GETDATE()返回当前系统时间MySQL是NOW()PostgreSQL是CURRENT_TIMESTAMP。这三者都是获取当前时刻但SQL Server还有一个SYSDATETIME()返回更高精度的系统时间精度到100纳秒。在做数据同步、记录日志时间戳时我建议用高精度版本因为同一毫秒内的多条记录如果用GETDATE()会得到完全相同的时间戳后续排查问题很难分辨顺序。日期格式化是使用频率最高的操作之一。SQL Server用CONVERT配合样式码来格式化比如CONVERT(varchar(10), GETDATE(), 23)得到2025-01-15CONVERT(varchar(19), GETDATE(), 120)得到2025-01-15 14:30:00。MySQL用的是DATE_FORMAT(日期, %Y-%m-%d %H:%i:%s)。这两种风格差异极大写习惯了MySQL再转去写SQL Server会非常别扭。为了避免团队代码混乱最好在项目初期就约定日期格式化的标准写法并写进团队规范里。日期计算的函数差异更大。SQL Server用DATEADD(day, -7, GETDATE())表示七天前DATEDIFF(day, 开始日期, 结束日期)计算相差天数。MySQL用DATE_SUB(日期, INTERVAL 7 DAY)和TIMESTAMPDIFF。这套写法看起来简单但有一个隐藏问题要注意DATEDIFF比较的是“日期”而非“时间点”如果开始和结束时间跨越了一整天但时间部分有偏差可能会多算或少算一天。举个实际例子用户创建时间是2025-01-15 23:59:59计算到今天2025-01-16 00:00:01的间隔天数DATEDIFF结果是1天实际上只差了1秒。如果要精确到小时或分钟的比较需要用DATEDIFF(hour, ...)配合其他逻辑来处理。还有一个在报表开发中经常碰到的场景统计某个时间段的数据比如“近30天”。很多人直接写WHERE 日期字段 DATEADD(day, -30, GETDATE())这个写法看似没问题但如果你的日期字段包含时间部分这个条件会漏掉当天更晚时刻的数据。正确做法是明确指定范围WHERE 日期字段 DATEADD(day, -30, CAST(GETDATE() AS DATE)) AND 日期字段 DATEADD(day, 1, CAST(GETDATE() AS DATE))这种“写清楚边界”的习惯能帮你避免很多数据统计不一致的问题。3.3 日期与字符互转报表输出的最后一公里日期和字符串之间互相转换是每个写报表的人都要掌握的技能。上面提到的CONVERT和DATE_FORMAT是日期转字符串的两个主力。反过来字符串转日期在SQL Server里可以用CAST(2025-01-15 AS datetime)或CONVERT(datetime, 2025-01-15, 23)MySQL是STR_TO_DATE。这里我想说一个非主流的经验在做数据导入时经常需要处理各种奇怪格式的日期字符串比如2025/1/15、15-01-2025、20250115等等。这种时候不要急着用数据库的转换函数硬解先看清楚数据规律用REPLACE统一格式再转换成标准日期调试效率会高很多。我自己有一次处理一个CSV导入日期字段有四种格式混在一起我直接用STR_TO_DATE转结果一堆报错。后来先清洗格式再转换十分钟就搞定了。日期函数与字符串处理结合使用还有一个常见场景是做日期的维度表。做数据分析的人习惯用日期维度表来关联业务数据里面通常会预先计算好年、月、周、季度、是否工作日等字段。日期函数掌握的熟练程度直接关系到维度表生成的效率和质量。如果你经常做这类工作我强烈建议把DATEADD、DATEDIFF、DATEPART或MySQL里的YEAR()、MONTH()、WEEK()这几个日期提取函数练熟它们是构建任意日期维度的基础设施。4. 聚合与窗口函数从一行到一组的思维跨越4.1 聚合函数GROUP BY时代的核心武器SUM、AVG、COUNT、MAX、MIN这五个聚合函数是SQL查询中最常见的函数族。它们的特点是输入多行、输出一行作用在分组之上。COUNT有一个细节容易被忽视COUNT(*)统计的是行数包含NULL值的行COUNT(字段)只统计该字段非NULL的行。这个差异在排查数据填充率问题时非常关键。比如你统计一个用户表的邮箱填写率用COUNT(邮箱)/COUNT(*)得到的比例就是邮箱的填充率。如果把COUNT(*)随手换成COUNT(1)效果是一样的但团队里很多人不知道COUNT(1)和COUNT(*)没什么区别反而容易引起误解。聚合函数与NULL值的关系还有一个经典问题SUM(字段)如果所有行的字段都是NULL结果不是0而是NULL。这一点坑过不少人。我在做一个销售报表时某个月的销售额字段全是空的结果SUM返回NULL前端展示直接变成一片空白。解决办法是用ISNULL(SUM(字段), 0)包一层或者用COALESCE这样缺数据时也能显示0而不是空白。AVG函数也需要注意它会把NULL自动排除只用非空值计算平均值。这在逻辑上是合理的但有时也会误导人。比如计算考试平均分时缺考的学生记录的是NULL而不是0分那AVG算出来的是“参加考试者的平均分”而不是“全班平均分”。如果业务上需要后者你得先把NULL转成0再平均SELECT AVG(ISNULL(分数, 0)) AS 全班平均分 FROM 成绩表;4.2 窗口函数既不减少行数又能排序分组的现代解法窗口函数是这几年SQL面试中的高频考点也是实际工作中非常能提升效率的一组函数。它和聚合函数最大的区别是聚合函数会把分组折叠成一行而窗口函数在保持原有行数的基础上对每一行计算分组范围内的值。最常见的窗口函数是ROW_NUMBER()、RANK()和DENSE_RANK()三个都是排序编号函数。举个例子取每个部门工资最高的前3名员工SELECT 部门, 员工姓名, 工资 FROM ( SELECT 部门, 员工姓名, 工资, ROW_NUMBER() OVER (PARTITION BY 部门 ORDER BY 工资 DESC) AS rn FROM 员工表 ) t WHERE rn 3;这里ROW_NUMBER()在工资相同的情况下会随机排先后而RANK()会给出相同的排名且后续有跳号DENSE_RANK()则是相同排名且不跳号。三者的差异在排行榜业务场景里至关重要。我做过一个积分排行功能产品经理明确要求相同积分显示相同名次并且下一名要跳号那就要用RANK()如果要求相同积分显示相同名次但下一名不跳号则用DENSE_RANK()。聚合函数也可以在窗口函数中嵌套使用比如计算累计销售额SELECT 月份, 销售额, SUM(销售额) OVER (ORDER BY 月份) AS 累计销售额 FROM 月度销售表;这种写法在分析“每月累计”趋势时非常直观比子查询关联要快得多代码也简洁得多。另外一个常用的窗口函数是LAG和LEAD可以用来获取上一行或下一行的值。计算环比增长率时就特别方便SELECT 月份, 销售额, LAG(销售额, 1) OVER (ORDER BY 月份) AS 上月销售额, (销售额 - LAG(销售额, 1) OVER (ORDER BY 月份)) / LAG(销售额, 1) OVER (ORDER BY 月份) AS 环比增长 FROM 月度销售表;我第一次用窗口函数的时候确实不习惯总觉得把逻辑写在OVER里很抽象。但一旦理解了PARTITION BY是“分组维度”、ORDER BY是“组内排序依据”这两个概念之后窗口函数就会变成最高频使用的工具之一很多以前要写子查询、临时表的逻辑现在一条SQL就能搞定。4.3 去重问题的两种解法DISTINCT和窗口函数怎么选DISTINCT是解决重复数据最简单的方式热搜词里也有“sql语句去重”“清洗sql语句去重”这些关键词。但DISTINCT的局限性在于它只作用于查询结果集不能做有条件的去重。比如保留每个用户的最新一条记录DISTINCT就无能为力了。聚合函数配合GROUP BY是去重的一种办法但也会丢失其他字段的明细信息。比如你GROUP BY 用户ID之后只能拿到用户ID和聚合结果拿不到那个用户的最新订单的其他字段。这种场景正确解法是窗口函数SELECT 用户ID, 订单时间, 订单金额 FROM ( SELECT 用户ID, 订单时间, 订单金额, ROW_NUMBER() OVER (PARTITION BY 用户ID ORDER BY 订单时间 DESC) AS rn FROM 订单表 ) t WHERE rn 1;这个模式在实际工作中非常常见强烈建议背下来。不管是“每个商品的最低价”“每人最近一次登录记录”“每个班级最高分的学生”都能套这套模板。它的效率比子查询关联要高可读性也更强是我平时写SQL使用频率最高的技巧之一。5. 空值与类型转换看起来小炸起来疼5.1 空值处理NULL的三种判断方式和两个坑NULL是SQL里最特别也最容易被误解的概念。它既不是0也不是空字符串更不是NULL这四个字符它的含义是“未知”或“未定义”。正因为它代表的是“未知”所以任何与NULL做等值比较都会返回UNKNOWN也就是不成立。这就是为什么不能用WHERE 字段 NULL而必须用WHERE 字段 IS NULL来判断空值。NULL参与的算术运算全部返回NULL。比如1 NULL结果是NULL这在做计算时尤其危险因为一条记录为空就会导致整个汇总结果为空。前面讲聚合函数时提到过的SUM(NULL)问题和字符串拼接时提到的CONCAT(字段 NULL)问题本质都是这个特性。处理NULL有两个常用函数ISNULL和COALESCE。SQL Server的ISNULL(字段, 替换值)只支持两个参数而COALESCE可以支持多个参数返回第一个非NULL的值。道理很简单但实际使用时有个性能问题COALESCE的参数如果是子查询在某些数据库优化器下可能每个参数都会完整执行一遍。我之前优化过一个慢查询排查到最后发现是COALESCE里写了两个标量子查询而且数据库没有做短路处理白白执行了两次子查询。改成CASE WHEN之后就快多了。所以我的建议是COALESCE适合参数简单的情况参数复杂时优先用CASE表达式来保证执行效率。空字符串和NULL还有一个区别需要搞清楚。MySQL里不是NULL它是个长度为0的字符串。但在Oracle里会被自动转成NULL。这个差异在跨数据库迁移时经常引发数据不一致问题。如果是做数据迁移我建议在迁移前跑一段统计脚本分别统计字段的NULL率、空字符串率、全空格率把数据分布摸清楚再做迁移方案。5.2 类型转换隐式转换是性能杀手显式转换也分效率高低SQL中的类型转换分为隐式和显式两种。隐式转换就是数据库自动把一种类型转成另一种类型比如字符串与数字比较时数据库会尝试自动转换。听起来很方便但隐式转换是SQL性能优化里一个典型的“隐形杀手”。举个最常见的例子某个表的ID字段是varchar类型但你查询时传了一个数字SELECT * FROM 订单表 WHERE 订单号 123456;这时候数据库实际上会执行订单号字段的隐式转换也就是CONVERT(varchar, 123456)然后比较。如果字段上有索引这个隐式转换会导致索引失效查询变成全表扫描。数据量小的时候感觉不到问题数据量到百万级以上查询时间可能是几十倍的差距。解决办法很简单查询时明确传字符串类型或者给字段类型统一改造。显式转换在不同数据库中的函数也不一样。SQL Server的CAST和CONVERT都可以用CAST(字段 AS 类型)是ANSI标准CONVERT是SQL Server特有的。CONVERT多了样式参数可以自定义格式但也正因为参数多有时候新手会写错。MySQL的转换函数是CAST和CONVERT(字段, 类型)注意参数顺序和SQL Server不同。类型转换最容易出错的地方是字符串转数字和日期。字符串转数字时如果字符串里有非数字字符不同数据库的处理方式不一样SQL Server会直接报错MySQL在严格模式下报错、非严格模式下会截断成0或者其他值。我在处理用户上传的Excel数据时就遇到过这种问题一堆25.5元、100这样的脏数据。我的处理方式是先做数据清洗把非数字字符用正则或者REPLACE逐层去掉再统一转成数字否则就算数据库不报错转换结果也是错的。日期转字符串时前面已经说过FORMAT的性能问题这里我再补充一句能用CONVERT或DATE_FORMAT解决的就别用FORMAT尤其在报表查询里差一个量级的执行时间是完全有可能的。6. 函数与SQL性能那些慢查询的常见诱因6.1 在WHERE条件中使用函数会让索引“失效”的操作“慢SQL优化”是很多人搜索的高频词而函数使用不当就是慢查询的第一大根源。最常见的问题是索引建立在普通列上但你在WHERE条件里对列套了一层函数导致索引失效。比如你有一个按创建时间建的索引但你的查询是SELECT * FROM 订单表 WHERE YEAR(创建时间) 2025;数据库没法直接使用创建时间上的索引因为它需要对每一行的创建时间都执行YEAR()函数再拿结果跟2025比较。如果数据量很大这就是一个完整的全表扫描。改成范围查询就能利用索引SELECT * FROM 订单表 WHERE 创建时间 2025-01-01 AND 创建时间 2026-01-01;类似的还有在字符串字段上使用LEFT(字段, 3) abc来替代前缀匹配或者在数值字段上使用ABS(字段) 100来代替范围条件。这些写法都会破坏索引的可用性。常规的经验法则是尽量把函数写在等号右边让列保持“干净”的状态。这里不是说所有函数都能移到右边但凡是能改写成范围条件的都应该优先改写性能收益非常明显。那是不是完全不建议在WHERE里用函数也不是。如果数据量很小全表扫描也就几毫秒的事为了查询性能去改写SQL反而是过度优化。判断标准是看执行计划里有没有走索引、扫描的行数多不多。写SQL性能优化的一个基本素养就是养成看执行计划的习惯而不是靠猜。6.2 函数嵌套过深可读性崩塌与隐性性能开销SQL函数可以嵌套使用比如SUBSTRING(REPLACE(LTRIM(字段), , -), 1, 10)这种写法。表达力很强但问题也很明显可读性极差维护困难而且嵌套层数过深时还可能触发数据库的表达式计算上限。我的建议是超过两层嵌套的表达式拆开写。可以用CROSS APPLYSQL Server或者公共表表达式CTE来分步计算让每一步都有名字。这不仅能提高可读性更重要的是能让每一步的计算结果复用。说一个有一次优化报表的真实经历。原SQL里有一个长表达式大概嵌套了六层函数用来计算用户分类标签。每次运行都要对所有历史数据跑一遍这个表达式毫无复用可言。我把这个表达式拆成CTE分了三步先清洗原始字段再格式化最后分类打标。结果报表生成时间从原来的半个小时缩短到四分钟查询逻辑也清晰多了后续别人接手也能看得懂。函数嵌套还有一个容易忽略的问题是类型转换的复合效应。每一层函数都可能有隐式类型转换嵌套越深隐式转换次数越多而每次隐式转换都是计算资源的消耗。虽然单个转换的代价很小但乘以百万行数据后累计开销就不可忽略了。6.3 聚合查询的优化能提前过滤就提前过滤聚合函数配合GROUP BY写统计查询时很多人会忽视过滤顺序对性能的影响。WHERE在分组前过滤HAVING在分组后过滤。如果你的过滤条件不涉及聚合结果应该放在WHERE里这样参与分组的行数少了聚合计算量自然就小了。举个例子SELECT 部门, COUNT(*) AS 员工数 FROM 员工表 WHERE 状态 在职 GROUP BY 部门;这个写法比HAVING COUNT(*) 0的过滤方式性能更好因为WHERE先过滤掉了离职员工只有在职员工参与分组。老手写聚合查询的一个习惯是总会看一眼WHERE和HAVING各过滤了多少行在EXPLAIN或执行计划里确认每一步的数据量级避免无谓的计算。另外COUNT(DISTINCT 字段)也是一个容易产生性能瓶颈的用法。这个函数要精确计算去重后的数量代价非常高。如果业务上能接受近似值可以考虑用APPROX_COUNT_DISTINCTSQL Server 2019大数据量下速度极快但结果会有极小的偏差。但要注意这种近似函数在结果精度要求极高的财务场景里不能乱用。7. 常见问题与排查技巧实录7.1 字符串函数相关的典型报错与奇怪结果SQL Server里CONCAT不支持多个参数。很多人从MySQL转到SQL Server后习惯性写CONCAT(字段1, 字段2, 字段3)却发现SQL Server的CONCAT只支持两个参数。我在团队带人时反复强调过SQL Server的字符串拼接用号MySQL的CONCAT支持多个参数。跨数据库写SQL时这是第一个容易踩的坑。字符串截取结果比预期少一位。前面提过SUBSTRING的起始位置从1开始还有一个问题是中文和英文字符混用时长度不好把握。SQL Server的SUBSTRING按字符计数中文和英文都算一个字符但如果你用LEFT截取字节在MySQL里可以配合CHAR_LENGTH和LENGTH的差异来排查。多个空格导致LIKE匹配不中。查询时明明感觉数据里有这个关键字但LIKE %关键词%就是查不到。原因往往是数据里混了全角空格或不可见字符。排查办法是先排除空格干扰SELECT * FROM 文章表 WHERE REPLACE(REPLACE(标题, , ), CHAR(9), ) LIKE %关键词%;如果这样能查到那大概率是特殊字符问题。另外可以查看这个字段的LENGTH和CHAR_LENGTH差异如果差异很大说明字段里含有大量空格或特殊字符。大小写比较导致匹配失败。默认情况下SQL Server的排序规则可能是大小写不敏感的但有的数据库实例大小写敏感。排查时先确认数据库的排序规则再决定是否要统一LOWER或UPPER后再比较。7.2 日期函数相关的典型报错与数据偏差**CONVERT转换失败导致查询报错**。字符串转日期时格式不对SQL Server会直接抛出 conversion failed 错误。排查思路是先把异常数据找出来再用TRY_CONVERTSQL Server 2012替代CONVERT这样转换失败时会返回NULL而不是报错。时区问题导致的数据偏差。数据库服务器的时区设置会直接影响日期函数的结果尤其是用了GETDATE()或NOW()这类“当前时间”函数时。如果应用服务器和数据库服务器在不同时区就会出现取到的日期差一天的情况。排查方式是先确认各服务器的时区配置统一把时间存储为UTC时间展示层再做时区转换。跨数据库的日期函数差异导致的移植报错。从MySQL迁移到SQL Server时日期函数基本全部要改NOW()要改成GETDATE()DATE_FORMAT要改成CONVERTDATE_ADD要改成DATEADD。我在做数据库迁移项目时专门列过一个函数映射表每次迁移前先用静态扫描工具找出所有用到日期函数的代码再逐条替换防止遗漏。7.3 一个完整的排查实例慢查询中的函数陷阱最后分享一个真实的排查过程这是几个月前帮客户优化系统时遇到的。客户反馈某个管理后台的报表页面越跑越慢数据量只有几十万行但每次查询要十几秒。我拿到的SQL大致是这样的SELECT FORMAT(创建时间, yyyy-MM-dd) AS 日期, COUNT(*) AS 订单数 FROM 订单表 WHERE YEAR(创建时间) 2025 AND MONTH(创建时间) 1 GROUP BY FORMAT(创建时间, yyyy-MM-dd);一眼扫过去至少三个问题FORMAT在分组里用性能极差YEAR()和MONTH()套在索引列上导致索引失效同一字段算了两遍FORMAT却没有复用。我把SQL改成这样SELECT CONVERT(varchar(10), 创建时间, 23) AS 日期, COUNT(*) AS 订单数 FROM 订单表 WHERE 创建时间 2025-01-01 AND 创建时间 2025-02-01 GROUP BY CONVERT(varchar(10), 创建时间, 23);改动并不复杂但查询时间直接从12秒降到0.3秒。客户的反馈是“像换了一套系统”。这个案例里用到的排查思路就是在执行计划里看每一步的操作类型和行数发现全表扫描和Compute Scalar操作占了大头然后顺藤摸瓜找到了那几个函数陷阱。这类问题的共性也很统一不是SQL写不出来而是写得太“顺手”没有考虑数据库底层是怎么执行这些函数的。正如前面反复提到的写SQL是要带着“数据库视角”去写的你写下的每一行代码最终都会变成数据库的每一步执行计划。8. 写在最后函数不是背出来的是练出来的这个系列的第一篇就到这里。说实话写这种基础性的内容反而比写高深的技术难点更费劲因为越基础的东西越难讲出新意也越容易被“以为懂了”的心态带过去。我自己的经验是SQL函数掌握得扎不扎实不看你能不能背出所有函数的语法而是看你在实际场景里能不能第一时间想到“这里应该用哪个函数、有哪些边界情况要注意”。如果你刚接触SQL不久我的建议是先别急着啃那些复杂的优化技巧把常用函数过一遍用自己手头的真实数据跑一遍看看不同函数在边界条件下的行为差异。如果你已经写了两三年SQL建议找个周末把自己最近写的查询翻出来专门检查一遍里面有没有隐式转换、有没有在索引列上套函数、有没有用FORMAT写大报表——这些都是能立刻带来性能提升的改进点。下一篇我会继续讲SQL函数里更进阶的部分包括条件逻辑相关的函数CASE WHEN的深度用法、IIF、NULLIF、JSON与XML的处理函数、以及跨数据库的函数兼容性问题。欢迎关注这个系列有什么想了解的具体问题也可以在评论区留言我会挑典型的写进后续的文章里。
SQL常用函数实战指南：从字符串处理到窗口函数与性能优化
1. 开篇为什么写了三年SQL还是要回头啃函数说个真实经历。前阵子帮同事排查一个报表问题他写的查询逻辑完全没问题JOIN条件都对WHERE过滤也正确就是跑出来的结果总差那么几行。我盯着屏幕看了十分钟最后发现他在字符串匹配时用了LIKE % 字段 %而那个字段里恰好有空字符串和空格——就这么一个细节数据就错了。这种场景我相信很多人都不陌生。SQL函数看起来是基础中的基础但恰恰是这些“太基础”的东西平时用的时候不觉得等到排查问题、做性能优化或者接手别人代码时才反应过来自己对函数的理解是碎片化的甚至是有偏差的。我写SQL差不多有十年了从SQL Server到MySQL再到PostgreSQL都折腾过期间踩过不少坑也积累了一些经验。最近打算写一个系列文章把SQL里最常用也最容易出问题的函数和方法系统性地梳理一遍这是第一篇。这篇不会讲什么高深的东西就是围绕字符串处理、数值计算、日期时间、聚合与窗口函数、空值处理这五个大类把每一个常用函数掰开揉碎说清楚它的语法、行为、边界情况和实际工作中的使用场景。这篇内容对谁有用三类人。刚入门SQL没多久的新人可以把这篇当作一份带案例的函数手册写了两三年SQL但都是“能跑就行”的开发者可以对照着检查一下自己的写法有没有隐患带团队的技术负责人可以直接把这篇扔给组里的人当培训材料。接下来进入正题。2. 字符串函数日常开发中使用频率最高的函数家族2.1 字符串拼接与截取不只是CONCAT那么简单字符串拼接是写SQL时躲不开的操作。很多人的第一反应是CONCAT但实际上不同数据库的写法差异很大而且各有各的坑。在SQL Server里标准写法是用加号直接拼接。这里有个很经典的坑如果参与拼接的字段里有NULL整个结果会变成NULL而不是你期望的跳过空值。比如你拼一个地址字段SELECT 省 市 区 详细地址 AS 完整地址 FROM 用户表;只要详细地址是NULL那完整地址就整个是NULL。我见过太多人在这一步栽跟头。正确做法是用ISNULL或COALESCE包一层SELECT ISNULL(省, ) ISNULL(市, ) ISNULL(区, ) ISNULL(详细地址, ) AS 完整地址 FROM 用户表;MySQL那边则是用CONCAT和CONCAT_WS。CONCAT会把参数里的NULL直接跳过这一点和SQL Server完全不同。CONCAT_WS多了一个分隔符参数比如拼多个标签字段用CONCAT_WS(,, 标签1, 标签2, 标签3)就非常方便不会出现中间缺分隔符的问题。截取函数方面SUBSTRING系列是最常用的。SQL Server和MySQL都支持SUBSTRING(字段, 起始位置, 长度)但要注意起始位置的索引是从1开始的不是从0。写代码写惯了的人特别容易在这上面吃暗亏——截出来结果总比预期少一位。还有一个相关操作是LEFT和RIGHT取左边或右边N个字符这两个函数在清洗固定格式的编码字段时特别好用比如手机号脱敏SELECT CONCAT(LEFT(手机号, 3), ****, RIGHT(手机号, 4)) AS 脱敏手机号 FROM 用户表;2.2 字符串替换与清理数据清洗环节的看家本领做数据分析的人对REPLACE应该非常熟悉。REPLACE(字段, 旧值, 新值)在所有主流数据库里语法都一样作用就是把字段里的指定字符串整体替换成新值。实际工作中最常见的用途就是清洗脏数据比如把用户输入里的多余空格清理掉把全角字符转成半角或者把旧编码批量换成新编码。清洗数据时还有一个容易被忽视的组合拳LTRIMRTRIM。这俩函数分别去掉字符串左侧和右侧的空格。很多数据库自带TRIM函数可以同时去两边但SQL Server的TRIM在旧版本里不支持所以我习惯了用LTRIM和RTRIM组合。不过这里要提醒一句这三个函数默认只处理普通空格对制表符、换行符是无能为力的需要的话得配合REPLACE先把特殊字符换掉。我处理过一次从老系统导出的数据里面充满了\r\n换行符直接TRIM完全没反应最后是先用REPLACE(字段, CHAR(13), )清掉回车再用REPLACE(字段, CHAR(10), )清掉换行才把数据弄干净。判断一个字段是否包含某个子串不同数据库的写法差异也很明显。SQL Server可以用CHARINDEXMySQL用INSTR或LOCATE但它们的参数顺序不一样写惯了一个数据库再切到另一个就很容易搞混。我的经验是如果只是想判断包含关系直接用LIKE关键字其实最直观也最兼容SELECT * FROM 文章表 WHERE 标题 LIKE %SQL%;但如果想在查询里拿到“包含位置”这个信息或者在一个复杂表达式中嵌套使用那还是得老老实实用对应的函数。2.3 大小写转换与格式处理看起来简单坑却不少UPPER和LOWER是所有数据库都支持的函数分别把字符串转成大写和小写。这个功能在业务上的常见用途是统一用户输入的验证比如登录时忽略邮箱大小写或者做去重时先把数据统一成小写再比较。逻辑很简单但我遇到过一个实际案例某个系统的用户表里邮箱字段有些存的是大写有些是小写做数据merge的时候需要以邮箱为关联键直接关联匹配率只有六成。最后加了一列LOWER(邮箱)作为关联键才把问题解决。大小写相关的还有一个INITCAPPostgreSQL支持把每个单词首字母转成大写其余转成小写。这个函数在处理人名、地名的显示格式时很实用但需要注意它对空格、连字符、括号等分隔符的处理行为不同数据库实现并不完全一致。MySQL里没有直接的INITCAP需要用CONCAT和UPPER、SUBSTRING组合自己写或者干脆在应用层处理。字符串格式化还有一个不得不提的是FORMAT函数。SQL Server 2012 支持FORMAT可以直接用 .NET的格式字符串来格式化日期和数字比如FORMAT(日期, yyyy-MM-dd)。这个函数写起来很直观但性能极差因为它在底层要调用CLR。我之前在给一个报表优化SQL时发现某个查询里对一个十万行的表用了FORMAT结果那一步就占了整个查询60%的执行时间。后来改成CONVERT(varchar, 日期, 23)执行时间直接从十几秒降到了不到两秒。所以我的建议是FORMAT在小数据量或者一次性脚本里用用无妨但在核心查询路径上能不用就不用。3. 数值与日期函数报表开发的核心基石3.1 数值函数取整、取余与随机数的认知纠偏数值函数给人的感觉是没什么好讲的但实际用起来仍然有几个容易踩坑的地方。ROUND函数是最典型的一个。SQL Server和MySQL的ROUND(数值, 小数位数)都是四舍五入但行为存在细微差别。SQL Server的ROUND在小数位数为负数时会对整数部分进行四舍五入比如ROUND(1234.56, -2)返回的是1200。MySQL也有类似行为。但如果你在PostgreSQL里用ROUND(1234.56, -2)会直接报错因为PostgreSQL要求第二参数必须是非负整数。另外ROUND是四舍五入但浮点数在计算机中的存储精度会导致一些意外的结果比如ROUND(2.675, 2)在某些数据库里得到的是2.67而不是2.68这个问题本质上是浮点数表示误差导致的。CEILING和FLOOR是另外两个常用函数分别向上取整和向下取整。这里有一个业务场景很容易搞错计算分页总数时总页数应该用CEILING(总记录数 / 每页条数)而不是ROUND或者直接整除了事。我之前接手的项目里有个人写的是总记录数 / 每页条数结果每页10条、总共31条记录时显示只有3页第4页的数据永远点不出来。ABS、MOD、POWER、SQRT这些基础函数没什么特别要说的但RAND需要单拎出来讲。SQL Server里RAND()不带参数时每次调用返回同一个序列的值这意味着在同一行数据里多次调用RAND()会得到相同的结果。我做随机抽样时踩过这个坑本来想取每组里随机一条记录写了ORDER BY RAND()结果发现每次运行得到的分组结果完全一样因为RAND()在同一批次里生成的随机数序列是固定的。解决办法是给RAND加一个随机的种子参数比如RAND(NEWID())这样每次都会重新生成随机序列。MySQL的RAND()行为则不同每次调用都会重新生成随机数所以ORDER BY RAND()在MySQL里是有效的随机排序方式但大数据量下性能堪忧。3.2 日期函数格式化、计算与时间区间问题的实战日期函数是报表开发中绕不开的主战场这里的坑也比字符串函数更多而且不同数据库之间的差异巨大。先说一下日期获取类。SQL Server的GETDATE()返回当前系统时间MySQL是NOW()PostgreSQL是CURRENT_TIMESTAMP。这三者都是获取当前时刻但SQL Server还有一个SYSDATETIME()返回更高精度的系统时间精度到100纳秒。在做数据同步、记录日志时间戳时我建议用高精度版本因为同一毫秒内的多条记录如果用GETDATE()会得到完全相同的时间戳后续排查问题很难分辨顺序。日期格式化是使用频率最高的操作之一。SQL Server用CONVERT配合样式码来格式化比如CONVERT(varchar(10), GETDATE(), 23)得到2025-01-15CONVERT(varchar(19), GETDATE(), 120)得到2025-01-15 14:30:00。MySQL用的是DATE_FORMAT(日期, %Y-%m-%d %H:%i:%s)。这两种风格差异极大写习惯了MySQL再转去写SQL Server会非常别扭。为了避免团队代码混乱最好在项目初期就约定日期格式化的标准写法并写进团队规范里。日期计算的函数差异更大。SQL Server用DATEADD(day, -7, GETDATE())表示七天前DATEDIFF(day, 开始日期, 结束日期)计算相差天数。MySQL用DATE_SUB(日期, INTERVAL 7 DAY)和TIMESTAMPDIFF。这套写法看起来简单但有一个隐藏问题要注意DATEDIFF比较的是“日期”而非“时间点”如果开始和结束时间跨越了一整天但时间部分有偏差可能会多算或少算一天。举个实际例子用户创建时间是2025-01-15 23:59:59计算到今天2025-01-16 00:00:01的间隔天数DATEDIFF结果是1天实际上只差了1秒。如果要精确到小时或分钟的比较需要用DATEDIFF(hour, ...)配合其他逻辑来处理。还有一个在报表开发中经常碰到的场景统计某个时间段的数据比如“近30天”。很多人直接写WHERE 日期字段 DATEADD(day, -30, GETDATE())这个写法看似没问题但如果你的日期字段包含时间部分这个条件会漏掉当天更晚时刻的数据。正确做法是明确指定范围WHERE 日期字段 DATEADD(day, -30, CAST(GETDATE() AS DATE)) AND 日期字段 DATEADD(day, 1, CAST(GETDATE() AS DATE))这种“写清楚边界”的习惯能帮你避免很多数据统计不一致的问题。3.3 日期与字符互转报表输出的最后一公里日期和字符串之间互相转换是每个写报表的人都要掌握的技能。上面提到的CONVERT和DATE_FORMAT是日期转字符串的两个主力。反过来字符串转日期在SQL Server里可以用CAST(2025-01-15 AS datetime)或CONVERT(datetime, 2025-01-15, 23)MySQL是STR_TO_DATE。这里我想说一个非主流的经验在做数据导入时经常需要处理各种奇怪格式的日期字符串比如2025/1/15、15-01-2025、20250115等等。这种时候不要急着用数据库的转换函数硬解先看清楚数据规律用REPLACE统一格式再转换成标准日期调试效率会高很多。我自己有一次处理一个CSV导入日期字段有四种格式混在一起我直接用STR_TO_DATE转结果一堆报错。后来先清洗格式再转换十分钟就搞定了。日期函数与字符串处理结合使用还有一个常见场景是做日期的维度表。做数据分析的人习惯用日期维度表来关联业务数据里面通常会预先计算好年、月、周、季度、是否工作日等字段。日期函数掌握的熟练程度直接关系到维度表生成的效率和质量。如果你经常做这类工作我强烈建议把DATEADD、DATEDIFF、DATEPART或MySQL里的YEAR()、MONTH()、WEEK()这几个日期提取函数练熟它们是构建任意日期维度的基础设施。4. 聚合与窗口函数从一行到一组的思维跨越4.1 聚合函数GROUP BY时代的核心武器SUM、AVG、COUNT、MAX、MIN这五个聚合函数是SQL查询中最常见的函数族。它们的特点是输入多行、输出一行作用在分组之上。COUNT有一个细节容易被忽视COUNT(*)统计的是行数包含NULL值的行COUNT(字段)只统计该字段非NULL的行。这个差异在排查数据填充率问题时非常关键。比如你统计一个用户表的邮箱填写率用COUNT(邮箱)/COUNT(*)得到的比例就是邮箱的填充率。如果把COUNT(*)随手换成COUNT(1)效果是一样的但团队里很多人不知道COUNT(1)和COUNT(*)没什么区别反而容易引起误解。聚合函数与NULL值的关系还有一个经典问题SUM(字段)如果所有行的字段都是NULL结果不是0而是NULL。这一点坑过不少人。我在做一个销售报表时某个月的销售额字段全是空的结果SUM返回NULL前端展示直接变成一片空白。解决办法是用ISNULL(SUM(字段), 0)包一层或者用COALESCE这样缺数据时也能显示0而不是空白。AVG函数也需要注意它会把NULL自动排除只用非空值计算平均值。这在逻辑上是合理的但有时也会误导人。比如计算考试平均分时缺考的学生记录的是NULL而不是0分那AVG算出来的是“参加考试者的平均分”而不是“全班平均分”。如果业务上需要后者你得先把NULL转成0再平均SELECT AVG(ISNULL(分数, 0)) AS 全班平均分 FROM 成绩表;4.2 窗口函数既不减少行数又能排序分组的现代解法窗口函数是这几年SQL面试中的高频考点也是实际工作中非常能提升效率的一组函数。它和聚合函数最大的区别是聚合函数会把分组折叠成一行而窗口函数在保持原有行数的基础上对每一行计算分组范围内的值。最常见的窗口函数是ROW_NUMBER()、RANK()和DENSE_RANK()三个都是排序编号函数。举个例子取每个部门工资最高的前3名员工SELECT 部门, 员工姓名, 工资 FROM ( SELECT 部门, 员工姓名, 工资, ROW_NUMBER() OVER (PARTITION BY 部门 ORDER BY 工资 DESC) AS rn FROM 员工表 ) t WHERE rn 3;这里ROW_NUMBER()在工资相同的情况下会随机排先后而RANK()会给出相同的排名且后续有跳号DENSE_RANK()则是相同排名且不跳号。三者的差异在排行榜业务场景里至关重要。我做过一个积分排行功能产品经理明确要求相同积分显示相同名次并且下一名要跳号那就要用RANK()如果要求相同积分显示相同名次但下一名不跳号则用DENSE_RANK()。聚合函数也可以在窗口函数中嵌套使用比如计算累计销售额SELECT 月份, 销售额, SUM(销售额) OVER (ORDER BY 月份) AS 累计销售额 FROM 月度销售表;这种写法在分析“每月累计”趋势时非常直观比子查询关联要快得多代码也简洁得多。另外一个常用的窗口函数是LAG和LEAD可以用来获取上一行或下一行的值。计算环比增长率时就特别方便SELECT 月份, 销售额, LAG(销售额, 1) OVER (ORDER BY 月份) AS 上月销售额, (销售额 - LAG(销售额, 1) OVER (ORDER BY 月份)) / LAG(销售额, 1) OVER (ORDER BY 月份) AS 环比增长 FROM 月度销售表;我第一次用窗口函数的时候确实不习惯总觉得把逻辑写在OVER里很抽象。但一旦理解了PARTITION BY是“分组维度”、ORDER BY是“组内排序依据”这两个概念之后窗口函数就会变成最高频使用的工具之一很多以前要写子查询、临时表的逻辑现在一条SQL就能搞定。4.3 去重问题的两种解法DISTINCT和窗口函数怎么选DISTINCT是解决重复数据最简单的方式热搜词里也有“sql语句去重”“清洗sql语句去重”这些关键词。但DISTINCT的局限性在于它只作用于查询结果集不能做有条件的去重。比如保留每个用户的最新一条记录DISTINCT就无能为力了。聚合函数配合GROUP BY是去重的一种办法但也会丢失其他字段的明细信息。比如你GROUP BY 用户ID之后只能拿到用户ID和聚合结果拿不到那个用户的最新订单的其他字段。这种场景正确解法是窗口函数SELECT 用户ID, 订单时间, 订单金额 FROM ( SELECT 用户ID, 订单时间, 订单金额, ROW_NUMBER() OVER (PARTITION BY 用户ID ORDER BY 订单时间 DESC) AS rn FROM 订单表 ) t WHERE rn 1;这个模式在实际工作中非常常见强烈建议背下来。不管是“每个商品的最低价”“每人最近一次登录记录”“每个班级最高分的学生”都能套这套模板。它的效率比子查询关联要高可读性也更强是我平时写SQL使用频率最高的技巧之一。5. 空值与类型转换看起来小炸起来疼5.1 空值处理NULL的三种判断方式和两个坑NULL是SQL里最特别也最容易被误解的概念。它既不是0也不是空字符串更不是NULL这四个字符它的含义是“未知”或“未定义”。正因为它代表的是“未知”所以任何与NULL做等值比较都会返回UNKNOWN也就是不成立。这就是为什么不能用WHERE 字段 NULL而必须用WHERE 字段 IS NULL来判断空值。NULL参与的算术运算全部返回NULL。比如1 NULL结果是NULL这在做计算时尤其危险因为一条记录为空就会导致整个汇总结果为空。前面讲聚合函数时提到过的SUM(NULL)问题和字符串拼接时提到的CONCAT(字段 NULL)问题本质都是这个特性。处理NULL有两个常用函数ISNULL和COALESCE。SQL Server的ISNULL(字段, 替换值)只支持两个参数而COALESCE可以支持多个参数返回第一个非NULL的值。道理很简单但实际使用时有个性能问题COALESCE的参数如果是子查询在某些数据库优化器下可能每个参数都会完整执行一遍。我之前优化过一个慢查询排查到最后发现是COALESCE里写了两个标量子查询而且数据库没有做短路处理白白执行了两次子查询。改成CASE WHEN之后就快多了。所以我的建议是COALESCE适合参数简单的情况参数复杂时优先用CASE表达式来保证执行效率。空字符串和NULL还有一个区别需要搞清楚。MySQL里不是NULL它是个长度为0的字符串。但在Oracle里会被自动转成NULL。这个差异在跨数据库迁移时经常引发数据不一致问题。如果是做数据迁移我建议在迁移前跑一段统计脚本分别统计字段的NULL率、空字符串率、全空格率把数据分布摸清楚再做迁移方案。5.2 类型转换隐式转换是性能杀手显式转换也分效率高低SQL中的类型转换分为隐式和显式两种。隐式转换就是数据库自动把一种类型转成另一种类型比如字符串与数字比较时数据库会尝试自动转换。听起来很方便但隐式转换是SQL性能优化里一个典型的“隐形杀手”。举个最常见的例子某个表的ID字段是varchar类型但你查询时传了一个数字SELECT * FROM 订单表 WHERE 订单号 123456;这时候数据库实际上会执行订单号字段的隐式转换也就是CONVERT(varchar, 123456)然后比较。如果字段上有索引这个隐式转换会导致索引失效查询变成全表扫描。数据量小的时候感觉不到问题数据量到百万级以上查询时间可能是几十倍的差距。解决办法很简单查询时明确传字符串类型或者给字段类型统一改造。显式转换在不同数据库中的函数也不一样。SQL Server的CAST和CONVERT都可以用CAST(字段 AS 类型)是ANSI标准CONVERT是SQL Server特有的。CONVERT多了样式参数可以自定义格式但也正因为参数多有时候新手会写错。MySQL的转换函数是CAST和CONVERT(字段, 类型)注意参数顺序和SQL Server不同。类型转换最容易出错的地方是字符串转数字和日期。字符串转数字时如果字符串里有非数字字符不同数据库的处理方式不一样SQL Server会直接报错MySQL在严格模式下报错、非严格模式下会截断成0或者其他值。我在处理用户上传的Excel数据时就遇到过这种问题一堆25.5元、100这样的脏数据。我的处理方式是先做数据清洗把非数字字符用正则或者REPLACE逐层去掉再统一转成数字否则就算数据库不报错转换结果也是错的。日期转字符串时前面已经说过FORMAT的性能问题这里我再补充一句能用CONVERT或DATE_FORMAT解决的就别用FORMAT尤其在报表查询里差一个量级的执行时间是完全有可能的。6. 函数与SQL性能那些慢查询的常见诱因6.1 在WHERE条件中使用函数会让索引“失效”的操作“慢SQL优化”是很多人搜索的高频词而函数使用不当就是慢查询的第一大根源。最常见的问题是索引建立在普通列上但你在WHERE条件里对列套了一层函数导致索引失效。比如你有一个按创建时间建的索引但你的查询是SELECT * FROM 订单表 WHERE YEAR(创建时间) 2025;数据库没法直接使用创建时间上的索引因为它需要对每一行的创建时间都执行YEAR()函数再拿结果跟2025比较。如果数据量很大这就是一个完整的全表扫描。改成范围查询就能利用索引SELECT * FROM 订单表 WHERE 创建时间 2025-01-01 AND 创建时间 2026-01-01;类似的还有在字符串字段上使用LEFT(字段, 3) abc来替代前缀匹配或者在数值字段上使用ABS(字段) 100来代替范围条件。这些写法都会破坏索引的可用性。常规的经验法则是尽量把函数写在等号右边让列保持“干净”的状态。这里不是说所有函数都能移到右边但凡是能改写成范围条件的都应该优先改写性能收益非常明显。那是不是完全不建议在WHERE里用函数也不是。如果数据量很小全表扫描也就几毫秒的事为了查询性能去改写SQL反而是过度优化。判断标准是看执行计划里有没有走索引、扫描的行数多不多。写SQL性能优化的一个基本素养就是养成看执行计划的习惯而不是靠猜。6.2 函数嵌套过深可读性崩塌与隐性性能开销SQL函数可以嵌套使用比如SUBSTRING(REPLACE(LTRIM(字段), , -), 1, 10)这种写法。表达力很强但问题也很明显可读性极差维护困难而且嵌套层数过深时还可能触发数据库的表达式计算上限。我的建议是超过两层嵌套的表达式拆开写。可以用CROSS APPLYSQL Server或者公共表表达式CTE来分步计算让每一步都有名字。这不仅能提高可读性更重要的是能让每一步的计算结果复用。说一个有一次优化报表的真实经历。原SQL里有一个长表达式大概嵌套了六层函数用来计算用户分类标签。每次运行都要对所有历史数据跑一遍这个表达式毫无复用可言。我把这个表达式拆成CTE分了三步先清洗原始字段再格式化最后分类打标。结果报表生成时间从原来的半个小时缩短到四分钟查询逻辑也清晰多了后续别人接手也能看得懂。函数嵌套还有一个容易忽略的问题是类型转换的复合效应。每一层函数都可能有隐式类型转换嵌套越深隐式转换次数越多而每次隐式转换都是计算资源的消耗。虽然单个转换的代价很小但乘以百万行数据后累计开销就不可忽略了。6.3 聚合查询的优化能提前过滤就提前过滤聚合函数配合GROUP BY写统计查询时很多人会忽视过滤顺序对性能的影响。WHERE在分组前过滤HAVING在分组后过滤。如果你的过滤条件不涉及聚合结果应该放在WHERE里这样参与分组的行数少了聚合计算量自然就小了。举个例子SELECT 部门, COUNT(*) AS 员工数 FROM 员工表 WHERE 状态 在职 GROUP BY 部门;这个写法比HAVING COUNT(*) 0的过滤方式性能更好因为WHERE先过滤掉了离职员工只有在职员工参与分组。老手写聚合查询的一个习惯是总会看一眼WHERE和HAVING各过滤了多少行在EXPLAIN或执行计划里确认每一步的数据量级避免无谓的计算。另外COUNT(DISTINCT 字段)也是一个容易产生性能瓶颈的用法。这个函数要精确计算去重后的数量代价非常高。如果业务上能接受近似值可以考虑用APPROX_COUNT_DISTINCTSQL Server 2019大数据量下速度极快但结果会有极小的偏差。但要注意这种近似函数在结果精度要求极高的财务场景里不能乱用。7. 常见问题与排查技巧实录7.1 字符串函数相关的典型报错与奇怪结果SQL Server里CONCAT不支持多个参数。很多人从MySQL转到SQL Server后习惯性写CONCAT(字段1, 字段2, 字段3)却发现SQL Server的CONCAT只支持两个参数。我在团队带人时反复强调过SQL Server的字符串拼接用号MySQL的CONCAT支持多个参数。跨数据库写SQL时这是第一个容易踩的坑。字符串截取结果比预期少一位。前面提过SUBSTRING的起始位置从1开始还有一个问题是中文和英文字符混用时长度不好把握。SQL Server的SUBSTRING按字符计数中文和英文都算一个字符但如果你用LEFT截取字节在MySQL里可以配合CHAR_LENGTH和LENGTH的差异来排查。多个空格导致LIKE匹配不中。查询时明明感觉数据里有这个关键字但LIKE %关键词%就是查不到。原因往往是数据里混了全角空格或不可见字符。排查办法是先排除空格干扰SELECT * FROM 文章表 WHERE REPLACE(REPLACE(标题, , ), CHAR(9), ) LIKE %关键词%;如果这样能查到那大概率是特殊字符问题。另外可以查看这个字段的LENGTH和CHAR_LENGTH差异如果差异很大说明字段里含有大量空格或特殊字符。大小写比较导致匹配失败。默认情况下SQL Server的排序规则可能是大小写不敏感的但有的数据库实例大小写敏感。排查时先确认数据库的排序规则再决定是否要统一LOWER或UPPER后再比较。7.2 日期函数相关的典型报错与数据偏差**CONVERT转换失败导致查询报错**。字符串转日期时格式不对SQL Server会直接抛出 conversion failed 错误。排查思路是先把异常数据找出来再用TRY_CONVERTSQL Server 2012替代CONVERT这样转换失败时会返回NULL而不是报错。时区问题导致的数据偏差。数据库服务器的时区设置会直接影响日期函数的结果尤其是用了GETDATE()或NOW()这类“当前时间”函数时。如果应用服务器和数据库服务器在不同时区就会出现取到的日期差一天的情况。排查方式是先确认各服务器的时区配置统一把时间存储为UTC时间展示层再做时区转换。跨数据库的日期函数差异导致的移植报错。从MySQL迁移到SQL Server时日期函数基本全部要改NOW()要改成GETDATE()DATE_FORMAT要改成CONVERTDATE_ADD要改成DATEADD。我在做数据库迁移项目时专门列过一个函数映射表每次迁移前先用静态扫描工具找出所有用到日期函数的代码再逐条替换防止遗漏。7.3 一个完整的排查实例慢查询中的函数陷阱最后分享一个真实的排查过程这是几个月前帮客户优化系统时遇到的。客户反馈某个管理后台的报表页面越跑越慢数据量只有几十万行但每次查询要十几秒。我拿到的SQL大致是这样的SELECT FORMAT(创建时间, yyyy-MM-dd) AS 日期, COUNT(*) AS 订单数 FROM 订单表 WHERE YEAR(创建时间) 2025 AND MONTH(创建时间) 1 GROUP BY FORMAT(创建时间, yyyy-MM-dd);一眼扫过去至少三个问题FORMAT在分组里用性能极差YEAR()和MONTH()套在索引列上导致索引失效同一字段算了两遍FORMAT却没有复用。我把SQL改成这样SELECT CONVERT(varchar(10), 创建时间, 23) AS 日期, COUNT(*) AS 订单数 FROM 订单表 WHERE 创建时间 2025-01-01 AND 创建时间 2025-02-01 GROUP BY CONVERT(varchar(10), 创建时间, 23);改动并不复杂但查询时间直接从12秒降到0.3秒。客户的反馈是“像换了一套系统”。这个案例里用到的排查思路就是在执行计划里看每一步的操作类型和行数发现全表扫描和Compute Scalar操作占了大头然后顺藤摸瓜找到了那几个函数陷阱。这类问题的共性也很统一不是SQL写不出来而是写得太“顺手”没有考虑数据库底层是怎么执行这些函数的。正如前面反复提到的写SQL是要带着“数据库视角”去写的你写下的每一行代码最终都会变成数据库的每一步执行计划。8. 写在最后函数不是背出来的是练出来的这个系列的第一篇就到这里。说实话写这种基础性的内容反而比写高深的技术难点更费劲因为越基础的东西越难讲出新意也越容易被“以为懂了”的心态带过去。我自己的经验是SQL函数掌握得扎不扎实不看你能不能背出所有函数的语法而是看你在实际场景里能不能第一时间想到“这里应该用哪个函数、有哪些边界情况要注意”。如果你刚接触SQL不久我的建议是先别急着啃那些复杂的优化技巧把常用函数过一遍用自己手头的真实数据跑一遍看看不同函数在边界条件下的行为差异。如果你已经写了两三年SQL建议找个周末把自己最近写的查询翻出来专门检查一遍里面有没有隐式转换、有没有在索引列上套函数、有没有用FORMAT写大报表——这些都是能立刻带来性能提升的改进点。下一篇我会继续讲SQL函数里更进阶的部分包括条件逻辑相关的函数CASE WHEN的深度用法、IIF、NULLIF、JSON与XML的处理函数、以及跨数据库的函数兼容性问题。欢迎关注这个系列有什么想了解的具体问题也可以在评论区留言我会挑典型的写进后续的文章里。
