使用 REGEXP 动态更改 URL 字符串的 SQL 查询

SQL Query with REGEXP to change URL strings dynamically(使用 REGEXP 动态更改 URL 字符串的 SQL 查询)

问题描述

我的名为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 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！