如何仅使用MySQL将对话表消息字段中的URL转换为HTML链接

刚好之前处理过类似的需求，不用额外写代码，靠MySQL本身的功能就能完美解决！先说明下，MySQL 8.0及以上版本支持正则替换，这是最便捷的方式；如果你的版本比较老（比如5.x），我也会给你一个替代方案。

针对MySQL 8.0+的快速解法 #

直接用REGEXP_REPLACE函数匹配消息里的URL，替换成你要的HTML链接格式就行：

UPDATE conversation
SET messages = REGEXP_REPLACE(
    messages,
    '(https?://[^\s,.;!?]+)',
    '<a href="$1" target="_blank">Link to web</a>'
);

给你解释下这个正则的逻辑：

• https?://：匹配http://或者https://，?表示s是可选的，两种协议都能覆盖
• [^\s,.;!?]+：匹配URL的主体部分，这里排除了空格、逗号、分号、句号这些常见的URL结尾标点，避免把https://example.com,里的逗号也当成URL的一部分

实际效果示例

假设你的messages列里有这么一条内容：

"Hey, check out https://exampleweb1.com or enter in: http://exampleweb2.com!"

执行上面的UPDATE语句后，内容会变成：

"Hey, check out Link to web or enter in: Link to web!"

完全符合你要的效果！

要是你用的是MySQL 5.x版本（无REGEXP_REPLACE） #

那就得自定义一个函数来循环查找替换URL了，我给你写好现成的：

DELIMITER //
CREATE FUNCTION replace_urls(input_text TEXT)
RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE url_start INT;
    DECLARE url_end INT;
    DECLARE url TEXT;
    DECLARE result TEXT DEFAULT input_text;
    
    -- 循环找所有URL，直到没有为止
    WHILE LOCATE('http://', result) > 0 OR LOCATE('https://', result) > 0 DO
        -- 定位第一个URL的起始位置，优先找出现更早的
        SET url_start = LEAST(
            IF(LOCATE('http://', result) > 0, LOCATE('http://', result), 999999),
            IF(LOCATE('https://', result) > 0, LOCATE('https://', result), 999999)
        );
        -- 定位URL的结束位置，遇到空格或常见标点就停
        SET url_end = LEAST(
            IF(LOCATE(' ', result, url_start) > 0, LOCATE(' ', result, url_start), LENGTH(result)+1),
            IF(LOCATE(',', result, url_start) > 0, LOCATE(',', result, url_start), LENGTH(result)+1),
            IF(LOCATE(';', result, url_start) > 0, LOCATE(';', result, url_start), LENGTH(result)+1),
            IF(LOCATE('.', result, url_start) > 0, LOCATE('.', result, url_start), LENGTH(result)+1),
            IF(LOCATE('!', result, url_start) > 0, LOCATE('!', result, url_start), LENGTH(result)+1),
            IF(LOCATE('?', result, url_start) > 0, LOCATE('?', result, url_start), LENGTH(result)+1)
        );
        -- 提取出找到的URL
        SET url = SUBSTRING(result, url_start, url_end - url_start);
        -- 替换成HTML链接
        SET result = REPLACE(result, url, CONCAT('<a href="', url, '" target="_blank">Link to web</a>'));
    END WHILE;
    
    RETURN result;
END //
DELIMITER ;

创建好函数后，直接调用更新数据就行：

UPDATE conversation
SET messages = replace_urls(messages);

最后提醒几句 #

• 不管用哪种方法，先备份数据再执行UPDATE！别手滑把数据搞坏了
• 如果你的消息里URL有特殊格式（比如带下划线、@之类的），可以调整正则里的排除字符，比如把[^\s,.;!?]+改成[^\s]+（但这样可能会把后面的标点也包含进去，得根据实际情况权衡）
• 两种方法都会替换消息里所有符合规则的URL，不管是单独的还是在句子中间的

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