使用 REGEXP 动态更改 URL 字符串的 SQL 查询 [英] 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.

推荐答案

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

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://或 'http:// 的位置查找匹配项，后跟 any characters 后跟 /images/，后跟 任何数字，4 或 5 次，后跟 [，或字母或空格，然后是任何字符.

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 函数的参考:https://mariadb.com/kb/en/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

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