在MariaDB中使用REGEXP_REPLACE查找和替换正则表达式的正确语法是什么? [英] What is the correct syntax for a Regex find-and-replace using REGEXP_REPLACE in MariaDB?

问题描述

我需要在名为post的MySQL表中对名为message的列运行正则表达式查找和替换.

I need to run a regex find-and-replace against a column named message in a MySQL table named post.

我的数据库正在运行MariaDB10.

My database is running MariaDB 10.

根据

According to the docs, MariaDB 10 has a new REGEXP_REPLACE function designed to do exactly this, but I can't seem to figure out the actual syntax.

它将影响280,000行，因此理想情况下，还有一种方法可以将其限制为在测试时一次只能更改一个特定行，或者只是执行SELECT而不是UPDATE直到我确保它可以满足我的要求.

It will affect 280,000 rows, so ideally there's also a way to limit it to only changing one specific row at a time while I'm testing it, or simply doing a SELECT rather than an UPDATE until I'm sure it does what I want.

我要运行的正则表达式: \[quote\sauthor=(.+)\slink=[^\]]+]

The regex I want to run: \[quote\sauthor=(.+)\slink=[^\]]+]

替换字符串: [quote="$1"]

以下是我尝试过的操作，但它只会引发SQL错误:

The following was what I tried, but it just throws a SQL error:

UPDATE post SET message = REGEXP_REPLACE(message, '\[quote\sauthor=(.+)\slink=[^\]]+]', '[quote="$1"]') WHERE post_id = 12

在这种情况下，原始消息是: [quote author=Jon_doe link=board=2;threadid=125;start=40#msg1206 date=1065088]，最终结果应为[quote="Jon_doe"]

In this case, the original message was: [quote author=Jon_doe link=board=2;threadid=125;start=40#msg1206 date=1065088] and the end result should be [quote="Jon_doe"]

使此REGEXP_REPLACE正常工作的正确语法是什么?

What is the proper syntax to make this REGEXP_REPLACE work?

推荐答案

您必须在此处进行很多转义操作:

You have to do a lot of escaping here:

REGEXP_REPLACE(message, "\\[quote\\sauthor=(.+)\\slink=[^\\]]+]", "\\[quote=\"\\1\"\\]")

请注意，您必须引用\\1

这篇关于在MariaDB中使用REGEXP_REPLACE查找和替换正则表达式的正确语法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！