更新：现在已将此内容转为博客文章：http://stevettt.blogspot.co.uk/2018/02/a-mysql-regular-expression-replace.html
请查看以下Rextester Fiddle，我认为应该能够产生您所要求的所有结果:
Rextester Demo
说明
这需要一个模式替换函数，但不幸的是MySQL没有提供这样的功能。因此，我写了一个（基于另一个不太够用的函数），并发布在这里。如引用答案中所述，此函数有一个限制，即不能使用反向引用替换捕获组。因此，在fiddle中稍微调整了一下，以接受进一步的参数，并允许它在找到匹配项后对替换进行递归替换。（注意正则表达式中允许的URL路径字符，参见这个优秀的答案）。
更新SQL
以下SQL将使用下面的函数更新表数据:
UPDATE urls
SET url = reg_replace(
url,
'www\\.mydomain\\.de/[-A-Za-z0-9\\._~!\\$&''\\(\\)\\*\\+,;=:@%/]+\\.html',
'/[^/]+\\.html',
'/page/',
TRUE,
22, -- Min match length = www.mydomain.de/?.html = 22
0, -- No max match length
7, -- Min sub-match length = /?.html = 7
0 -- No max sub-match length
);
函数代码
演示中使用的UDF代码如下所示。注意：该UDF委托给存储过程，因为只有存储过程才允许在MySQL中进行递归。
DROP FUNCTION IF EXISTS reg_replace;
DELIMITER //
CREATE FUNCTION reg_replace(subject VARCHAR(21845), pattern VARCHAR(21845),
subpattern VARCHAR(21845), replacement VARCHAR(21845), greedy BOOLEAN,
minMatchLen INT, maxMatchLen INT, minSubMatchLen INT, maxSubMatchLen INT)
RETURNS VARCHAR(21845) DETERMINISTIC BEGIN
DECLARE result VARCHAR(21845);
CALL reg_replace_worker(
subject, pattern, subpattern, replacement, greedy, minMatchLen, maxMatchLen,
minSubMatchLen, maxSubMatchLen, result);
RETURN result;
END;//
DELIMITER ;
DROP PROCEDURE IF EXISTS reg_replace_worker;
DELIMITER //
CREATE PROCEDURE reg_replace_worker(subject VARCHAR(21845), pattern VARCHAR(21845),
subpattern VARCHAR(21845), replacement VARCHAR(21845), greedy BOOLEAN,
minMatchLen INT, maxMatchLen INT, minSubMatchLen INT, maxSubMatchLen INT,
OUT result VARCHAR(21845))
BEGIN
DECLARE subStr, usePattern, useRepl VARCHAR(21845);
DECLARE startPos, prevStartPos, startInc, len, lenInc INT;
SET @@SESSION.max_sp_recursion_depth = 2;
IF subject REGEXP pattern THEN
SET result = '';
SET minMatchLen = IF(minMatchLen < 1, 1, minMatchLen);
SET maxMatchLen = IF(maxMatchLen < 1 OR maxMatchLen > CHAR_LENGTH(subject),
CHAR_LENGTH(subject), maxMatchLen);
SET usePattern = IF (LEFT(pattern, 1) = '^', pattern, CONCAT('^', pattern));
SET usePattern = IF (RIGHT(pattern, 1) = '$', usePattern, CONCAT(usePattern, '$'));
IF LEFT(pattern, 1) = '^' OR RIGHT(pattern, 1) <> '$' THEN
SET startPos = 1, startInc = 1;
ELSEIF greedy THEN
SET startPos = CHAR_LENGTH(subject) - maxMatchLen + 1, startInc = 1;
ELSE
SET startPos = CHAR_LENGTH(subject) - minMatchLen + 1, startInc = -1;
END IF;
WHILE startPos >= 1 AND startPos <= CHAR_LENGTH(subject)
AND startPos + minMatchLen - 1 <= CHAR_LENGTH(subject)
AND !(LEFT(pattern, 1) = '^' AND startPos <> 1)
AND !(RIGHT(pattern, 1) = '$'
AND startPos + maxMatchLen - 1 < CHAR_LENGTH(subject)) DO
IF greedy OR RIGHT(pattern, 1) = '$' THEN
SET len = LEAST(CHAR_LENGTH(subject) - startPos + 1, maxMatchLen), lenInc = -1;
ELSE
SET len = minMatchLen, lenInc = 1;
END IF;
SET prevStartPos = startPos;
lenLoop: WHILE len >= 1 AND len <= maxMatchLen
AND startPos + len - 1 <= CHAR_LENGTH(subject)
AND !(RIGHT(pattern, 1) = '$'
AND startPos + len - 1 <> CHAR_LENGTH(subject)) DO
SET subStr = SUBSTRING(subject, startPos, len);
IF subStr REGEXP usePattern THEN
IF subpattern IS NULL THEN
SET useRepl = replacement;
ELSE
CALL reg_replace_worker(subStr, subpattern, NULL, replacement, greedy,
minSubMatchLen, maxSubMatchLen, NULL, NULL, useRepl);
END IF;
SET result = IF(startInc = 1,
CONCAT(result, useRepl), CONCAT(useRepl, result));
SET startPos = startPos + startInc * len;
LEAVE lenLoop;
END IF;
SET len = len + lenInc;
END WHILE;
IF (startPos = prevStartPos) THEN
SET result = IF(startInc = 1, CONCAT(result, SUBSTRING(subject, startPos, 1)),
CONCAT(SUBSTRING(subject, startPos, 1), result));
SET startPos = startPos + startInc;
END IF;
END WHILE;
IF startInc = 1 AND startPos <= CHAR_LENGTH(subject) THEN
SET result = CONCAT(result, RIGHT(subject, CHAR_LENGTH(subject) + 1 - startPos));
ELSEIF startInc = -1 AND startPos >= 1 THEN
SET result = CONCAT(LEFT(subject, startPos), result);
END IF;
ELSE
SET result = subject;
END IF;
END;//
DELIMITER ;
