简介一份面向数据分析师、后端开发与数据库运维人员的MySQL字符串处理PDF手册聚焦字符串截取和指定数据获取。内容以substring_index()为核心讲解如何利用分隔符与index正负向定位从类似 ProviderRiskManagement finalScore65 RGID100397278 的字段中提取finalScore值同时覆盖left()、right()、substring()等常用函数及典型写法。文档共1个PDF文件约48KB示例直接、说明简洁便于快速查阅。目前已有8959人学习下载。通过这份文档读者能系统掌握不同截取函数的适用场景理解substring_index()多层嵌套的用法并学会在数据清洗、报表取数和ETL过程中灵活处理字符串。1. 字符串截取在 MySQL 里不是拼函数是拼边界很多老工程师遇到“从字段里取出中间一段”的需求第一反应是百度一个函数然后一把梭。但 MySQL 字符串截取真正难的不是 SUBSTRING 怎么写而是“边界”怎么定——从哪里开始、遇到哪个分隔符停、分隔符本身要不要留。这篇文章围绕 Mysql 字符串截取及获取指定字符串中的数据这组需求展开覆盖 SUBSTRING、LEFT/RIGHT、SUBSTRING_INDEX、LOCATE 和正则提取的实际写法与参数调优。适合写报表要拆日志、做 ETL 拆冗余字段、面试前突击 mysql 字符串函数的开发。跟字符串截取有关的血泪经验在这里一次讲完。2. SUBSTRING 与 LEFT/RIGHT先搞清楚起点和长度再截取2.1 SUBSTRING 的三种写法与索引从 1 开始的坑SUBSTRING 是 MySQL 字符串截取的基础函数支持两种参数形式。第一种是SUBSTRING(str, pos)从第 pos 位一直取到字符串末尾第二种是SUBSTRING(str, pos, len)从第 pos 位往后数 len 个字符。这里的 pos 从 1 开始不是 0跟 Java、JavaScript 里 substring 的语义完全不同老手也容易在这里栽跟头。-- 从第 2 位取到末尾 SELECT SUBSTRING(MySQL, 2); -- ySQL -- 从第 3 位开始取 2 个字符 SELECT SUBSTRING(MySQL, 3, 2); -- SQ -- 等价写法SUBSTR 和 MID 是 SUBSTRING 的同义词 SELECT SUBSTR(MySQL, 3, 2); -- SQ SELECT MID(MySQL, 3, 2); -- SQ这段代码里三条查询的结果分别是ySQL和两次SQ。注意MID是历史遗留别名MySQL 里依然保留但新代码里不建议混用统一写SUBSTRING可读性更好。参数pos支持负值表示从字符串尾部倒着数比如SUBSTRING(MySQL, -2)取的是QL。查询里我最常被问到的一个问题是为什么SUBSTRING(MySQL, 1, 1)取的是 M而SUBSTRING(MySQL, 0, 1)返回空原因很简单pos 的起点是 1写成 0 时 MySQL 会把它当成非法起点处理整个表达式返回空字符串但不会报错。这种“静默返回空”的行为相当隐蔽在存储过程或报表 SQL 里排查起来非常耗时间。所以写截取逻辑前先把起点是正数还是负数、从第几位开始在注释里标清楚。2.2 LEFT 和 RIGHT适合固定宽度前缀/后缀如果目标数据固定在字符串的头部或尾部用 LEFT 和 RIGHT 比 SUBSTRING 更直白。LEFT(str, len) 取左边 len 个字符RIGHT(str, len) 取右边 len 个字符。它们的语义完全对称没有起点概念也不存在 pos 写成 0 的坑。-- 从左侧取 4 个字符 SELECT LEFT(2024-06-01, 4); -- 2024 -- 从右侧取 5 个字符 SELECT RIGHT(2024-06-01, 5); -- 06-01 -- 固定宽度截掉前 4 位 SELECT RIGHT(PO20240601001, 11); -- 20240601001常见的场景是处理日期字符串和流水号。比如订单号PO20240601001前缀 PO 加年月日再加序号要取中间序号部分可以先做一次替换再截取或者直接用 RIGHT 从右侧取 11 位。这里隐藏一个容易忽略的点RIGHT 按字符数取不是按字节数取所以对中文和英文混排的字符串同样安全。LEFT 和 RIGHT 的局限在于只能处理固定在边界的子串。如果边界是动态的比如两个横杠之间夹着的数字它们就无能为力了。这时候要么先算出位置再配合 SUBSTRING要么直接用 SUBSTRING_INDEX。下一个小节讲的就是动态定起点的方法。2.3 用 LOCATE 动态定位起点告别写死的 start写截取 SQL 最忌讳把位置写死因为源数据一旦加了一个前缀整条语句就作废。动态定位的核心是先用 LOCATE 找到目标子串出现的位置再把这个位置当起点传给 SUBSTRING。LOCATE(substr, str) 返回第一次出现的下标找不到时返回 0。-- 找到 - 第二次出现的位置 SELECT LOCATE(-, 2024-06-01); -- 5 -- 从指定位置继续找第三个参数是搜索起点 SELECT LOCATE(-, 2024-06-01, 6); -- 8 -- 动态截取取第二个横杠之后的内容 SELECT SUBSTRING(2024-06-01, LOCATE(-, 2024-06-01, 6) 1); -- 01LOCATE 的第三个参数是搜索起点这是实现“跳过一个分隔符”的关键。要取第二个横杠后面的内容先算第二个横杠的位置再加 1 作为 SUBSTRING 的起点。这里特别注意LOCATE 返回的位置是相对于整个字符串的不是相对于搜索起点的所以LOCATE(-, 2024-06-01, 6)返回 8而不是 3。INSTR(str, substr) 和 LOCATE 的区别只是参数顺序反了结果一致。用哪个取决于你哪只手更顺团队规范里统一一个即可。动态定位方案是后面所有复杂截取的基础也是 mysql 面试题里经常考察的组合考点——单独问函数谁都认识组合使用才能看出对字符串边界的理解。3. 获取指定字符串中的数据SUBSTRING_INDEX 是拆分主力3.1 正向取、反向取的参数语义SUBSTRING_INDEX(str, delim, count) 是我做数据清洗时用得最多的函数它的设计思路很巧妙按分隔符把字符串切开count 为正数时返回左侧前 count 段count 为负数时返回右侧最后 count 段注意顺序是从右往左数的第几段方向仍是原始字符串的左侧到右侧方向返回结果里分隔符保留。-- 正向取返回第一个逗号前的部分 SELECT SUBSTRING_INDEX(a,b,c, ,, 1); -- a -- 正向取返回前两段保留分隔符 SELECT SUBSTRING_INDEX(a,b,c, ,, 2); -- a,b -- 反向取返回最后一段 SELECT SUBSTRING_INDEX(a,b,c, ,, -1); -- c -- 反向取返回最后两段 SELECT SUBSTRING_INDEX(a,b,c, ,, -2); -- b,c注意SUBSTRING_INDEX(a,b,c, ,, 2)返回a,b分隔符还在。很多人以为它会自动去掉最后的逗号其实不会需要时得手动加一层替换或再截取。而负数的语义是“从右往左数第 count 段之前的全部内容”不是“取右边 count 个字符”这一条区分清楚后面嵌套才不会乱。count 的值还会影响找不到分隔符时的行为。如果字符串里根本没有分隔符函数返回整个字符串。这个特性既是方便也是坑——做数据校验时分隔符缺失会让它静默返回整段脏数据所以用之前先确认源数据的完整性。3.2 经典实践日志字段按逗号拆分实际生产里最典型的场景是从日志或接口返回里拆字段。假设有一张登录日志表client_info 字段存的是手机号:15319144272,设备:iPhone15,登录状态:成功,登录时间:2024-06-01 10:22:33要提取手机号和登录状态两层 SUBSTRING_INDEX 就能解决。-- 提取手机号先取第一个逗号前的部分再取冒号后的部分 SELECT SUBSTRING_INDEX( SUBSTRING_INDEX(手机号:15319144272,设备:iPhone15,登录状态:成功, ,, 1), :, -1) AS phone; -- 提取登录状态先取第三个逗号前的部分再取冒号后的部分 SELECT SUBSTRING_INDEX( SUBSTRING_INDEX(手机号:15319144272,设备:iPhone15,登录状态:成功, ,, 3), :, -1) AS login_status;第一层 SUBSTRING_INDEX 按逗号切出目标片段第二层按冒号取最后一段。这里用: , -1而不是: , 2是为了避免冒号后面还有冒号时取错。比如设备字段如果写的是设备:iPhone:15 Pro取:, 2会返回iPhone:15 Pro而:, -1只返回15 Pro。这是实践里很实用的技巧处理键值对格式时分隔符后面永远用 -1。SUBSTRING_INDEX 嵌套的层数没有硬性限制但嵌套超过三层以上的 SQL 已经很难读。遇到这种情况我一般先写一层子查询把中间结果算出来再让外层 SQL 继续截取这样每一层都看得懂排查时也能单独跑中间结果验证。3.3 多层嵌套拆出“倒数第二段”有些字符串的结构是a-b-c-d你需要倒数第二段c。直接SUBSTRING_INDEX(str, -, -2)拿到的是c-d还要再甩掉最后一个-d。处理方式是把“取倒数第二段”拆成两步先丢掉最后一段再取新字符串的最后一段。SET s 2024-06-01-订单号-待发货; -- 第一步丢掉最后一段剩下 2024-06-01-订单号 SELECT SUBSTRING_INDEX(s, -, -2); -- 订单号-待发货 -- 第二步取上一步结果的最后一段 SELECT SUBSTRING_INDEX( SUBSTRING_INDEX(s, -, 3), -, -1) AS middle_part; -- 订单号第二种写法的思路是先用正向 count3 把前三段都留下来再反向取最后一段。这样拿到的就是原始字符串的倒数第二段。如果字段被分隔符拆出的段数不固定这种做法可能取错对象需要先统计段数用 LENGTH 减去替换后的长度算出分隔符数量再加 1 就是总段数。统计段数的表达式是LENGTH(str) - LENGTH(REPLACE(str, -, )) 1。这里如果存的是中文LENGTH 会按字节数算中文字符在 utf8mb4 下占 3 个字节分隔符如果也是中文统计就会翻车。先确认字符集再把函数换成 CHAR_LENGTH 和 REPLACE 配合否则计算出的段数永远是算出来的两倍。3.4 特殊分隔符的转义处理SUBSTRING_INDEX 的分隔符参数是一个普通字符串不是正则表达式所以原则上不需要转义。但有几个边界情况需要特别处理分隔符是空字符串时函数返回空分隔符是连续字符如::时按整体匹配不会拆成两个冒号分隔符本身包含%或_时不做通配符处理。-- 连续分隔符按整体匹配 SELECT SUBSTRING_INDEX(a::b::c, ::, 2); -- a::b -- 分隔符是多个字符只取第一个分隔符前的部分 SELECT SUBSTRING_INDEX(order_noPO-2024, , 1); -- order_no -- 分隔符在字符串开头时正向取第一段返回空 SELECT SUBSTRING_INDEX(-abc, -, 1); -- 如果分隔符是竖线|这类在 LIKE 里有特殊含义的字符SUBSTRING_INDEX 照样按普通字符串处理。真正需要转义的是正则场景也就是第 4 章要讲的 REGEXP_SUBSTR那里面的括号、点号、星号全部要按正则语法写。很多人把 SUBSTRING_INDEX 的分隔符习惯带到正则里结果点号变成通配符匹配出来的数据宽了一大截这种翻车几乎每周都能在工单里见到。4. 正则提取MySQL 8.0 的 REGEXP_SUBSTR 与 REGEXP_REPLACE4.1 正则函数能替代多少手工拼接MySQL 8.0 引入了 REGEXP_SUBSTR、REGEXP_REPLACE、REGEXP_INSTR 三个正则函数把以前要在应用层做的字符串解析搬到了 SQL 里。REGEXP_SUBSTR(str, pattern) 返回第一个匹配到的子串REGEXP_REPLACE(str, pattern, replacement) 把匹配到的部分替换成指定字符串。这两个函数配合起来能处理 SUBSTRING_INDEX 搞不定的变长、无固定分隔符的场景。-- 从混合文本中提取第一串连续数字 SELECT REGEXP_SUBSTR(订单号PO2024001金额899.50, [0-9]); -- 2024001 -- 提取数字和小数点组成的金额 SELECT REGEXP_SUBSTR(订单号PO2024001金额899.50, [0-9]\\.[0-9]); -- 899.50 -- 把非数字字符清除后只留数字 SELECT REGEXP_REPLACE(PO2024001, [^0-9], ); -- 2024001正则匹配默认是贪婪的[0-9]会尽可能多地匹配连续数字。如果你只需要第一段数字它会在遇到第一个非数字字符时停住所以上面第一条查询返回2024001而不是把899也吞进来。第三条里[^0-9]表示“非数字的任意字符”替换成空串后整串只剩数字这是清洗电话号码和订单号最常用的表达式。4.2 提取手机号、订单号的完整表达式生产场景里提取手机号最怕的是数据格式不统一有的带 86 前缀有的带横杠有的中间有空格。正则的写法要根据源数据的实际情况设计一个万能表达式往往在测试数据上很漂亮落到真实数据上就过拟合。我一般先统计样本确认手机号前后边界是什么字符再写匹配表达式。-- 提取 11 位手机号 SELECT REGEXP_SUBSTR(客户电话13800138000联系人王先生, 1[0-9]{10}); -- 13800138000 -- 提取带国家码的电话 SELECT REGEXP_SUBSTR(86 138-0013-8000, \\?[0-9][0-9 -]{10,15}); -- 提取 PO 开头的订单号 SELECT REGEXP_SUBSTR(2024-06-01PO20240601001已发货, PO[0-9]{11}); -- PO20240601001手机号表达式1[0-9]{10}限定了第一位必须是 1后面 10 位任意数字。第一次写正则的人容易写成[0-9]{11}这在只有纯数字的字段里没问题但一旦文本里还有别的数字串就容易匹配到别的地方。订单号表达式PO[0-9]{11}同理前缀字母相当于给匹配加了一个锚点这比纯数字序列可靠得多。在 MySQL 字符串里写反斜杠要加倍\\d代表正则里的\d\\代表字面加号。这个双反斜杠的细节经常让人调半天而且不同版本驱动里对反斜杠的处理还有差异建议在 SQL 客户端里先跑一遍再往代码里搬。4.3 REGEXP_REPLACE 清掉干扰字符再截取截取之前先做清洗是处理脏数据最有效的手段。REGEXP_REPLACE 的作用是把干扰字符替换成统一的分隔符或直接删除让后续的 SUBSTRING_INDEX 有规律可循。最常见的组合是先替换后截取而不是寄希望于一个正则匹配到底。-- 清洗把非数字、非字母的字符替换成逗号 SELECT REGEXP_REPLACE(订单号:PO-2024_0601(已付), [^0-9A-Za-z], ,); -- 结果,PO,2024,0601,已付 -- 中文也被转成了逗号 -- 清洗后按逗号截取第 2 段 SELECT SUBSTRING_INDEX( SUBSTRING_INDEX( REGEXP_REPLACE(订单号:PO-2024_0601(已付), [^0-9A-Za-z], ,), ,, 2), ,, -1); -- PO这个例子里正则替换把所有非字母数字字符都换成了逗号中文也变成了逗号导致“已付”这两个字被拆掉。如果你的数据里中文是有意义的不要用这种全局清洗应该把中文字符保留[^0-9A-Za-z\\u4e00-\\u9fa5]这种范围匹配在部分 MySQL 版本里不生效换成显式列出允许的字符范围更稳妥。用 REGEXP_REPLACE 清洗的原则是只替换你确定不要的字符不要用排除法误伤有效字段。5. 字符串截取避坑5 个高频翻车现场与处理办法5.1 LENGTH 按字节数计导致中文截断错位现象对你好abc执行SUBSTRING(你好abc, 1, LENGTH(你好abc))结果只返回部分字符甚至出现乱码而同样的 SQL 在英文数据上完全正常。原因LENGTH 返回的是字节数utf8mb4 字符集下一个中文占 3 字节LENGTH(你好abc)返回 9 而不是 5截取长度被放大。解决统计字符数用 CHAR_LENGTHMySQL 8.0 也支持字符语义的截取函数。-- 正确按字符数计算 SELECT CHAR_LENGTH(你好abc); -- 5 SELECT SUBSTRING(你好abc, 1, CHAR_LENGTH(你好abc)); -- 你好abc -- 错误按字节数截取 SELECT LENGTH(你好abc); -- 95.2 SUBSTRING 负起点不是从右往左取现象想取字符串20240601的最后 3 位写了SUBSTRING(20240601, -3)结果拿到的是601看起来没问题但想取前 3 位时写了SUBSTRING(20240601, -5, 3)结果返回406完全不是预期。原因负起点表示从右往左数第几个位置作为起点截取方向依然是从左往右。解决取右边固定位数直接用 RIGHT取中间段先算 LOCATE。-- 想取 20240601 的倒数第 2、3、4 位正解是 RIGHT SELECT RIGHT(20240601, 3); -- 601 -- 经过一番折腾还是不推荐 SUBSTRING 负数写法 SELECT SUBSTRING(20240601, -4, 2); -- 06位置绕容易算错5.3 SUBSTRING_INDEX 找不到分隔符时静默返回全串现象脏数据里某行没有逗号SUBSTRING_INDEX(addr, ,, 1)本该取第一段结果这一行返回了完整的 addr后面的解析逻辑全乱。原因SUBSTRING_INDEX 在找不到分隔符时不报错直接返回整个字符串这是设计如此。解决先判断分隔符是否存在用 LOCATE 或 CASE WHEN 挡住脏数据。SELECT CASE WHEN LOCATE(,, addr) 0 THEN SUBSTRING_INDEX(addr, ,, 1) ELSE 分隔符缺失 END AS first_part FROM user_addr;5.4 MySQL 5.7 没有 REGEXP_SUBSTR代码直接报错现象开发环境用 MySQL 8.0 调试通过的查询部署到生产 5.7 上报FUNCTION REGEXP_SUBSTR does not exist。原因REGEXP_SUBSTR 是 8.0 新增函数5.7 及更早版本只支持 REGEXP 运算符不支持正则提取和替换函数。解决先确认版本5.7 下用 SUBSTRING_INDEX 和 LOCATE 组合替代如果版本混杂在代码里做兼容分支。5.5 NULL 参与截取运算返回 NULL报表里出现整列空白现象某个字段部分行为 NULLSUBSTRING_INDEX(NULL, ,, 1)返回 NULL 而不是空串报表里这列整段消失。原因所有字符串函数在遇到 NULL 参数时结果都是 NULL这是 SQL 的三值逻辑。解决进函数前用 COALESCE 或 IFNULL 把 NULL 转成默认值但注意转换后要确保分隔符存在否则又触发 5.3 的问题。SELECT SUBSTRING_INDEX(COALESCE(addr, 占位,占位), ,, 1) AS first_part FROM user_addr;这五条是我在使用 MySQL 字符串函数时踩过的真实坑第 5.1 和第 5.3 的坑最容易在测试数据上漏掉建议建一个包含中文、NULL、缺失分隔符的临时表把截取语句跑一遍再上线。6. 把截取能力接到真实任务里临时表、UPDATE 与验证习惯6.1 用 UPDATE 把截取结果回填到新列截取结果只用来 SELECT 的话每次查询都要重复写一段嵌套函数性能和维护成本都不划算。常见做法是先把结果回填到表里的新字段用一次 UPDATE 把脏字段的解析结果固化下来后续查询直接走新列。回填前先 SELECT 验证截取结果再执行 UPDATE避免直接在线上数据上翻车。ALTER TABLE login_log ADD COLUMN phone VARCHAR(20) NULL; UPDATE login_log SET phone SUBSTRING_INDEX( SUBSTRING_INDEX(client_info, ,, 1), :, -1) WHERE client_info IS NOT NULL AND client_info LIKE %手机号:%;UPDATE 里建议加一个 LIKE 条件挡掉无手机号的数据否则SUBSTRING_INDEX会按 5.3 的行为返回整串把垃圾数据写进新列。执行后马上用 COUNT 对比源数据和回填后的数据量确认没有大面积丢行或错行。6.2 存储过程中批量截取的写法参考需要循环处理行数据时有人喜欢写存储过程配游标。我一般不建议为纯截取任务上存储过程能用一条 UPDATE 解决的不要用游标。但如果你确实要在存储过程里逐行做条件判断注意把截取结果先赋值给变量再在 IF 分支里复用不要每条分支都重新写一遍嵌套函数。DELIMITER // CREATE PROCEDURE parse_login_info() BEGIN DECLARE v_info VARCHAR(255); DECLARE v_phone VARCHAR(20); DECLARE done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT client_info FROM login_log WHERE phone IS NULL; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done 1; OPEN cur; read_loop: LOOP FETCH cur INTO v_info; IF done 1 THEN LEAVE read_loop; SET v_phone IF(v_info IS NULL, NULL, SUBSTRING_INDEX(SUBSTRING_INDEX(v_info, ,, 1), :, -1)); IF v_phone IS NOT NULL THEN UPDATE login_log SET phone v_phone WHERE client_info v_info; END IF; END LOOP; CLOSE cur; END// DELIMITER ;存储过程的坑主要在游标效率和死循环风险逐行 UPDATE 在小表上没问题百万级以上行的表就别这么干改成批量 UPDATE 或者临时表 JOIN。这里用 IFNULL 包裹再判断的做法是为了同时防 NULL 和防空串。6.3 用 COUNT、LENGTH 与排序验证截取结果写完截取逻辑验证比实现更重要。我的验证习惯是三步走第一用 COUNT 比较源数据行数和回填后的非空行数第二用 LENGTH 检查截取结果的宽度分布比如手机号长度应该集中在 11第三用 ORDER BY 按截取结果排序肉眼扫一遍有没有明显越界的值。-- 第一步数量验证 SELECT COUNT(*) FROM login_log WHERE client_info LIKE %手机号:%; SELECT COUNT(*) FROM login_log WHERE phone IS NOT NULL; -- 第二步长度分布验证 SELECT CHAR_LENGTH(phone) AS len, COUNT(*) FROM login_log GROUP BY CHAR_LENGTH(phone); -- 第三步排序抽查异常值 SELECT phone FROM login_log WHERE CHAR_LENGTH(phone) 11 ORDER BY phone;长度分布在手机号场景里能立刻暴露问题比如出现长度 20 的记录基本可以断定分隔符缺失或嵌套截取错了层级。GROUP BY CHAR_LENGTH(phone)这种写法在 MySQL 里允许在 GROUP BY 中使用函数表达式所以不用额外套一层子查询这让验证 SQL 简洁不少。我自己的习惯是线上执行任何批量 UPDATE 前必带 WHERE 限制截取语句先 SELECT 出来看 10 行样本再动真实数据。字符串函数本身不难难的是数据里藏着的那几个异常值验证步骤就是给自己买的后悔药。希望这些用法和踩坑经验帮到你能少走几步弯路。本文还有配套的精品资源点击获取