MySQL：如何替换URL域名并移除其后的8位随机数字

针对你遇到的URL替换需求，这里分两种MySQL版本给出可行方案：

方案一：MySQL 8.0及以上版本（推荐，用正则表达式） #

利用REGEXP_REPLACE可以快速匹配并替换指定位置的内容，无需纠结域名长度：

处理Type A URL #

UPDATE your_table
SET url_col = REGEXP_REPLACE(
    -- 先替换旧域名为新域名
    REPLACE(url_col, 'https://www.old_example1.com', 'https://www.new_example1.com'),
    -- 正则匹配新域名后紧跟的8位数字及斜杠
    '^(https://www.new_example1.com)/[0-9]{8}/',
    -- 替换成新域名加斜杠，去掉中间的8位数字
    '$1/'
)
-- 只匹配符合Type A格式的URL，避免误改
WHERE url_col LIKE 'https://www.old_example1.com/[0-9]{8}/%';

处理Type B URL #

UPDATE your_table
SET url_col = REGEXP_REPLACE(
    REPLACE(url_col, 'https://www.old_example2.com', 'http://www.new_example2.com'),
    '^(http://www.new_example2.com)/[0-9]{8}/',
    '$1/'
)
WHERE url_col LIKE 'https://www.old_example2.com/[0-9]{8}/%';

方案二：MySQL 5.x版本（无正则支持，用字符串函数） #

如果你的MySQL版本不支持正则，可以通过定位斜杠位置来实现：

处理Type A URL #

UPDATE your_table
SET url_col = (
    SELECT CONCAT(
        -- 取新域名部分（到第三个斜杠为止）
        SUBSTRING(temp_url, 1, pos3),
        -- 取第四个斜杠之后的所有路径内容
        SUBSTRING(temp_url, pos4)
    )
    FROM (
        SELECT
            -- 先替换域名得到临时URL
            REPLACE(url_col, 'https://www.old_example1.com', 'https://www.new_example1.com') AS temp_url,
            -- 定位第三个斜杠的位置
            LOCATE('/', temp_url, LOCATE('/', temp_url, LOCATE('/', temp_url)+1)+1) AS pos3,
            -- 定位第四个斜杠的位置
            LOCATE('/', temp_url, pos3+1) AS pos4
        FROM your_table
        WHERE url_col LIKE 'https://www.old_example1.com/%/%'
    ) AS sub_query
)
WHERE url_col LIKE 'https://www.old_example1.com/%/%';

处理Type B URL #

UPDATE your_table
SET url_col = (
    SELECT CONCAT(
        SUBSTRING(temp_url, 1, pos3),
        SUBSTRING(temp_url, pos4)
    )
    FROM (
        SELECT
            REPLACE(url_col, 'https://www.old_example2.com', 'http://www.new_example2.com') AS temp_url,
            LOCATE('/', temp_url, LOCATE('/', temp_url, LOCATE('/', temp_url)+1)+1) AS pos3,
            LOCATE('/', temp_url, pos3+1) AS pos4
        FROM your_table
        WHERE url_col LIKE 'https://www.old_example2.com/%/%'
    ) AS sub_query
)
WHERE url_col LIKE 'https://www.old_example2.com/%/%';

注意事项 #

1. 替换前建议先运行SELECT语句测试结果，确认无误后再执行UPDATE
2. 如果URL后面的路径只有一级（比如/myname），也可以用更简洁的SUBSTRING_INDEX组合：

   UPDATE your_table
   SET url_col = CONCAT('https://www.new_example1.com/', SUBSTRING_INDEX(SUBSTRING_INDEX(url_col, '/', -2), '/', -1))
   WHERE url_col LIKE 'https://www.old_example1.com/[0-9]{8}/%';
   

   但这种方法不适用于路径包含多个斜杠的情况。

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