mysql - 如何从 mysql 数据库记录中删除 [mytag] 某些文本 [/mytag] 标签之间的文本？

Question

我有一个表叫

故事

还有一列

正文

每个正文都包含来自先前 CMS 的 php/html 代码，封装在标签 [soltag] [/soltag] 之间

例如一条记录看起来像这样:

Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting
[soltag]php, $end = ""; if (isset($_GET["vote"])) { $end .= "?vote=" . $_GET["vote"]; if (isset($_GET["vid"])) { $end .= "&vid=" . $_GET["vid"]; }; }; $output = file_get_contents("http://example.com/something.html" . $end);[/soltag] And the body text continues here.

我想从数据库中的每条记录中删除这些标签之间的所有内容，包括这些标签。

所以，在这之后它应该是这样的:

Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting  And the body text continues here.

我有大约 5000 条记录，因此手动操作至少需要 2-3 天的机器人式工作。

有什么想法可以用 mysql 命令删除它吗？

提前致谢。

Answer 1

我正在为类似的目的搜索类似的东西，这真的很有用。

只有一件事......对于下一个搜索同一问题的人。

如果您还想删除结束标记，您应该将结束标记的字符串长度加到 SUBSTRING 的第三个参数中:

UPDATE stories SET [body] = REPLACE([body],

                 SUBSTRING([body], 
                           INSTR([body],'[soltag]'), 
                           INSTR([body],'[/soltag]') +CHAR_LENGTH('[/soltag]') -INSTR([body],'[soltag]')
                          )
                 ,'');

希望对某人有所帮助!:)