如何仅用SQL在MySQL中反序列化WooCommerce购物车序列化数据？

好问题！MySQL本身并没有内置的PHP序列化数据解析函数，不过我们可以通过自定义SQL函数结合字符串处理逻辑，来针对你这种WooCommerce购物车的序列化格式进行解析。下面是具体的实现步骤和查询示例：

一、创建自定义解析函数 #

首先，我们需要创建几个辅助函数来处理序列化字符串中的不同数据类型（字符串、整数、浮点数），以及提取购物车对应的核心数组内容。

1. 提取序列化中的字符串值 #

DELIMITER //
CREATE FUNCTION get_string_from_serial(serial TEXT, start_pos INT)
RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE len INT;
    DECLARE str TEXT;
    -- 提取字符串长度（s:N:"..."中的N）
    SET len = CAST(SUBSTRING(serial, start_pos + 2) AS UNSIGNED);
    -- 提取实际字符串内容
    SET str = SUBSTRING(serial, start_pos + 4 + LENGTH(CAST(len AS CHAR)), len);
    RETURN str;
END //
DELIMITER ;

2. 提取序列化中的整数值 #

DELIMITER //
CREATE FUNCTION get_int_from_serial(serial TEXT, start_pos INT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE int_val INT;
    -- 提取i:后面的整数内容
    SET int_val = CAST(SUBSTRING(serial, start_pos + 2) AS UNSIGNED);
    RETURN int_val;
END //
DELIMITER ;

3. 提取序列化中的浮点数值 #

DELIMITER //
CREATE FUNCTION get_float_from_serial(serial TEXT, start_pos INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE float_val DECIMAL(10,2);
    -- 提取d:后面的浮点内容，转为DECIMAL类型
    SET float_val = CAST(SUBSTRING(serial, start_pos + 2) AS DECIMAL(10,2));
    RETURN float_val;
END //
DELIMITER ;

4. 提取购物车核心数组的序列化内容 #

这个函数会从完整的序列化数据中，定位并提取cart键对应的数组部分：

DELIMITER //
CREATE FUNCTION get_cart_serial(serial TEXT)
RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE cart_start INT;
    DECLARE cart_end INT;
    DECLARE brace_count INT DEFAULT 0;
    -- 定位cart键的结束位置
    SET cart_start = LOCATE('s:4:"cart";', serial) + LENGTH('s:4:"cart";');
    SET cart_end = cart_start;
    -- 匹配大括号，找到cart数组的结束位置
    WHILE cart_end <= LENGTH(serial) DO
        IF SUBSTRING(serial, cart_end, 1) = '{' THEN
            SET brace_count = brace_count + 1;
        ELSEIF SUBSTRING(serial, cart_end, 1) = '}' THEN
            SET brace_count = brace_count - 1;
            IF brace_count = 0 THEN
                LEAVE;
            END IF;
        END IF;
        SET cart_end = cart_end + 1;
    END WHILE;
    -- 返回cart数组的序列化内容
    RETURN SUBSTRING(serial, cart_start, cart_end - cart_start);
END //
DELIMITER ;

二、编写查询提取购物车项数据 #

假设你的购物车表名为woocommerce_cart_table，存储序列化数据的字段名为serialized_cart_data，可以用递归CTE拆分每个购物车项，并提取所需属性：

WITH RECURSIVE cart_items AS (
    SELECT 
        get_cart_serial(serialized_cart_data) AS cart_serial,
        1 AS item_num,
        -- 定位第一个购物车项的起始位置
        LOCATE('s:32:"', get_cart_serial(serialized_cart_data)) AS item_start,
        NULL AS item_serial
    FROM woocommerce_cart_table
    UNION ALL
    SELECT 
        cart_serial,
        item_num + 1,
        -- 定位下一个购物车项的起始位置
        LOCATE('s:32:"', cart_serial, item_start + LENGTH('s:32:"') + 32 + 3) AS next_item_start,
        -- 提取当前购物车项的序列化内容
        SUBSTRING(cart_serial, item_start, 
            CASE WHEN next_item_start > 0 THEN next_item_start - item_start 
            ELSE LENGTH(cart_serial) - item_start + 1 END) AS item_serial
    FROM cart_items
    WHERE item_start > 0
)
SELECT 
    -- 购物车项唯一标识
    get_string_from_serial(item_serial, LOCATE('s:32:"', item_serial)) AS cart_item_key,
    -- 商品ID
    get_int_from_serial(item_serial, LOCATE('s:10:"product_id";i:', item_serial)) AS product_id,
    -- 商品数量
    IFNULL(get_int_from_serial(item_serial, LOCATE('s:8:"quantity";i:', item_serial)), 0) AS quantity,
    -- 商品总价
    IFNULL(get_float_from_serial(item_serial, LOCATE('s:10:"line_total";d:', item_serial)), 0.00) AS line_total,
    -- 变体ID
    get_int_from_serial(item_serial, LOCATE('s:12:"variation_id";i:', item_serial)) AS variation_id
FROM cart_items
WHERE item_serial IS NOT NULL;

三、注意事项 #

• 版本兼容性：递归CTE需要MySQL 8.0及以上版本，如果使用旧版本，需要改用临时表或其他方式拆分购物车项。
• 格式依赖：这些函数和查询是针对你提供的WooCommerce购物车序列化格式编写的，如果WooCommerce改变了键名或序列化结构（比如键名长度变化），需要调整LOCATE中的匹配字符串（比如s:10:"product_id"中的10是键名的长度）。
• 健壮性：字符串解析的方式不如PHP的unserialize函数健壮，对于复杂嵌套的序列化结构可能出错，但针对常规的WooCommerce购物车数据完全适用。
• 空值处理：用IFNULL处理可能缺失的字段（比如你示例中第二个购物车项没有line_total），避免返回NULL。

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