在mysql的日常数据处理场景中，经常需要从包含字母、符号的混合字符串中提取出连续的数值部分，比如从订单编号“ORD20240512001”中提取出“20240512001”，或者从用户留言“联系电话13800138000”中提取出手机号。除了使用内置函数处理简单场景外，编写自定义循环函数能覆盖更复杂的提取需求。
一、使用内置函数提取连续数字
对于简单的字符串，我们可以组合使用mysql的内置字符串函数实现连续数字提取，核心思路是遍历字符串的每个字符，筛选出0-9的字符后拼接。
比如使用SUBSTRING函数逐个截取字符，结合REGEXP判断是否为数字，示例代码如下：
-- 提取字符串中的连续数字，仅支持单段数字场景
DELIMITER //
CREATE FUNCTION get_number_simple(str VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE len INT DEFAULT CHAR_LENGTH(str);
DECLARE result VARCHAR(255) DEFAULT '';
DECLARE current_char CHAR(1);
WHILE i <= len DO
SET current_char = SUBSTRING(str, i, 1);
IF current_char REGEXP '^[0-9]$' THEN
SET result = CONCAT(result, current_char);
END IF;
SET i = i + 1;
END WHILE;
RETURN result;
END //
DELIMITER ;
调用该函数测试：
SELECT get_number_simple('ORD20240512001'); -- 返回20240512001
SELECT get_number_simple('联系电话13800138000，备用13900139000'); -- 仅返回13800138000，无法提取第二段数字
二、编写循环提取多段连续数字的自定义函数
上述简单函数只能提取第一段连续数字，如果需要提取字符串中所有连续的数字段，就需要优化循环逻辑，在遍历字符时记录连续数字的起止位置，遇到非数字字符时结束当前数字段的拼接。
2.1 函数逻辑设计
- 定义变量记录当前遍历位置、字符串长度、临时数字段、最终结果集合
- 循环遍历字符串的每个字符，判断当前字符是否为数字
- 如果是数字，将其加入临时数字段，同时标记当前处于数字段提取状态
- 如果不是数字，且处于数字段提取状态，则将临时数字段加入结果集合，重置临时数字段和提取状态
- 循环结束后，如果仍处于数字段提取状态，需要将最后一段临时数字段加入结果
- 返回所有提取到的连续数字段，可自定义分隔符拼接结果
2.2 完整函数代码
以下是支持提取多段连续数字的自定义函数实现，默认用逗号分隔多个结果：
DELIMITER //
CREATE FUNCTION get_all_numbers(str VARCHAR(255))
RETURNS VARCHAR(1000)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE len INT DEFAULT CHAR_LENGTH(str);
DECLARE current_char CHAR(1);
DECLARE temp_num VARCHAR(255) DEFAULT '';
DECLARE result VARCHAR(1000) DEFAULT '';
DECLARE is_in_num TINYINT DEFAULT 0; -- 0表示不在数字段，1表示在数字段
WHILE i <= len DO
SET current_char = SUBSTRING(str, i, 1);
-- 判断当前字符是否为数字
IF current_char REGEXP '^[0-9]$' THEN
SET temp_num = CONCAT(temp_num, current_char);
SET is_in_num = 1;
ELSE
-- 当前不是数字，且之前处于数字段，说明一段数字结束
IF is_in_num = 1 THEN
IF result = '' THEN
SET result = temp_num;
ELSE
SET result = CONCAT(result, ',', temp_num);
END IF;
SET temp_num = '';
SET is_in_num = 0;
END IF;
END IF;
SET i = i + 1;
END WHILE;
-- 循环结束后，如果还有未加入结果的数字段，补充进去
IF is_in_num = 1 THEN
IF result = '' THEN
SET result = temp_num;
ELSE
SET result = CONCAT(result, ',', temp_num);
END IF;
END IF;
RETURN result;
END //
DELIMITER ;
2.3 函数测试验证
调用上述函数测试不同场景的字符串：
-- 测试1：单段数字
SELECT get_all_numbers('ORD20240512001'); -- 返回20240512001
-- 测试2：多段数字
SELECT get_all_numbers('联系电话13800138000，备用13900139000'); -- 返回13800138000,13900139000
-- 测试3：数字夹杂在符号中间
SELECT get_all_numbers('价格：99.8元，库存：200件'); -- 返回99,8,200
-- 测试4：无数字字符串
SELECT get_all_numbers('纯字母字符串'); -- 返回空字符串
三、函数使用注意事项
- 创建自定义函数需要有mysql的
CREATE ROUTINE权限，且函数创建后默认对所有有权限的用户可见 - 如果字符串长度超过函数定义的
VARCHAR(255)参数长度，可以调整参数类型和结果变量的长度，避免截断 - 上述函数返回的多段数字用逗号分隔，如果需要其他分隔符，可以修改代码中拼接结果的
CONCAT部分 - 如果只需要提取最长的一段连续数字，可以在函数逻辑中记录每段数字的长度，最后返回最长的那段即可
通过上述自定义循环函数，我们可以灵活应对mysql中各种字符串连续数字提取的需求，比单纯使用内置函数适配性更强，开发者可以根据实际业务场景调整函数的返回逻辑。