如何去除SQL表中url字段的http://与https://前缀？

嘿，这个需求我日常工作里碰到好多次了！要批量去掉数据表url字段里的http://和https://前缀，不同数据库有不同的实现方式，我给你整理几个常用的靠谱方案：

MySQL/MariaDB #

如果你的MySQL是8.0及以上版本，用正则替换最方便：

-- 先验证清理后的结果，没问题再执行UPDATE
SELECT url, REGEXP_REPLACE(url, '^https?://', '') AS cleaned_url
FROM your_table
WHERE url REGEXP '^https?://';

-- 确认结果正确后，执行更新
UPDATE your_table
SET url = REGEXP_REPLACE(url, '^https?://', '')
WHERE url REGEXP '^https?://';

这里^https?://专门匹配字符串开头的http://或https://（?表示s是可选的），WHERE子句确保只处理有前缀的记录，不会动本来就没有前缀的URL。

如果是MySQL 5.x这类不支持REGEXP_REPLACE的低版本，可以用字符串函数组合实现：

-- 先验证
SELECT url, SUBSTRING(url, CASE
    WHEN LEFT(url, 8) = 'https://' THEN 9
    WHEN LEFT(url, 7) = 'http://' THEN 8
    ELSE 1
END) AS cleaned_url
FROM your_table
WHERE LEFT(url, 7) = 'http://' OR LEFT(url, 8) = 'https://';

-- 执行更新
UPDATE your_table
SET url = SUBSTRING(url, CASE
    WHEN LEFT(url, 8) = 'https://' THEN 9
    WHEN LEFT(url, 7) = 'http://' THEN 8
    ELSE 1
END)
WHERE LEFT(url, 7) = 'http://' OR LEFT(url, 8) = 'https://';

PostgreSQL #

PostgreSQL对正则的支持很友好，写法类似MySQL，还可以加不区分大小写的参数（如果需要处理大写前缀的话）：

-- 验证结果
SELECT url, REGEXP_REPLACE(url, '^https?://', '', 'i') AS cleaned_url
FROM your_table
WHERE url ~ '^https?://';

-- 执行更新
UPDATE your_table
SET url = REGEXP_REPLACE(url, '^https?://', '', 'i')
WHERE url ~ '^https?://';

'i'参数会让匹配忽略大小写，比如能处理HTTP://example.com这种情况；如果不需要可以去掉这个参数。

SQL Server #

SQL Server 2017及以上可以用嵌套REPLACE，但更严谨的是用STUFF函数确保只去掉开头的前缀：

-- 先验证
SELECT url, STUFF(url, 1, CASE
    WHEN CHARINDEX('https://', url) = 1 THEN 8
    WHEN CHARINDEX('http://', url) = 1 THEN 7
    ELSE 0
END, '') AS cleaned_url
FROM your_table
WHERE CHARINDEX('http://', url) = 1 OR CHARINDEX('https://', url) = 1;

-- 执行更新
UPDATE your_table
SET url = STUFF(url, 1, CASE
    WHEN CHARINDEX('https://', url) = 1 THEN 8
    WHEN CHARINDEX('http://', url) = 1 THEN 7
    ELSE 0
END, '')
WHERE CHARINDEX('http://', url) = 1 OR CHARINDEX('https://', url) = 1;

如果追求简单，也可以用嵌套REPLACE，但要注意如果URL中间意外出现http://也会被替换（这种情况极少）：

UPDATE your_table
SET url = REPLACE(REPLACE(url, 'https://', ''), 'http://', '')
WHERE url LIKE 'http://%' OR url LIKE 'https://%';

重要提示 #

不管用哪种方案，一定要先执行SELECT语句验证清理结果，确认没问题再执行UPDATE操作，最好提前备份数据表，避免误操作导致数据丢失！

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