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