msyql 中批量替换url网址中的域名方案。

小咪咪 2022-01-20 02:41 520阅读 0赞

文章：[blog.csdn.net/ycj1982/art…][blog.csdn.net_ycj1982_art]

最近做一个项目（项目是采用的第三方的内容php框架+mysql搭建的），项目中有个功能需要采集大量的文章数据，数据库中直接存储的是文章的网址（举例：[www.xxx.com/article?id=…][www.xxx.com_article_id]

所以网站采集过后需要批量处理文章的url网址更换成现有的域名，需要大批量的更换数据库文章链接，查看了项目原来更新的方式是采取读取文章URL，再后端程序批量替换，这样操作耗时而且会造成程序页面卡死终止运行。

想优化这种更新方式，思考着采用sql代码上操作速度应该很快。

通过实验mysql中replace 函数是不能直接使用正则表达式替换的。

网络搜索也没找到好的解决办法。通过自己研究采用replace 函数+ REGEXP 函数、再集合其他字符串处理做出来的批量更新url字符串中的网址。

解决方案和思路如下：

注释：id , url 是你数据库存储链接的字段名。以下举例都是以https:// 做的。

1、先验证域名是哪种方式（http 或者https）

a、如果url是以http或者https开头的则进入下一个验证：再次验证区分具体是HTTP 或者https 。

b、如果url不是以http或者https开通的则直接返回URL。

select if(url REGEXP '^(https://)|(http://)',if(url REGEXP '^(https://)','','0'), url ) ,id,url from table\_name

2、先排除url中的http:// 或者 https://  用于后续取值使用

SUBSTRING(url,LENGTH('https://')+1)  //返回的url值是不包含http:// 或者 https:// 的字符串

3、搜索定位已经删除（http:// 或者 https://）的字符串中的‘/’

LOCATE('/',SUBSTRING(url,LENGTH('https://')+1

//举例：URL= ‘[www.2333.com/a/ba/ba’][www.2333.com_a_ba_ba]

//删除https:// 后，URL = ‘www.2333.com/a/ba/ba’

//查找URL 中从左边第一个‘/’ 也就是定位域名后面的斜杠位置。用于后续把“[www.2333.com/”这个完整域名取出作为…][www.2333.com]

4、完整取出 url中的网址（格式：[www.2333.com/）。][www.2333.com 1]

SUBSTRING(url,1,LOCATE('/',SUBSTRING(url,LENGTH('https://')+1))+ LENGTH('https://') )

5、最后加上if 判断当前url 具体是哪种格式的网址，然后再采用对应的截取方式取出带替换掉的域名。（方便阅读代码做了换行）

REPLACE(url, if(url REGEXP '^(https://)|(http://)',if(url REGEXP '^(https://)',   SUBSTRING(url,1,LOCATE('/',SUBSTRING(url,LENGTH('https://')+1))+ LENGTH('https://') ) ,SUBSTRING(url,1,LOCATE('/',SUBSTRING(url,LENGTH('http://')+1))+ LENGTH('http://') ) ),url) ,"[t.w123.com/][t.w123.com]")

这是拿来修改的表中所有数据的sql代码：

update  table\_name  set url = REPLACE(url, if(url REGEXP '^(https://)|(http://)',if(url REGEXP '^(https://)',   SUBSTRING(url,1,LOCATE('/',SUBSTRING(url,LENGTH('https://')+1))+ LENGTH('https://') ) ,SUBSTRING(url,1,LOCATE('/',SUBSTRING(url,LENGTH('http://')+1))+ LENGTH('http://') ) ),url) ,"[t.w123.com/][t.w123.com]")

以上是我处理本次项目遇到的问题方案，处理更新效率比之前的后端程序快很多。发布记录下，如果大家有更好的建议方案欢迎留言交流谢谢。

[blog.csdn.net_ycj1982_art]: https://link.juejin.im?target=https%3A%2F%2Fblog.csdn.net%2Fycj1982%2Farticle%2Fdetails%2F89918412
[www.xxx.com_article_id]: https://link.juejin.im?target=http%3A%2F%2Fwww.xxx.com%2Farticle%3Fid%3D111%25EF%25BC%2589%25E3%2580%2582
[www.2333.com_a_ba_ba]: https://link.juejin.im?target=https%3A%2F%2Fwww.2333.com%2Fa%2Fba%2Fba%25E2%2580%2599
[www.2333.com]: https://link.juejin.im?target=https%3A%2F%2Fwww.2333.com%2F%25E2%2580%259D%25E8%25BF%2599%25E4%25B8%25AA%25E5%25AE%258C%25E6%2595%25B4%25E5%259F%259F%25E5%2590%258D%25E5%258F%2596%25E5%2587%25BA%25E4%25BD%259C%25E4%25B8%25BA%25E6%259B%25BF%25E6%258D%25A2%25E7%259A%2584%25E5%25AD%2597%25E7%25AC%25A6%25E4%25B8%25B2%25E3%2580%2582
[www.2333.com 1]: https://link.juejin.im?target=https%3A%2F%2Fwww.2333.com%2F%25EF%25BC%2589%25E3%2580%2582
[t.w123.com]: https://link.juejin.im?target=http%3A%2F%2Ft.w123.com%2F