MariaDB 10.6.20提取多列中多组有效10位手机号的问题

针对你需要从col1、col2、col3、col4列提取所有有效10位手机号（移除首位多余的1）并保存到col5的需求，以下是适配MariaDB 10.6的SQL方案，可处理同一列存在多个手机号的场景：

核心思路 #

1. 用递归CTE生成数字序列，遍历同一行内的所有手机号匹配项
2. 拼接目标列避免跨列内容误匹配，通过正则提取所有符合规则的手机号
3. 转换11位手机号为10位（移除首位1），合并所有有效结果到col5

完整SQL代码 #

WITH RECURSIVE nums(n) AS (
    SELECT 1 UNION ALL SELECT n+1 FROM nums WHERE n < 10 -- 按需调整最大匹配数，比如最多5个就设n<5
)
UPDATE your_table t
JOIN (
    SELECT 
        id, -- 替换为你的表主键列名
        GROUP_CONCAT(DISTINCT 
            CASE 
                -- 若提取到11位手机号（首位为1），截取后10位
                WHEN LENGTH(REGEXP_SUBSTR(CONCAT(col1, ' ', col2, ' ', col3, ' ', col4), '1?[0-9]{10}', 1, nums.n)) = 11 
                THEN SUBSTR(REGEXP_SUBSTR(CONCAT(col1, ' ', col2, ' ', col3, ' ', col4), '1?[0-9]{10}', 1, nums.n), 2)
                -- 若为10位有效号，直接保留
                ELSE REGEXP_SUBSTR(CONCAT(col1, ' ', col2, ' ', col3, ' ', col4), '1?[0-9]{10}', 1, nums.n)
            END
            SEPARATOR ', ') AS extracted_phones
    FROM your_table -- 替换为实际表名
    CROSS JOIN nums
    WHERE REGEXP_SUBSTR(CONCAT(col1, ' ', col2, ' ', col3, ' ', col4), '1?[0-9]{10}', 1, nums.n) IS NOT NULL
    GROUP BY id
) AS phone_data ON t.id = phone_data.id
SET t.col5 = phone_data.extracted_phones;

关键说明 #

• 数字序列nums：生成1到指定上限的数字，用于遍历同一行内的第N个手机号匹配项，可根据实际数据调整上限（比如n<5表示最多提取5个手机号）
• 正则表达式：1?[0-9]{10}匹配两种情况：11位以1开头的手机号，或直接10位的有效手机号
• 去重与分隔：GROUP_CONCAT中的DISTINCT可避免同一行出现重复手机号，SEPARATOR ', '指定手机号之间的分隔符，可按需修改
• 主键关联：必须通过主键（如id）关联原表与临时结果集，确保更新正确的行

内容的提问来源于stack exchange，提问作者yenren