时间戳

首页 > 解决方案 > 条件 SQL 替换

sql - 条件 SQL 替换

问题描述

是否可以有条件地替换 MySQL 中的部分字符串？

问题介绍：我的数据库中的用户存储的文章（表称为“表”，列“值”，每行=一篇文章）带有错误的图像链接。我想一次修复所有这些。为此，我必须替换“href”链接中的所有地址，然后是图像，即 <a href="link1"><img src="link2"></a> 应该替换为 <a href="link2"><img src="link2"></a> 我的想法是搜索每个“href”标签，如果标签后面跟着“img” ”，而不是我想从图像中获取“link2”并使用它替换“link1”。我知道如何在 bash 或 python 中做到这一点，但我没有足够的 MySQL 经验。

具体来说，我的表包含对图像的引用，例如

<a href="www.a.cz/b/c"><img class="image image-thumbnail " src="www.d.cz/e/f.jpg" ...

我想用图片链接替换第一个地址（href）。要得到

<a href="www.d.cz/e/f.jpg"><img class="image image-thumbnail " src="www.d.cz/e/f.jpg" ...

是否可以进行查询（查询？），例如

UPDATE `table` 
SET value = REPLACE(value, 'www.a.cz/b/c', 'XXX') 
WHERE `value` LIKE '%www.a.cz/b/c%'

其中XXX每次都不同，其值是从数据库中获取的？此外，“www.a.cz/b/c”各不相同。

更复杂的是，并非所有图像都有“href”链接，也不是所有链接都指向图像。有三种可能：

  1. "href" 后跟 "img" -> 替换

  2. “href”后面没有“img”->保留原始链接（可能是指向另一个页面的链接）

  3. 没有“href”的“img” -> 什么都不做（没有要替换的错误链接）

当然，有些图片可能有正确的链接。在这种情况下，它也可能被替换（原始和新的将相同）。

来自 phpMyAdmin 的数据库信息

软件：MariaDB

软件版本：10.1.32-MariaDB - 源码分发

协议版本：10

服务器字符集：UTF-8 Unicode (utf8)

阿帕奇

数据库客户端版本：libmysql - 5.6.15

PHP 扩展：mysqli

先感谢您

标签： sqlreplacemariadb

解决方案

SELECT
  regexp_replace(
    value,
    '^<a href="([^"]+)"><img class="([^"]+)" src="([^"]+)"(.*)$',
    '<a href="\\3"><img class="\\2" src="\\3"\\4'
  )
FROM
  yourTable

仅当模式匹配时才会发生替换。

  • ^一开始的意思start of the string
  • ([^"]+)方法one of more characters, excluding "
  • (.*)表示零个或多个任何字符
  • $最后意味着end of the string

替换采用第三个“用大括号括起来的模式”（反向引用）并将其放在第一个“用大括号括起来的模式”（反向引用）所在的位置。

第 2、第 3 和第 4 个反向引用被替换为它们自己（没有变化）。

https://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=96aef2214f844a1466772f41415617e5

如果您的字符串与模式不完全匹配，它将什么也不做。例如，额外的空间会绊倒它。

在这种情况下，您需要制定一个始终匹配您要处理的所有字符串的新正则表达式。然后您可以使用\\n反向引用进行替换。

例如，以下处理href标签中的额外空格...

SELECT
  regexp_replace(
    value,
    '^<a[ ]+href[ ]*=[ ]*"([^"]+)"><img class="([^"]+)" src="([^"]+)"(.*)$',
    '<a href="\\3"><img class="\\2" src="\\3"\\4'
  )
FROM
  yourTable

编辑：

以下评论澄清了这些实际上是字符串中间的片段......

https://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=48ce1cc3df5bf4d3d140025b662072a7

UPDATE
  yourTable
SET
  value = REGEXP_REPLACE(
            value,
            '<a href="([^"]+)"><img class="([^"]+)" src="([^"]+)"',
            '<a href="\\3"><img class="\\2" src="\\3"'
         )
WHERE
  value REGEXP '<a href="([^"]+)"><img class="([^"]+)" src="([^"]+)"'

（虽然我更喜欢语法RLIKE，但它在功能上是相同的。）

这也将多次找到替换该模式。您不清楚这是需要还是可能。

推荐阅读