如何用MySQL从序列化字符串中提取所有order_id值并拼接？

给定一段PHP序列化字符串（示例如下），需要用MySQL查询提取其中所有order_id对应的数值，拼成逗号分隔的列表。之前用SUBSTRING_INDEX写的查询只能拿到第一个匹配的order_id，没法提取全部，下面是可行的解决方法：

示例序列化字符串：

a:4:{s:11:"customersId";i:381032;s:12:"commerceName";s:9:"amazonmws";s:9:"serviceId";i:32042;s:9:"trackings";a:2:{i:0;a:10:{s:12:"customers_id";i:381032;s:13:"commerce_name";s:9:"amazonmws";s:10:"service_id";i:32042;s:15:"commerce_ref_id";s:19:"112-1414980-3467405";s:12:"order_number";i:151434392;s:11:"tracking_no";s:15:"995365613134160";s:7:"carrier";s:3:"FDX";s:8:"order_id";i:151434392;s:7:"details";a:1:{i:0;a:2:{s:4:"wsku";s:12:"KZW4OC-F0015";s:3:"qty";s:9:"000000001";}}s:20:"commerce_account_key";N;}i:1;a:10:{s:12:"customers_id";i:381032;s:13:"commerce_name";s:9:"amazonmws";s:10:"service_id";i:32042;s:15:"commerce_ref_id";s:19:"002-5809777-9709040";s:12:"order_number";i:151536332;s:11:"tracking_no";s:15:"995365613134153";s:7:"carrier";s:3:"FDX";s:8:"order_id";i:151536332;s:7:"details";a:1:{i:0;a:2:{s:4:"wsku";s:12:"KZW4OC-F0011";s:3:"qty";s:9:"000000001";}}s:20:"commerce_account_key";N;}}}

你之前尝试的查询（仅返回单个order_id）：

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('serialized_string','"order_id";i:',-1),';s:7:',1);

解决方案 #

方法1：递归CTE（MySQL 8.0+可用） #

递归CTE可以循环遍历字符串，提取所有匹配的order_id，最后用GROUP_CONCAT拼成列表。假设你的表是your_table，存序列化字符串的字段是serialized_data，执行以下查询：

WITH RECURSIVE cte AS (
    -- 先提取第一个order_id，同时保留剩下的字符串
    SELECT
        SUBSTRING_INDEX(SUBSTRING_INDEX(serialized_data, '"order_id";i:', -1), ';', 1) AS order_id,
        SUBSTRING(serialized_data, LOCATE('"order_id";i:', serialized_data) + LENGTH('"order_id";i:') + LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(serialized_data, '"order_id";i:', -1), ';', 1))) AS remaining_data
    FROM your_table
    WHERE serialized_data LIKE '%"order_id";i:%'
    
    UNION ALL
    
    -- 递归提取剩余字符串里的order_id，直到没有匹配项
    SELECT
        SUBSTRING_INDEX(SUBSTRING_INDEX(remaining_data, '"order_id";i:', -1), ';', 1) AS order_id,
        SUBSTRING(remaining_data, LOCATE('"order_id";i:', remaining_data) + LENGTH('"order_id";i:') + LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(remaining_data, '"order_id";i:', -1), ';', 1))) AS remaining_data
    FROM cte
    WHERE remaining_data LIKE '%"order_id";i:%'
)
SELECT GROUP_CONCAT(DISTINCT order_id ORDER BY order_id) AS order_id_list
FROM cte;

方法2：数字表匹配（兼容MySQL 5.x） #

如果你的MySQL版本不支持CTE，可以先建一个临时数字表，用来对应order_id出现的次数：

1. 创建临时数字表（这里生成10行，不够的话可以多插几行）：

CREATE TEMPORARY TABLE numbers (n INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT);
INSERT INTO numbers VALUES (),(),(),(),(),(),(),(),(),();

2. 执行查询提取所有order_id并拼接：

SELECT GROUP_CONCAT(DISTINCT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(t.serialized_data, '"order_id";i:', n.n), ';', 1) 
    ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(t.serialized_data, '"order_id";i:', n.n), ';', 1)
) AS order_id_list
FROM your_table t
JOIN numbers n 
    ON n.n <= (LENGTH(t.serialized_data) - LENGTH(REPLACE(t.serialized_data, '"order_id";i:', ''))) / LENGTH('"order_id";i:')
WHERE t.serialized_data LIKE '%"order_id";i:%';

补充说明 #

• DISTINCT是用来去重的，如果你的数据里没有重复的order_id，可以去掉。
• GROUP_CONCAT默认用逗号分隔，要是需要其他分隔符，比如竖线，就写成GROUP_CONCAT(..., SEPARATOR '|')。

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