在MariaDB中使用REGEXP_REPLACE更新字段的子查询参数问题

背景 #

ps_product_lang表的description字段包含带旧链接的HTML内容，需要将链接替换为新域名格式，且每个产品链接要对应其所属分类名称：

旧内容示例：

<ul>
<li><a href="https://onedomain.com/name-of-product-18-8433290610079.html">Product 1 </a></li>
<li><a href="https://onedomain.com/name-of-product-15-8433290610079.html">Product 2</a></li>
</ul>

目标格式：

<ul>
<li><a href="https://newdomain.com/category-name-IdProduct18/18-name-of-product.html">Product 1</a></li>
<li><a href="https://newdomain.com/category-name-IdProduct15/15-name-of-product.html">Product 2</a></li>
</ul>

核心问题 #

MariaDB的REGEXP_REPLACE无法在替换的CONCAT子查询中引用当前正则匹配的捕获组值（如\2），因为子查询是独立执行的，无法感知正则的匹配上下文，导致要么子查询不识别捕获组，要么所有链接复用第一个匹配项的分类。

解决方案 #

方法1：预生成分类别名+JSON_TABLE拆分替换（MariaDB 10.6+适用） #

先预计算所有产品的分类别名，再通过JSON_TABLE拆分每个链接进行精准替换：

1. 创建临时表存储产品ID与对应分类别名：

CREATE TEMPORARY TABLE product_category_alias (
    id_product INT PRIMARY KEY,
    category_alias VARCHAR(255)
);

INSERT INTO product_category_alias
SELECT 
    p.id_product,
    REPLACE(LOWER(cl.name), ' ', '-') AS category_alias
FROM ps_product p
LEFT JOIN ps_category_lang cl ON p.id_category_default = cl.id_category;

2. 执行更新操作：

UPDATE ps_product_lang pl
SET pl.description = (
    SELECT CONCAT('<ul>', GROUP_CONCAT('<li>', new_link, '</li>'), '</ul>')
    FROM (
        SELECT 
            REGEXP_REPLACE(
                link,
                '<a href="https://onedomain.com/(.*)-([0-9]{1,5})(?:-.*\\.html|\\.html)',
                CONCAT(
                    '<a href="https://newdomain.com/',
                    pca.category_alias,
                    '/\\2-', REPLACE(LOWER(pl.name), ' ', '-'), '.html'
                )
            ) AS new_link
        FROM JSON_TABLE(
            CONCAT('["', REPLACE(REPLACE(pl.description, '<li>', ''), '</li>', '","'), '"]'),
            '$[*]' COLUMNS (link VARCHAR(1000) PATH '$')
        ) AS links
        JOIN product_category_alias pca 
            ON REGEXP_SUBSTR(link, '-([0-9]{1,5})-', 1, 1, '', 1) = pca.id_product
        WHERE link LIKE '%<a href="https://onedomain.com/%'
    ) AS replaced_links
);

方法2：存储过程循环处理（兼容低版本MariaDB） #

如果不支持JSON_TABLE，用存储过程逐个处理每个链接：

DELIMITER //
CREATE PROCEDURE UpdateProductLinks()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE prod_id INT;
    DECLARE orig_desc TEXT;
    DECLARE new_desc TEXT;
    DECLARE cur CURSOR FOR SELECT id_product, description FROM ps_product_lang WHERE description LIKE '%https://onedomain.com/%';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO prod_id, orig_desc;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET new_desc = orig_desc;
        -- 循环替换每个匹配的链接
        WHILE new_desc REGEXP '<a href="https://onedomain.com/(.*)-([0-9]{1,5})(?:-.*\\.html|\\.html)' DO
            SET @match_id = REGEXP_SUBSTR(new_desc, '-([0-9]{1,5})-', 1, 1, '', 1);
            SELECT REPLACE(LOWER(cl.name), ' ', '-') INTO @cat_alias
            FROM ps_product p
            LEFT JOIN ps_category_lang cl ON p.id_category_default = cl.id_category
            WHERE p.id_product = @match_id;

            SET new_desc = REGEXP_REPLACE(
                new_desc,
                '<a href="https://onedomain.com/(.*)-([0-9]{1,5})(?:-.*\\.html|\\.html)',
                CONCAT(
                    '<a href="https://newdomain.com/',
                    @cat_alias,
                    '/\\2-', REPLACE(LOWER((SELECT name FROM ps_product_lang WHERE id_product = prod_id)), ' ', '-'), '.html'
                ),
                1, 1
            );
        END WHILE;

        UPDATE ps_product_lang SET description = new_desc WHERE id_product = prod_id;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

-- 调用存储过程执行更新
CALL UpdateProductLinks();

关键说明 #

• 临时表方法通过预计算分类别名，避免了正则替换中嵌套子查询的上下文问题，确保每个产品ID匹配正确的分类。
• 存储过程通过循环逐个处理每个链接，每次替换一个匹配项，实时查询对应产品的分类名称，适配低版本MariaDB。

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