1. 从“字符串里抠数字”说起一个高频且恼人的数据清洗场景做数据清洗或者业务系统维护的朋友十有八九都遇到过这个场景某个字段里数字和文字、符号、空格混在一起你需要把其中的数字部分单独“抠”出来。比如商品规格字段里写着“iPhone 14 Pro Max 256GB”你需要提取出“14”和“256”又或者从一段混杂的日志文本“ErrorCode: 500, UserID: A123B456”里你需要拿到“500”和“123456”。在MySQL里没有像Python的re.findall()或者JavaScript的match()那样直接返回数组的正则函数这个“抠数字”的操作就成了一个需要动点脑筋的“手艺活”。我处理过太多类似的需求从简单的订单号清洗到复杂的爬虫数据入库整理字符串提取数字几乎是家常便饭。很多人第一反应是写个程序用Python或Java处理完再入库这当然可以但很多时候数据就在库里或者你需要写一个SQL报表、一个数据更新的触发器这时候就必须在SQL层面解决。MySQL虽然没有“一键提取”函数但通过组合REGEXP、REGEXP_REPLACE、SUBSTRING等函数完全可以优雅且高效地完成任务。今天我就结合这些年踩过的坑和总结的技巧把MySQL里从字符串提取数字的几种主流方法掰开揉碎了讲清楚让你下次再遇到时能从容地选出最适合的那把“手术刀”。2. 需求拆解你要的“数字”到底是什么在动手写SQL之前最关键的一步是明确需求。笼统地说“提取数字”可能会在后续引发大问题。我们必须问自己几个问题数字的形态是只包含0-9的纯数字还是包含小数点.、负号-、千分位分隔符,如1,234.56数字的位置数字是固定在字符串的某个位置如开头、结尾还是毫无规律地散落在其中数字的连续性字符串中可能有多段数字你是需要第一段、最后一段还是把所有数字拼接成一个整体对非数字字符的处理是直接丢弃还是需要保留其位置信息根据这些问题的答案我们可以把常见的“提取数字”需求分为以下几类每种都有对应的解决方案场景A提取字符串中第一个连续的数字序列。例如从ABC123DEF456中提取123。场景B提取字符串中所有的数字字符并拼接成一个新的数字字符串。例如从Room 501, Building 7中提取5017。场景C提取符合特定格式的数值如带小数点的浮点数98.5kg或带负号的温度-15度。场景D字符串中本身包含分隔符的数值如价格: $1,299.99需要得到1299.99。不同的场景解决方案的复杂度和性能开销差异很大。下面我们就针对这些主流场景逐一给出详细的实现方案和避坑指南。3. 核心武器库MySQL的字符串与正则函数工欲善其事必先利其器。在深入具体方案前我们先快速过一下会用到的几个核心MySQL函数理解它们的能力和边界。3.1REGEXP/RLIKE判断与匹配这是正则表达式的基础操作符用于检查一个字符串是否匹配某个模式。它只返回布尔值1或0不直接提取内容但它是我们定位数字的“侦察兵”。SELECT abc123 REGEXP [0-9]; -- 返回 1因为字符串包含数字 SELECT abc REGEXP [0-9]; -- 返回 0因为字符串不包含数字这里的[0-9]就是一个简单的正则模式表示“任意一个数字字符”。更复杂的模式如[0-9]一个或多个数字、^[0-9]以数字开头也都支持。3.2REGEXP_REPLACE替换与删除MySQL 8.0这是MySQL 8.0版本引入的“神器”功能强大。它可以将字符串中匹配正则模式的部分替换为指定的新字符串。提取数字的核心思路之一就是把“非数字”都替换成空字符串。-- 语法REGEXP_REPLACE(原字符串, 正则模式, 替换字符串) SELECT REGEXP_REPLACE(abc123def456, [^0-9], ); -- 结果123456这里[^0-9]中的^表示“非”所以这个模式匹配任何“非数字字符”并将它们替换为空最终只剩下数字。3.3REGEXP_SUBSTR直接提取子串MySQL 8.0同样是8.0引入的利器它可以直接返回字符串中匹配正则模式的第一个子串。这对于提取第一段连续数字特别方便。-- 语法REGEXP_SUBSTR(原字符串, 正则模式) SELECT REGEXP_SUBSTR(abc123def456, [0-9]); -- 结果123模式[0-9]匹配一个或多个连续的数字。3.4SUBSTRING/MID与LOCATE/REGEXP_INSTR传统定位截取在MySQL 8.0之前或者在一些需要更精细控制的情况下我们可以结合使用这些函数。LOCATE(substr, str)返回子串substr在字符串str中第一次出现的位置。REGEXP_INSTR(str, pattern)(8.0)返回正则模式pattern在字符串str中第一次出现的位置。比LOCATE更强大。SUBSTRING(str, pos, len)从字符串str的第pos位开始截取长度为len的子串。思路是先用LOCATE或REGEXP_INSTR找到数字开始的位置再想办法确定数字的长度最后用SUBSTRING截取。确定长度是这里的难点通常需要一些技巧。3.5REPLACE处理固定字符虽然不是正则但对于简单的字符移除非常高效。比如如果只想移除字符串中所有的空格或横线REPLACE是首选。SELECT REPLACE(2024-01-01, -, ); -- 结果20240101了解这些工具后我们就可以开始组装我们的解决方案了。4. 实战方案详解针对不同场景的SQL配方4.1 场景A提取第一个连续数字序列如ABC123DEF - 123这是非常常见的需求比如从产品型号、混合编码中提取编号。方案1推荐MySQL 8.0使用REGEXP_SUBSTR这是最直观、最简洁的方法。SELECT your_column, REGEXP_SUBSTR(your_column, [0-9]) AS extracted_number FROM your_table;注意如果字符串开头就是数字例如123ABC这个模式也能正确提取出123。但如果数字中间有小数点等这个模式不会匹配需要修改为[0-9](\\.[0-9])?来匹配小数。方案2MySQL 5.x 或更精细控制使用字符串函数组合在低版本MySQL中我们需要自己“造轮子”。思路是找到第一个数字出现的位置然后从这个位置开始逐个字符判断直到遇到非数字字符为止。这通常需要一个辅助的序列表或递归CTEMySQL 8.0 的通用表表达式比较复杂。一个相对取巧的“穷举”方法是结合SUBSTRING和LOCATE但前提是你能预估数字的大致长度。这里不推荐生产环境使用过于复杂的低版本方案建议优先考虑升级或使用应用程序处理。4.2 场景B提取所有数字并拼接如Room 501, Building 7 - 5017这个需求在于收集散落在各处的所有数字字符。方案1首选MySQL 8.0使用REGEXP_REPLACE这是最优雅的方案一行代码搞定。SELECT your_column, REGEXP_REPLACE(your_column, [^0-9], ) AS extracted_digits FROM your_table;[^0-9]匹配所有非数字字符并将其替换为空字符串剩下的自然就是所有数字字符按原顺序的拼接。方案2MySQL 5.7及以下使用递归CTE或自定义函数在MySQL 8.0以下版本没有REGEXP_REPLACE实现起来非常麻烦。一种方法是写一个循环或递归的存储过程遍历字符串的每个字符判断是否为数字并拼接。另一种方法是使用多次嵌套的REPLACE函数把已知的非数字字符如字母a-z, A-Z逐一替换掉但这不通用且效率低下。-- 一个非常局限且低效的示例仅用于演示思路 SELECT REPLACE(REPLACE(LOWER(a1b2c3), a, ), b, ); -- 需要替换所有字母...因此对于这个场景强烈建议在应用层处理或者升级数据库版本。4.3 场景C提取带符号和小数的完整数值如-12.5℃ - -12.5这时我们的正则模式需要升级以容纳负号和小数点。方案使用REGEXP_SUBSTR匹配更复杂的模式SELECT your_column, -- 模式解释-? 表示可选的负号[0-9] 表示整数部分(\.[0-9])? 表示可选的小数部分 REGEXP_SUBSTR(your_column, -?[0-9](\\.[0-9])?) AS extracted_number FROM your_table;关键点在MySQL的正则字符串中反斜杠\是转义符。为了匹配字面意义的小数点.我们需要写成\\.。而在SQL字符串中反斜杠本身也需要转义所以最终写成了\\\\.。这是最容易出错的地方之一。 执行后温度是-12.5度会被提取为-12.5。但请注意结果仍然是字符串类型。如果需要做数值计算需要使用CAST(extracted_number AS DECIMAL(10,2))进行转换。4.4 场景D处理含千分位的数字字符串如$1,299.99 - 1299.99这个场景多出现在从文本报告或网页中抓取的格式化数据。我们需要先移除千分位逗号再提取数字。方案两步法先清理再提取SELECT your_column, -- 第一步移除逗号和美元符号等非数字字符保留小数点 REGEXP_REPLACE(your_column, [^0-9.], ) AS cleaned_string, -- 第二步将清理后的字符串转为数字 CAST(REGEXP_REPLACE(your_column, [^0-9.], ) AS DECIMAL(10,2)) AS extracted_number FROM your_table;这里[^0-9.]模式移除了所有非数字和非小数点的字符将$1,299.99变成1299.99然后再将其转换为DECIMAL类型。5. 性能考量、常见陷阱与实战技巧在实际生产环境中直接使用这些正则函数尤其是对海量数据操作时需要格外小心。5.1 性能陷阱正则表达式的开销REGEXP_REPLACE和REGEXP_SUBSTR虽然强大但属于计算密集型操作比简单的LIKE或SUBSTRING开销大得多。当表中数据量达到百万、千万级时在WHERE条件或SELECT列表中使用这些函数进行全表扫描可能会导致查询时间急剧上升。优化建议建立预处理字段如果源数据更新不频繁但查询频繁最好的方法是在数据写入或更新时就通过触发器或应用程序计算好“提取后的数字”字段并存入一个单独的列如product_code_numeric并为其建立索引。查询时直接使用这个索引列性能极佳。避免在WHERE中直接使用WHERE REGEXP_SUBSTR(column, [0-9]) 123这种写法会导致每一行都要执行正则计算无法使用索引。应尽量使用预处理字段。缩小数据范围先通过其他条件如时间范围、类别利用索引缩小数据集再对结果集应用正则提取。5.2 数据类型转换的坑提取出来的数字默认是VARCHAR类型。如果你需要对其进行、、SUM()、AVG()等数值运算必须使用CAST()或CONVERT()函数进行显式转换否则可能会得到意想不到的结果按字符串字典序比较。-- 错误示例字符串比较 SELECT 100 20; -- 返回 1 (true)因为1 2 -- 正确示例转换为数字后比较 SELECT CAST(100 AS UNSIGNED) CAST(20 AS UNSIGNED); -- 返回 0 (false)在转换时还要考虑数值范围。如果提取的数字可能很大要使用DECIMAL(M, D)或BIGINT而不是INT。5.3 空值与异常处理如果某行数据中根本没有数字REGEXP_SUBSTR会返回NULLREGEXP_REPLACE会返回空字符串。这可能会导致后续计算出错。SELECT your_column, -- 使用IFNULL或COALESCE提供默认值 IFNULL(CAST(REGEXP_SUBSTR(your_column, [0-9]) AS UNSIGNED), 0) AS safe_number FROM your_table;对于REGEXP_REPLACE的结果如果可能是空字符串转换时要小心SELECT CAST(NULLIF(REGEXP_REPLACE(col, [^0-9], ), ) AS UNSIGNED) FROM table; -- NULLIF(a, b) 函数在a等于b时返回NULL这里如果替换后是空串则返回NULLCAST(NULL as UNSIGNED) 结果也是NULL。5.4 正则表达式模式的设计经验贪婪匹配[0-9]是贪婪匹配会尽可能匹配更长的数字串。这通常是你要的。但在abc123def456xyz中如果你想分别提取123和456单个REGEXP_SUBSTR就无能为力了它只会拿到123。需要配合其他方法循环提取。精确匹配如果你的数字有固定格式比如是4位年份用[0-9]{4}比[0-9]更精确、更高效。转义字符牢记在MySQL字符串中写正则时对于特殊字符如.、(、)、[、等如果需要匹配其本身前面要加两个反斜杠\\。例如匹配小数点\\\\.。6. 进阶案例在UPDATE、触发器及索引中的应用掌握了基础提取方法后我们来看看如何在更复杂的数据库操作中应用它们。6.1 使用UPDATE批量清洗历史数据假设我们有一个products表model字段是混杂的字符串现在需要将其中第一段数字提取出来更新到新加的model_number字段中。-- 假设已添加了 model_number INT 字段 UPDATE products SET model_number CAST(REGEXP_SUBSTR(model, ^[0-9]) AS UNSIGNED) WHERE model REGEXP [0-9]; -- 只更新包含数字的记录提高效率 -- 注意这里使用了 ^[0-9] 确保只匹配开头的数字避免匹配到中间的数字。提示在大表上执行UPDATE前务必先在一个小范围数据或备份表上测试确认正则模式准确无误。也可以分批更新例如加上LIMIT 1000并循环执行。6.2 创建触发器自动维护提取字段为了保持数据一致性可以在源字段更新时自动计算并填充提取字段。这在MySQL 8.0中非常方便。DELIMITER // CREATE TRIGGER before_products_update BEFORE UPDATE ON products FOR EACH ROW BEGIN -- 当model字段被更新时自动重新提取数字到model_number IF NEW.model OLD.model THEN SET NEW.model_number CAST(REGEXP_SUBSTR(NEW.model, [0-9]) AS UNSIGNED); END IF; END; // DELIMITER ;同样可以创建一个BEFORE INSERT的触发器确保新插入的数据也能自动处理。6.3 关于索引的严肃讨论直接对REGEXP_SUBSTR(column, ...)的结果表达式创建索引是不被允许的。但是你可以为那个存储提取结果的物理列如model_number创建索引。这正是我们推荐添加预处理字段的核心原因之一。一旦model_number上有索引查询WHERE model_number 123的速度将是闪电般的而WHERE REGEXP_SUBSTR(model, [0-9]) 123则需要进行全表扫描和函数计算。6.4 处理多段数字的复杂情况有时一段字符串里有多组独立的数字需要分别提取。例如从尺寸:100x200cm中提取长度100和宽度200。在MySQL 8.0中REGEXP_SUBSTR可以通过第三个参数起始位置和第四个参数匹配次数来实现。SET str 尺寸:100x200cm; SELECT REGEXP_SUBSTR(str, [0-9], 1, 1) AS length, -- 从第1个字符开始找第1次匹配 REGEXP_SUBSTR(str, [0-9], 1, 2) AS width; -- 从第1个字符开始找第2次匹配 -- 结果length100, width200这个功能非常强大可以应对很多复杂的解析场景。参数1, 1是默认值所以之前我们省略了。