★ FEATURED ARTICLE 1. 字符串处理的基础必修课为什么substring是每个SQL开发者的基本功干了这些年数据开发和报表需求我越来越觉得SQL里的字符串函数属于典型的平时没人重视、关键时刻卡脖子的知识点。业务方丢来一句帮我把这个字段里的某段字符取出来你要是只会写个WHERE条件当场就得愣住。而substring有些数据库叫substr就是处理这类需求的第一板斧也是后续学习regexp_substr、split_part这些进阶函数前必须吃透的基础。先说清楚这函数究竟解决什么问题。一句话从一个字符串里按位置、按长度切出一段子串。听起来简单但真正复杂的是不同数据库的语法细节差异、边界条件的处理、以及和定位函数搭配使用时的场景变化。这些坑我在实际项目里几乎全踩过所以这篇打算把substring从语法、差异、实战到性能、避坑完整梳理一遍。这篇内容适合谁看如果你是刚接触SQL的学生或转行做数据分析的新人能帮你把字符串截取这块的技能树一次性点亮如果你是从Oracle转MySQL、或者从SQL Server迁PostgreSQL的开发者这篇里的差异对照表能直接帮你少走弯路。文章里的示例我尽量用通用的写法再标注各数据库的差异方便你对照自己的环境去测试。2. 语法拆解与多数据库差异同一句话五种写法2.1 标准语法和几个容易忽略的细节ANSI SQL标准里定义的substring是这样的SUBSTRING(字符串表达式 FROM 起始位置 [FOR 长度])注意这个写法用的不是逗号而是FROM和FOR关键字很多人在书上看过但实际从不这么写因为各数据库对标准形式的支持程度不太一样。更常见的写法是SUBSTRING(字符串表达式, 起始位置, 长度)三个参数的含义分别是要截取的原字符串、从第几个字符开始、截取多长。有几个边界细节必须清楚第一起始位置的索引从1开始不是从0开始。这是新手最容易犯的错误写了个SUBSTRING(abcde, 0, 2)以为能拿到ab结果拿到了空字符串有些数据库会把它当成从位置1开始处理但千万别依赖这种差异。第二长度是可选的。如果只给起始位置不给长度那就从起始位置一直截到字符串末尾。比如SUBSTRING(abcdef, 3)结果是cdef。第三起始位置可以是负数吗这就是各数据库打架最狠的地方。MySQL里SUBSTRING(abcde, -2)会从倒数第2个字符开始截结果是de但SQL Server里直接报错Oracle的SUBSTR则支持负数从尾部向前数。这种差异稍后单独拉个表说。2.2 主流数据库的substring函数写法对照我在实际工作中接触过不下五种数据库这里把最常用的几个拉出来做个对照方便你迁移时心里有数。数据库函数名长度参数支持负数起始位特殊行为MySQLSUBSTRING / SUBSTR可选支持从尾部倒数支持不带长度的截取PostgreSQLSUBSTRING / SUBSTR可选不支持支持标准的FROM...FOR语法SQL ServerSUBSTRING必填不支持长度不足时返回到末尾不报错OracleSUBSTR可选支持从尾部倒数长度传0返回空串SQLiteSUBSTR必填支持长度不足返回剩余部分这张表里我特别提醒两处。第一处是SQL Server的SUBSTRING长度参数是必填的不写长度直接报错这和MySQL、PostgreSQL的缺省截到末尾完全不同。第二处是Oracle的SUBSTR如果长度传了0返回空字符串但传负数会报错别踩混了。2.3 各数据库的正向写法与一个通用兜底方案如果你要写一段能在多个数据库上跑的通用逻辑最保险的写法是全部显式传三个参数起始位置用正数-- 严格传三个参数避免缺省行为差异 SELECT SUBSTRING(column_name, 3, 5) FROM table_name;这段在MySQL、PostgreSQL、SQL Server、Oracle四个库里都能跑Oracle拼写是SUBSTR注意区分。如果项目中既有MySQL又有Oracle混着用我一般建议封装成视图或者统一数据访问层去处理别在业务SQL里到处裸写函数。另外要说的是PostgreSQL对标准语法的支持最到位写SUBSTRING(name FROM 2 FOR 3)这种完全合规。MySQL也支持这种写法但SQL Server和Oracle不支持。我在跨库迁移时如果碰到这种写法会统一先批量改成逗号分隔的三参数形式省得一边迁移一边报错。3. 字符串截取的定位搭档CHARINDEX、INSTR、POSITION的使用差异3.1 为什么不直接写死起始位置实践中绝大多数截取需求起始位置都不是一个固定的常数而是要根据某些标识字符去动态定位。比如从邮箱地址里截用户名的位置在每行都不一样或者从文件路径里截文件名最后一个斜杠的位置也各不相同。这时候substring必须配合定位函数使用。各数据库的定位函数名称差异很大SQL Server和MySQL用CHARINDEXMySQL也支持LOCATE和INSTRPostgreSQL支持标准SQL的POSITION也可以strposOracle用INSTR。功能都一样在一个字符串里找另一个字符串出现的位置返回整数下标找不到时返回0PostgreSQL的strpos找不到时返回0但POSITION找不到也返回0MySQL的LOCATE返回0。这里有个细节charindex支持从指定位置开始找的第三参数而instr是按照起始位置作为第二参数的顺序不一样写的时候特别容易搞混。3.2 截取邮箱用户名的实操写法实战场景从user_email字段里取前面的部分-- MySQL写法 SELECT user_email, SUBSTRING(user_email, 1, LOCATE(, user_email) - 1) AS username FROM users; -- SQL Server写法 SELECT user_email, SUBSTRING(user_email, 1, CHARINDEX(, user_email) - 1) AS username FROM users; -- PostgreSQL写法 SELECT user_email, SUBSTRING(user_email FROM 1 FOR POSITION( IN user_email) - 1) AS username FROM users;这里有个关键点容易漏定位函数返回的是标识字符所在的位置而我们截取时要排除掉这个字符本身所以要减1。如果忘了减会把也带进结果里。这种细节看文档根本记不住只有真跑一次数据看到结果不对才记得牢。3.3 复合场景动态定位截取文件名从一个完整文件路径里截取文件名比如/data/uploads/2024/report_final.pdf要拿到report_final.pdf方法是找最后一个斜杠的位置然后从它后面一位开始截。MySQL里可以用LENGTH配合REVERSE实现也可以直接用SUBSTRING_INDEX这个神器后面细说。这里展示一下相对通用的组合写法-- MySQL取最后一个斜杠后面的内容 SELECT file_path, SUBSTRING( file_path, LENGTH(file_path) - LOCATE(/, REVERSE(file_path)) 2 ) AS file_name FROM file_table;这个公式略微有点绕拆开解释先反转整个路径用LOCATE找反转后第一个斜杠的位置这个位置其实就是原路径最后一个斜杠距离末尾的距离用总长度减掉它再加2加1跳到斜杠后一位再调整边界就得到最后一个斜杠后面的起点。这是典型的用函数组合解决复杂定位的思路写一次理顺了后面碰到类似需求都能套用。4. 实战案例库从数据清洗到报表统计的六个典型场景4.1 结构化文本字段的拆分业务系统里常常把多个信息塞进一个字段用分隔符串联。比如某日志表有个raw_info字段存的是订单号#商品名称#金额#支付方式现在要单独取金额出来做汇总。MySQL直接上SUBSTRING_INDEXSELECT raw_info, SUBSTRING_INDEX(SUBSTRING_INDEX(raw_info, #, 3), #, -1) AS amount FROM order_logs;这个嵌套写法的原理内层SUBSTRING_INDEX(raw_info, #, 3)取的是前3段也就是订单号#商品名称#金额外层SUBSTRING_INDEX(..., #, -1)再从结果里取最后一段也就是金额。两层嵌套解决取第N段的问题属于非常经典的处理手段。如果是PostgreSQL用split_part(raw_info, #, 3)一步到位SQL Server麻烦一些可以用PARSENAME但只支持点号分隔或写个自定义函数。4.2 从日期时间字符串里提取时间维度有时候时间字段不是标准DateTime类型而是存成了2024-05-18 14:32:08这种字符串甚至更乱的格式。要进一步按小时做统计就得先切出小时部分-- 从日期时间字符串里取小时段 SELECT time_str, SUBSTRING(time_str, 12, 2) AS hour_part FROM event_table;这个写法的前提是你明确知道字符串的固定格式前10位是日期第12位开始是小时。但如果是2024/5/18 14:32:08这种不固定位数直接按位置截就废了得先想办法统一格式或者用正则。所以我反复强调用substring按固定位置截取前必须先确认数据格式的一致性否则截出来的东西五花八门。4.3 清洗用户输入的电话号码用户注册时填写的手机号经常带着乱七八糟的符号比如86 138-1234-5678、(010) 12345678、138 1234 5678。要做标准化存储第一步往往是把数字以外的字符去掉然后取有效的号码段。虽然更优雅的方案是正则替换但纯SQL场景下也可以结合REPLACE和SUBSTRING做处理-- 先去掉常见格式符号再截取最后11位示例实际按业务规则调整 SELECT phone_input, SUBSTRING( REPLACE(REPLACE(REPLACE(REPLACE(phone_input, -, ), , ), , ), (, ), -11 ) AS clean_phone FROM user_contact;MySQL这里用到了负数的起始位置直接取最后11位SQL Server就得换成RIGHT(phone_input, 11)或者先计算字符串长度再定位。这种需求里最重要的不是函数本身而是想清楚你打算怎么处理异常数据比如号码本身不足11位或者包含字母等非法字符。4.4 多级分类编码的逐层拆解很多系统里层级分类是用编码表示的比如01-02-003表示一级分类01下二级02下的三级003。做报表时需要按每一层分别透视就得把每一段截出来。MySQLSELECT category_code, SUBSTRING_INDEX(category_code, -, 1) AS level1, SUBSTRING_INDEX(SUBSTRING_INDEX(category_code, -, 2), -, -1) AS level2, SUBSTRING_INDEX(category_code, -, -1) AS level3 FROM product_category;这套逻辑我在好几个报表需求里都用过不用改业务表结构就能支撑按层级下钻的交互展示。PostgreSQL的split_part更直观split_part(category_code, -, 1)、split_part(category_code, -, 2)、split_part(category_code, -, 3)。SQL Server建议先写个拆分的表值函数或者直接用开窗配合STRING_SPLIT去展开不过STRING_SPLIT在更早版本里没有。4.5 隐藏敏感信息中间四位打码用户列表展示时手机号中间四位要打码这种需求十有八九逃不掉substringSELECT phone, CONCAT( SUBSTRING(phone, 1, 3), ****, SUBSTRING(phone, 8) ) AS masked_phone FROM user_table;这里用了两个截取片段再加一个掩码串拼接的方式。注意这里的SUBSTRING(phone, 8)是从第8位截到末尾的MySQL写法SQL Server必须补第三参数写成SUBSTRING(phone, 8, 4)。打码这类操作我建议在SQL层做而不是在应用层做一个是统一逻辑另一个是SQL层处理不占用应用服务器资源效率上更合理。4.6 关联键提取从复合字段里抽业务ID有个订单表订单编号规则是ORD20240518001前三位是前缀中间8位是日期后三位是序列号。有一天业务要按每天新增订单数出报表就要先把日期那8位截出来SELECT order_no, SUBSTRING(order_no, 4, 8) AS order_date FROM orders WHERE order_no LIKE ORD%;我在实际优化这个场景时还会加一个索引友好的写法如果表数据量很大不要直接在SUBSTRING(order_no, 4, 8)上做GROUP BY的同时还想走索引这种写法索引大概率失效。更稳的做法是在ETL阶段就把order_date单独计算成一列并建索引查询时直接GROUP BY这列。这也算substring实战中的一条性能铁律能用预处理就把截取逻辑下沉到加工层别在报表查询里大量现算。5. 性能优化与索引失效一次截取引发的全表扫描5.1 在WHERE条件里用substring的巨大代价很多人一开始图省事直接在WHERE条件里用substring。比如想查所有订单号第4位开始的8位等于某个日期的数据-- 危险写法导致全表扫描 SELECT * FROM orders WHERE SUBSTRING(order_no, 4, 8) 20240518;这个写法不是不能跑而是当表里数据量上了千万级别它会让查询规划器放弃索引走全表扫描性能直接崩。原因很简单索引列上套了函数后索引本身的有序性对查询没用了数据库没法利用B树快速定位。优化方案是改写为范围查询-- 利用前缀匹配走索引 SELECT * FROM orders WHERE order_no LIKE ORD20240518%;这种写法数据库可以直接利用order_no的前缀索引进行定位性能天差地别。我在一个模拟项目X里验证过一千万行数据用SUBSTRING的条件查询耗时接近3秒改成LIKE 前缀%后耗时降到几十毫秒差距接近两个数量级。5.2 能不能给substring表达式建索引如果你确实没办法改造SQL还是有别的出路MySQL 8.0支持函数索引可以给表达式建索引。比如-- MySQL 8.0 允许对表达式建立索引 CREATE INDEX idx_order_no_substr ON orders ((SUBSTRING(order_no, 4, 8)));PostgreSQL也支持表达式索引写法类似。但这类索引维护成本高每次DML操作都要同步计算表达式值写入性能会有一定损耗。我的建议是数据量小随便用数据量大优先改造查询条件实在不行再考虑表达式索引。5.3 隐式类型转换的隐藏坑还有一类性能问题是substring截取结果是字符串如果拿来和数字比较可能触发隐式类型转换又让索引失效。比如-- 截取结果是字符串和数字比较会转换类型 SELECT * FROM orders WHERE SUBSTRING(order_no, 4, 8) 20240518;这里SUBSTRING返回的是字符串20240518是数字数据库要统一类型通常会把字符串转数字再比较转的过程可能会导致无法走索引。解决办法很简单保持类型一致右边也写成字符串20240518。6. 常见报错与结果异常六个高频问题和排查方案6.1 起始位置为0或者负数时的行为差异这是跨数据库最坑的地方。MySQL的substring起始位置为0时会被当作从位置1开始处理所以SUBSTRING(abcde, 0, 2)返回空串还是ab有版本差异的隐患Oracle的SUBSTR(abcde, 0, 2)返回ab因为Oracle把0当成了1PostgreSQL和SQL Server起始为0就是空。负数方面MySQL支持负数从尾部数但SQL Server直接报错。排查建议先确认项目用的什么数据库再写测试SQL验证行为千万不要凭经验把一套写法搬到另一个库上。6.2 截取长度超出字符串剩余长度不同数据库的处理策略也不同。SQL Server如果长度超出剩余字符数返回剩余部分不报错MySQL也返回剩余部分Oracle同样不报错。所以整体来说这不算大坑但要注意截取结果可能不是预期长度做数据对比时容易对不上。6.3 中文字符与字节的截取混乱这是国内开发者最常踩的坑。MySQL里CHARACTER_LENGTH按字符计数但某些老写法用LENGTH它按字节计数中文在UTF-8下一个字符占3字节。如果直接SUBSTRING(name, 1, 3)想截前三个字符实际MySQL的substring也是按字符处理的不会按字节截断这点还好但如果你先用了LENGTH做边界计算再把结果喂给substring就会出乱码。PostgreSQL的substring也是按字符处理默认没有字节截断问题。SQL Server的SUBSTRING同样按字符。真正按字节的是一些老数据库的SUBSTRINGB之类的函数碰到时看清楚函数名。排查中文字段截取异常时第一件事就是确认你用的函数是字符版的还是字节版的第二件事是确认LENGTH用的是字符还是字节单位。6.4 嵌套定位函数后取到空字符串嵌套场景里最常见的问题是定位函数找不到目标字符返回0然后你拿来减1变成负数起始位不同数据库结果还不同。比如SELECT SUBSTRING(email, 1, LOCATE(, email) - 1) FROM users;万一某行的email里没有LOCATE返回0减1变成-1MySQL当成从倒数第一位开始截截出来的东西完全不对。正确做法是先加条件过滤掉脏数据或者用CASE WHEN兜底SELECT email, CASE WHEN LOCATE(, email) 0 THEN SUBSTRING(email, 1, LOCATE(, email) - 1) ELSE NULL END AS username FROM users;这类问题在数据质量差的表里特别常见查出来的数据会让业务方一头雾水排查起来费时费力不如一开始就把防御性写法加上。6.5 函数名拼写引发的不兼容问题MySQL里SUBSTRING和SUBSTR是同一个意思可以混用Oracle只能用SUBSTR没有SUBSTRINGPostgreSQL两个都有但SUBSTR是标准substring的别名SQL Server只有SUBSTRING。如果你在Oracle里写了SUBSTRING直接报无效的关系运算符之类的错误排查思路就是先把函数名对齐到当前数据库方言。6.6 隐式空格和不可见字符导致的截取结果异常有时候你看一段字符串开头好像有空格用substring从第1位截结果多截出了空格导致后续比较和展示都对不上。这种情况下建议先TRIM/LTRIM/RTRIM去空格或者用REPLACE去掉换行符、制表符等不可见字符。操作之前先用HEX或DUMP这类函数检查字符的实际编码能节约大量排查时间。我自己调试过一个从外部导入的数据文件字符串里夹着全角空格和零宽字符查了一个多小时才发现是这种看不见的脏字符。7. 工具选型解析与思考什么时候该放弃SUBSTRING7.1 正则表达式更灵活但更昂贵如果截取规则很复杂比如取出括号里的内容提取所有连续数字SUBSTRING就捉襟见肘了这时候上REGEXP_SUBSTRMySQL 8.0、Oracle支持、regexp_substrPostgreSQL通过regexp_matches实现、SQL Server用STRING_SPLIT加正则或PATINDEX来辅助。正则的好处是表达能力强坏处是性能开销大尤其是大表上频繁调用CPU消耗比普通substring高出很多。我的经验是能用固定位置或固定分隔符解决的就别动用正则正则留给那些确实字符串格式千变万化的场景。7.2 专门的拆分函数SUBSTRING_INDEX与SPLIT_PARTMySQL的SUBSTRING_INDEX和PostgreSQL的SPLIT_PART在按固定分隔符取第N段这件事上比多层嵌套substring可读性和性能都要好。我在前面4.1和4.4里已经用了这种方法。注意SQL Server没有等价函数新版提供了STRING_SPLIT但它做的是把字符串展开成多行不是直接取某一列的第N段需要配合行号去过滤写起来没那么直觉。7.3 NOT NULL与长度校验先想到数据防御这是我要强调的观念截取函数用之前先想清楚数据可能长什么样。比如截取身份证号里的出生日期就要考虑有些数据可能位数不足、可能有X结尾、可能有空格。我的做法是上线前先跑一遍统计SQL把字段长度的分布情况摸清楚再决定截取方案的边界逻辑。-- 上线前先检查长度分布 SELECT LENGTH(id_card) AS len_val, COUNT(*) AS cnt FROM user_info GROUP BY LENGTH(id_card) ORDER BY cnt DESC;这种预防性检查配合防御式SQL写法能让你的代码稳定性明显上一个台阶也能少挨几次业务方的抱怨。8. 实战踩坑记录与经验心得8.1 一次跨库迁移引发的批量替换之前做过一个从MySQL迁往PostgreSQL的系统一堆历史SQL里用了SUBSTRING(col, pos, len)到处截取。表面看两边语法一致但MySQL支持的负数起始位PostgreSQL不支持结果某些边界数据在迁移后开始报错或返回空值。排查过程特别折腾最后只能写个脚本把这些负数参数的写法全部改成先计算长度的等价形式再重新跑回归测试。这事给我的教训很深字符串函数的边界行为差异比语法差异更容易埋雷。语法不兼容顶多报错一眼就能看到边界行为不一致直接导致数据错乱还可能悄无声息。8.2 报表层大量截取导致接口超时的优化过程之前一个报表接口每次查询都在千万级明细表上做十几次substring截取外加好几层嵌套子查询线上接口平均响应时间超过8秒。优化方案分了三步走第一把明细表里的关键截取字段在ETL阶段提前算好落成独立字段并建索引第二把原来大批量的substring计算移到物化视图中或汇总表里查询只面对加工后的宽表第三把报表查询改成按日期分区裁剪数据量缩小后再做计算。最终接口响应降到200毫秒以内。核心思想就一条计算尽量前置存储尽量冗余。在OLAP场景里用空间换时间是再划算不过的买卖。8.3 习惯性加防御条件的意义我后来写SQL有一个习惯凡是用了定位函数的地方一定想到目标字符可能不存在先在WHERE或CASE WHEN里过滤掉凡是用了截取函数的地方一定想到长度可能不足先确认截出来的是否还是完整的概念单元。这套防御习惯看着像是多写了不少代码实际能省下大量返工和排查的时间。数据质量从来不是天生的而是靠一层层防御兜出来的。回到文章开头那句话substring是SQL字符串处理的基石也是从会写SQL走向写好SQL的一个分水岭。把它的边界踩明白把不同数据库的差异记清楚再养成防御性编码的习惯你会发现在海量数据处理中这种基本功比很多花哨的框架技巧更抗打。最后分享一个我自己的小习惯每接触一个陌生的数据库先写十条字符串函数的测试SQL跑一遍把返回结果记到笔记里。耗时不到半小时但后面写任何复杂查询心里都有底。这份方言行为对照表就是我踩过那么多次坑之后最想提醒你尽早准备的东西。
阅读完成 · 觉得有帮助？
