如何在MariaDB 5.5/MySQL 8.0中提取并筛选重复OrderID？

需求说明 #

• 数据表earnings的comment字段包含Order#:XXX格式字符串，需提取其中的OrderID，并筛选出存在重复的OrderID对应的所有记录
• 示例comment内容：DEBIT Order#:11985, OrderUsername: chana，需提取11985作为order_id

一、提取OrderID的实现方案 #

1. MariaDB 5.5.68版本（无正则提取支持） #

由于MariaDB 5.5不支持正则提取功能，需通过LOCATE()和SUBSTRING()组合实现：

提取查询语句

SELECT id, comment, 
       SUBSTRING(comment, LOCATE('#:', comment) + 2, 
                 LOCATE(', ', comment) - LOCATE('#:', comment) - 2) AS order_id
FROM earnings
WHERE comment LIKE 'DEBIT Order%' OR comment LIKE 'CREDIT Order%'

注：替换原语句中固定偏移的写法，改用两个LOCATE()的差值计算截取长度，避免因字符串长度变化导致提取错误

批量更新到新增的order_id列

UPDATE earnings e1
INNER JOIN (
    SELECT id, 
           TRIM(BOTH ',' FROM SUBSTRING(comment, LOCATE('#:', comment) + 2, 
                                        LOCATE(', ', comment) - LOCATE('#:', comment) - 2)) AS order_id
    FROM earnings
    WHERE comment LIKE 'DEBIT Order%' OR comment LIKE 'CREDIT Order%'
) dt ON e1.id = dt.id
SET e1.order_id = dt.order_id;

2. MySQL 8.0版本（支持正则替换） #

利用REGEXP_REPLACE()直接通过正则提取，无需依赖固定位置：

提取查询语句

SELECT id, comment,
       REGEXP_REPLACE(comment, '^.*Order#:(.*?),.*$', '$1') AS order_id
FROM earnings
WHERE comment REGEXP 'Order#:[0-9]+,';

正则^.*Order#:(.*?),.*$匹配整个字符串，捕获Order#:后到逗号前的内容作为order_id

批量更新到order_id列

UPDATE earnings
SET order_id = REGEXP_REPLACE(comment, '^.*Order#:(.*?),.*$', '$1')
WHERE comment REGEXP 'Order#:[0-9]+,';

二、筛选重复OrderID的所有记录 #

方法1：基于已更新的order_id列查询 #

若已将order_id更新到表中，直接用以下语句筛选重复项：

SELECT e.*
FROM earnings e
INNER JOIN (
    SELECT order_id
    FROM earnings
    WHERE order_id IS NOT NULL
    GROUP BY order_id
    HAVING COUNT(*) > 1
) dup ON e.order_id = dup.order_id
ORDER BY e.order_id;

方法2：无需提前更新，直接关联提取结果 #

如果不想提前更新order_id列，可直接在查询中关联提取逻辑：

MariaDB 5.5版本

SELECT e.*, 
       SUBSTRING(e.comment, LOCATE('#:', e.comment) + 2, 
                 LOCATE(', ', e.comment) - LOCATE('#:', e.comment) - 2) AS order_id
FROM earnings e
INNER JOIN (
    SELECT SUBSTRING(comment, LOCATE('#:', comment) + 2, 
                     LOCATE(', ', comment) - LOCATE('#:', comment) - 2) AS order_id
    FROM earnings
    WHERE comment LIKE 'DEBIT Order%' OR comment LIKE 'CREDIT Order%'
    GROUP BY order_id
    HAVING COUNT(*) > 1
) dup ON SUBSTRING(e.comment, LOCATE('#:', e.comment) + 2, 
                   LOCATE(', ', e.comment) - LOCATE('#:', e.comment) - 2) = dup.order_id
WHERE e.comment LIKE 'DEBIT Order%' OR e.comment LIKE 'CREDIT Order%'
ORDER BY order_id;

MySQL 8.0版本

SELECT e.*,
       REGEXP_REPLACE(e.comment, '^.*Order#:(.*?),.*$', '$1') AS order_id
FROM earnings e
INNER JOIN (
    SELECT REGEXP_REPLACE(comment, '^.*Order#:(.*?),.*$', '$1') AS order_id
    FROM earnings
    WHERE comment REGEXP 'Order#:[0-9]+,'
    GROUP BY order_id
    HAVING COUNT(*) > 1
) dup ON REGEXP_REPLACE(e.comment, '^.*Order#:(.*?),.*$', '$1') = dup.order_id
WHERE e.comment REGEXP 'Order#:[0-9]+,'
ORDER BY order_id;

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