使用REGEXP进行SQL查询以动态更改URL字符串 [英] SQL Query with REGEXP to change URL strings dynamically

问题描述

我的名为"post"的数据库表的确如下所示

My DB table named "post" does look as follows

id   |   message
----------------
1    |   test
2    |   Here is your image link: [LINK]https://example.com/images/1234[/LINK] You can view it now.
3    |   some strings
4    |   Here is your image link: [LINK]https://example.com/images/5678[/LINK] You can view it now.
5    |   [LINK]no correct url[/LINK]
6    |   [LINK][IMG]https://example.com/images/9123[/IMG][/LINK]
7    |   [LINK]https://example.com/images/912364[/LINK]
8    |   [LINK]Some text https://example.com/images/23456 Text again[/LINK]
9    |   [URL="https://example.com/images/10796"]

因此，并非每个消息行都包含一个URL，也不是每个带有[LINK]标签的消息都包含一个正确的URL.还有一些具有较长ID的实体，不应更改.

So not every message row does contain an url and not every message with a [LINK]-tag does contain a proper url. Also there are enrties which have a longer ID, they should not be changed.

现在我必须更改ID长度在4到5个字符之间的每个条目:

Now i have to change every entry which has an ID length between 4 and 5 characters:

https://example.com/images/1234
https://example.com/images/5678

以这种格式->添加文件扩展名

To that format -> adding a file extension

https://example.com/images/1234.png
https://example.com/images/5678.png

因此，"ID"等于文件名.仅替换URL并不难，但是我必须添加静态文件扩展名，在我的情况下，它是URL字符串末尾的".png".

So the "ID" is equal to the filename. Replacing just the URL isn't that hard, but i have to add the static file extension, which is in my case ".png" at the end of the URL string.

编辑//

最后，我的数据库表应该是这样的

At the end, my DB table should look like that

id   |   message
----------------
1    |   test
2    |   Here is your image link: [LINK]https://example.com/images/1234.png[/LINK] You can view it now.
3    |   some strings
4    |   Here is your image link: [LINK]https://example.com/images/5678.png[/LINK] You can view it now.
5    |   [LINK]no correct url[/LINK]
6    |   [LINK][IMG]https://example.com/images/9123.png[/IMG][/LINK]
7    |   [LINK]https://example.com/images/912364[/LINK]
8    |   [LINK]Some text https://example.com/images/23456.png Text again[/LINK]
9    |   [URL="https://example.com/images/10796.png"]

仅在"URL-ID"具有4或5位数字并且只有URL匹配的情况下，才将文件扩展名添加到URL.

That adding the file extension to the URL only where the "URL-ID" has 4 or 5 digits and only if the URL matches.

我绝对不是有经验的SQL用户.

I'm absolutely no experienced SQL user.

推荐答案

您可以在8.0及更高版本的MySQL中使用正则表达式:

You can use Regular expressions in MySQL, from 8.0 and on:

SELECT message AS original, REGEXP_REPLACE(message, '((http://|https://).*/images/[0-9]+)', '$1.png') AS new
  FROM Post 
  WHERE message REGEXP '.*(http://|https://).*/images/([0-9]{4,5})(?![0-9]).*'

where子句只是在找到https:// or 'http://的位置查找匹配项，然后依次是any characters，/images/，any number, 4 or 5 times，[, or a letter or space和任意字符.

The where clause simply finds matches where https:// or 'http:// is found, followed by any characters followed by /images/, followed by any number, 4 or 5 times, followed by a [, or a letter or space then any characters.

(?![0-9])很重要，因为.*将匹配任何字符，包括数字.因此，如果没有它，将找到具有6个以上数字的匹配项.它的基本意思是除了数字什么都没有."

The (?![0-9]) is important as the .* will match any characters, including numbers. So without it, matches with 6+ numbers would be found. It basically means "Anything but a number".

正则表达式使用捕获组捕获数字之前的所有内容，并将其替换为自身以及.png.

The regexp is using a capture group to capture everything before the numbers, and replace it with itself, plus the .png.

Here is your image link: [LINK]https://example.com/images/1234.png[/LINK] You can view it now.
Here is your image link: [LINK]https://example.com/images/5678.png[/LINK] You can view it now.
[LINK][IMG]https://example.com/images/9123.png[/IMG][/LINK]
[LINK]Some text https://example.com/images/23456.png Text again[/LINK]
[URL="https://example.com/images/10796.png"]

DBFiddle

https://www.db-fiddle.com/#&togetherjs = 4qC2I51yju

因为您使用的是MariaDB 10，请查看REGEXP_REPLACE函数的参考:>: //mariadb.com/kb/zh-CN/regexp_replace/

Becuase you are using MariaDB 10, Taking a look at the reference for the REGEXP_REPLACE function: https://mariadb.com/kb/en/regexp_replace/

您需要使用\\1，而不是$1.因此，如果您使用的是MariaDB，请用\\1.png替换$1.png.

You need to use \\1, not $1. So replace $1.png with \\1.png if you are using MariaDB.

用于更新的最终查询:

UPDATE Post SET message = REGEXP_REPLACE(message, '((http://|https://).*/images/[0-9]+)', '$1.png')
      WHERE message REGEXP '.*(http://|https://).*/images/([0-9]{4,5})(?!0-9]).*';

将MariaDB的$1.png更改为\\1.png

Change $1.png to \\1.png for MariaDB

这篇关于使用REGEXP进行SQL查询以动态更改URL字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！