如何在MySQL中进行正则表达式替换？

Question

我有一张包含约500k行的表格；其中一个名为filename的varchar(255) UTF8列中包含文件名。

我想从文件名中去除各种奇怪字符 - 我打算使用字符类：[^a-zA-Z0-9()_ .\-]

现在，MySQL是否有一种函数可以让你通过正则表达式进行替换？我正在寻找与REPLACE()函数类似的功能 - 下面是一个简化的示例：

SELECT REPLACE('stackowerflow', 'ower', 'over');

Output: "stackoverflow"

/* does something like this exist? */
SELECT X_REG_REPLACE('Stackoverflow','/[A-Zf]/','-'); 

Output: "-tackover-low"

我知道关于REGEXP/RLIKE的用法，但那些只检查是否有匹配项，而不是匹配项是什么。

(我可以从PHP脚本中执行"SELECT pkey_id,filename FROM foo WHERE filename RLIKE '[^a-zA-Z0-9()_ .\-]'"，进行preg_replace，然后执行"UPDATE foo ... WHERE pkey_id=..."，但看起来这似乎是一个最后的手段，并且效率低下且难看)

- Piskvor left the building

---

这是自2007年以来的一个功能请求：http://bugs.mysql.com/bug.php?id=27389。如果你真的想要这个功能，请登录并点击“影响我”按钮，希望它能得到足够的投票支持。 - Tomas

@Tomas：我在2009年寻找时做过这件事情......由于目前没有任何进展 - 显然这不是一个很重要的功能。（顺便说一句，Postgres有它：https://dev59.com/V2gt5IYBdhLWcg3w7BkE） - Piskvor left the building

与此问题相关的简化版本：https://dev59.com/D2w05IYBdhLWcg3w6GLH - Kzqai

我已经创建了“regexp_split”（函数+过程）和“regexp_replace”，这些都是使用“REGEXP”运算符实现的。对于简单的查找，它很管用。你可以在这里找到它：https://github.com/almadomundo/mysql-regexp，这就是使用MySQL存储代码的方式，而不是UDF。如果您发现一些未被已知限制所覆盖的错误，请随时开启问题。 - Alma Do

在我看来，选择使用PHP再更新MySQL并不是一种糟糕的“hack”方式。当然，如果可能的话，我更喜欢使用MySQL本地方法——据说这样会更快速。但是我的系统大量使用了PHP，在几秒钟内就能完成数十万次这样的替换操作（我构建的系统正是专门为此类工作设计的）。 - Buttle Butkus

在另一个 SO 线程中发现了这个库：https://github.com/mysqludf/lib_mysqludf_preg，它完美地工作。 - Kyle

Answer 1

如果你正在使用MariaDB或MySQL 8.0，它们都有一个函数

REGEXP_REPLACE(col, regexp, replace)

请参考MariaDB文档和PCRE正则表达式增强

请注意，您还可以使用正则表达式分组（我发现这非常有用）：

SELECT REGEXP_REPLACE("stackoverflow", "(stack)(over)(flow)", '\\2 - \\1 - \\3')

返回

over - stack - flow

- Benvorth

---

这是来自Mariadb 10。 - Nick

下次需要时，更改整个列的语法如下：UPDATE table SET Name = REGEXP_REPLACE(Name, "-2$", "\\1")。这将一次性从整个列中删除abcxyz-2中的“-2”。 - Josiah

改变整个平台并不是一个现实的解决方案。 - David Baucum

MariaDB是MySQL的即插即用替代品，因此它并不会改变平台，就像为同一旅行选择不同航空公司一样。 - Benvorth

MySQL 8.0也支持这个功能。 - Lukasz Szozda

Answer 2

MySQL 8.0+：

你可以使用本地的REGEXP_REPLACE函数。

旧版本：

你可以使用一个用户定义的函数 (UDF)，例如mysql-udf-regexp。

- Jeremy Stein

---

REGEXP_REPLACE作为用户定义函数？听起来很有前途，我会进一步研究。谢谢！ - Piskvor left the building

很遗憾，mysql-udf-regexp似乎不支持多字节字符。regexp_replace('äöõü', 'ä', '')返回的是一长串数字而不是实际文本。 - lkraav

MySQL本身不支持在其正则表达式功能中使用多字节字符。 - Brad

Windows 用户：此处链接的 UDF 库似乎对 Windows 支持不太好。我尝试按照指南进行安装，但在 Windows 上无法正常工作。 - Jonathan

@lkraav，你应该尝试一下下面的lib_mysqludf_preg库，因为它非常好用。这是冗长版本，因为默认情况下它返回一个blob，我不知道你是否有一个多字节字符集作为默认值： 选择cast(T.R as char)COLLATE utf8_unicode_ci 从 （选择preg_replace('/ä/','','öõüä')R）T - gillyspy

Answer 3

我用的暴力方法是：

1. 导出表格 - mysqldump -u 用户名 -p 数据库名 表格名 > dump.sql
2. 查找和替换一些模式 - find /path/to/dump.sql -type f -exec sed -i 's/旧字符串/新字符串/g' {} \;，当然您也可以对文件执行其他perl正则表达式。
3. 导入表格 - mysqlimport -u 用户名 -p 数据库名 表格名 < dump.sql

如果您想确保该字符串不在数据集中的其他位置，可以运行几个正则表达式以确保它们都出现在类似的环境中。在运行替换之前创建备份也不那么困难，以防意外删除了深度信息。

- Ryan Ward Valverde

---

好的，那也可以运作；我没有考虑离线替换。你的创意真是超出预期！ - Piskvor left the building

对我来说，你使用find命令似乎有些奇怪。我会将命令简化为sed -i 's/旧字符串/新字符串/g' /路径/到/dump.sql。 - speshak

对于大型数据集或已设置参照完整性的情况下，这种方法非常危险且不切实际：如果要删除数据并重新插入，则必须关闭参照完整性。这实际上会使您的数据库关闭。 - Raul Luna

过去我曾使用过这种方法，我同意Raul的观点，这非常危险。你必须要确信，你的字符串在数据集中没有出现过。 - eggmatters

很晚才回答@speshak，但我选择像这样访问文件的原因是因为我最初也非常紧张，就像上面提到的那样。当时，将“查找文件”部分与“替换”部分分开似乎会使代码更易于阅读，然后再提交它。 - Ryan Ward Valverde

Answer 4

使用 MySQL 8.0+，您可以原生地使用 REGEXP_REPLACE 函数。

12.5.2 正则表达式：

REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])

将字符串 expr 中与模式 pat 匹配的所有出现替换为字符串 repl，并返回结果字符串。如果 expr、pat 或 repl 为 NULL，则返回值为 NULL。

以及正则表达式支持：

先前，MySQL 使用 Henry Spencer 正则表达式库支持正则表达式运算符 (REGEXP，RLIKE)。

正则表达式支持已使用 International Components for Unicode (ICU) 重新实现，提供完整的 Unicode 支持并且支持多字节字符。函数 REGEXP_LIKE() 以 REGEXP 和 RLIKE 运算符的方式执行正则表达式匹配，这两个运算符现在是该函数的同义词。此外，REGEXP_INSTR()、REGEXP_REPLACE() 和 REGEXP_SUBSTR() 函数可用于查找匹配位置以及执行子字符串替换和提取。

SELECT REGEXP_REPLACE('Stackoverflow','[A-Zf]','-',1,0,'c'); 
-- Output:
-tackover-low

DBFiddle演示

- Lukasz Szozda

Answer 5

最近我编写了一个MySQL函数来使用正则表达式替换字符串。您可以在以下位置找到我的帖子：

http://techras.wordpress.com/2011/06/02/regex-replace-for-mysql/

以下是该函数的代码：

DELIMITER $$

CREATE FUNCTION  `regex_replace`(pattern VARCHAR(1000),replacement VARCHAR(1000),original VARCHAR(1000))
RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN 
 DECLARE temp VARCHAR(1000); 
 DECLARE ch VARCHAR(1); 
 DECLARE i INT;
 SET i = 1;
 SET temp = '';
 IF original REGEXP pattern THEN 
  loop_label: LOOP 
   IF i>CHAR_LENGTH(original) THEN
    LEAVE loop_label;  
   END IF;
   SET ch = SUBSTRING(original,i,1);
   IF NOT ch REGEXP pattern THEN
    SET temp = CONCAT(temp,ch);
   ELSE
    SET temp = CONCAT(temp,replacement);
   END IF;
   SET i=i+1;
  END LOOP;
 ELSE
  SET temp = original;
 END IF;
 RETURN temp;
END$$

DELIMITER ;

示例执行：

mysql> select regex_replace('[^a-zA-Z0-9\-]','','2my test3_text-to. check \\ my- sql (regular) ,expressions ._,');

- rasika godawatte

---

我只是想强调上面的观点：此功能替换与单个字符表达式匹配的字符。上面说它用于“使用正则表达式替换字符串”，这可能有点误导。它完成了它的工作，但这不是所要求的工作。（并非抱怨-只是为了避免让人们走错路） - Jason

很好 - 但不幸的是它不能处理像 select regex_replace('.*(abc).*','\1','noabcde') 这样的引用（返回'noabcde'而不是'abc'）。 - Izzy

我已经修改了这个方法，试图解决上述一些限制和更多的问题。请参见此答案。 - Steve Chambers

@Izzy MySQL 8+内置的REGEXP_REPLACE函数也不行，对吧？我正在尝试，但似乎不起作用。 - golimar

@golimar 我不知道。而且我也无法测试，因为我没有 MySQL 8 或更高版本。 - Izzy

Answer 6

我们解决这个问题时没有使用正则表达式，此查询仅替换完全匹配的字符串。

update employee set
employee_firstname = 
trim(REPLACE(concat(" ",employee_firstname," "),' jay ',' abc '))

示例：

emp_id employee_firstname

1 jay

2 jay ajay

3 jay

执行查询后的结果：

emp_id employee_firstname

1 abc

2 abc ajay

3 abc

- Jay Patel

---

@yellowmelon 双引号的两对是用来做什么的？ - codecowboy

他在员工姓名前后填充空格。这使他能够进行(space)员工姓名(space)的搜索和替换，从而避免捕捉到员工姓名"jay"，如果它是较大字符串"ajay"的一部分。完成后，他会删除这些空格。 - Slam

Answer 7

更新2： MySQL 8.0 现已提供一组有用的正则表达式函数，包括REGEXP_REPLACE。这意味着，除非你受限于使用早期版本，否则无需进行读取。

---

更新1：现在已将此内容制作成博客文章：http://stevettt.blogspot.co.uk/2018/02/a-mysql-regular-expression-replace.html

---

下面对Rasika Godawatte提供的函数进行了扩展，它遍历了所有必要的子字符串，而不仅仅是测试单个字符：

-- ------------------------------------------------------------------------------------
-- USAGE
-- ------------------------------------------------------------------------------------
-- SELECT reg_replace(<subject>,
--                    <pattern>,
--                    <replacement>,
--                    <greedy>,
--                    <minMatchLen>,
--                    <maxMatchLen>);
-- where:
-- <subject> is the string to look in for doing the replacements
-- <pattern> is the regular expression to match against
-- <replacement> is the replacement string
-- <greedy> is TRUE for greedy matching or FALSE for non-greedy matching
-- <minMatchLen> specifies the minimum match length
-- <maxMatchLen> specifies the maximum match length
-- (minMatchLen and maxMatchLen are used to improve efficiency but are
--  optional and can be set to 0 or NULL if not known/required)
-- Example:
-- SELECT reg_replace(txt, '^[Tt][^ ]* ', 'a', TRUE, 2, 0) FROM tbl;
DROP FUNCTION IF EXISTS reg_replace;
DELIMITER //
CREATE FUNCTION reg_replace(subject VARCHAR(21845), pattern VARCHAR(21845),
  replacement VARCHAR(21845), greedy BOOLEAN, minMatchLen INT, maxMatchLen INT)
RETURNS VARCHAR(21845) DETERMINISTIC BEGIN 
  DECLARE result, subStr, usePattern VARCHAR(21845); 
  DECLARE startPos, prevStartPos, startInc, len, lenInc INT;
  IF subject REGEXP pattern THEN
    SET result = '';
    -- Sanitize input parameter values
    SET minMatchLen = IF(minMatchLen IS NULL OR minMatchLen < 1, 1, minMatchLen);
    SET maxMatchLen = IF(maxMatchLen IS NULL OR maxMatchLen < 1
                         OR maxMatchLen > CHAR_LENGTH(subject),
                         CHAR_LENGTH(subject), maxMatchLen);
    -- Set the pattern to use to match an entire string rather than part of a string
    SET usePattern = IF (LEFT(pattern, 1) = '^', pattern, CONCAT('^', pattern));
    SET usePattern = IF (RIGHT(pattern, 1) = '$', usePattern, CONCAT(usePattern, '$'));
    -- Set start position to 1 if pattern starts with ^ or doesn't end with $.
    IF LEFT(pattern, 1) = '^' OR RIGHT(pattern, 1) <> '$' THEN
      SET startPos = 1, startInc = 1;
    -- Otherwise (i.e. pattern ends with $ but doesn't start with ^): Set start pos
    -- to the min or max match length from the end (depending on "greedy" flag).
    ELSEIF greedy THEN
      SET startPos = CHAR_LENGTH(subject) - maxMatchLen + 1, startInc = 1;
    ELSE
      SET startPos = CHAR_LENGTH(subject) - minMatchLen + 1, startInc = -1;
    END IF;
    WHILE startPos >= 1 AND startPos <= CHAR_LENGTH(subject)
      AND startPos + minMatchLen - 1 <= CHAR_LENGTH(subject)
      AND !(LEFT(pattern, 1) = '^' AND startPos <> 1)
      AND !(RIGHT(pattern, 1) = '$'
            AND startPos + maxMatchLen - 1 < CHAR_LENGTH(subject)) DO
      -- Set start length to maximum if matching greedily or pattern ends with $.
      -- Otherwise set starting length to the minimum match length.
      IF greedy OR RIGHT(pattern, 1) = '$' THEN
        SET len = LEAST(CHAR_LENGTH(subject) - startPos + 1, maxMatchLen), lenInc = -1;
      ELSE
        SET len = minMatchLen, lenInc = 1;
      END IF;
      SET prevStartPos = startPos;
      lenLoop: WHILE len >= 1 AND len <= maxMatchLen
                 AND startPos + len - 1 <= CHAR_LENGTH(subject)
                 AND !(RIGHT(pattern, 1) = '$' 
                       AND startPos + len - 1 <> CHAR_LENGTH(subject)) DO
        SET subStr = SUBSTRING(subject, startPos, len);
        IF subStr REGEXP usePattern THEN
          SET result = IF(startInc = 1,
                          CONCAT(result, replacement), CONCAT(replacement, result));
          SET startPos = startPos + startInc * len;
          LEAVE lenLoop;
        END IF;
        SET len = len + lenInc;
      END WHILE;
      IF (startPos = prevStartPos) THEN
        SET result = IF(startInc = 1, CONCAT(result, SUBSTRING(subject, startPos, 1)),
                        CONCAT(SUBSTRING(subject, startPos, 1), result));
        SET startPos = startPos + startInc;
      END IF;
    END WHILE;
    IF startInc = 1 AND startPos <= CHAR_LENGTH(subject) THEN
      SET result = CONCAT(result, RIGHT(subject, CHAR_LENGTH(subject) + 1 - startPos));
    ELSEIF startInc = -1 AND startPos >= 1 THEN
      SET result = CONCAT(LEFT(subject, startPos), result);
    END IF;
  ELSE
    SET result = subject;
  END IF;
  RETURN result;
END//
DELIMITER ;

演示

Rextester演示

限制

1. 当主题字符串较大时，这种方法肯定需要一段时间。 更新：现在已添加最小和最大匹配长度参数，以提高效率（零=未知/无限）。
2. 它不会允许替换反向引用（例如\1、\2等）来替换捕获组。如果需要此功能，请参见此答案，它尝试通过更新函数以允许在每个找到的匹配中进行第二次查找和替换来提供解决方法（代价是增加了复杂性）。
3. 如果在模式中使用了^和/或$，则它们必须分别位于开头和结尾-例如，不支持如(^start|end$)的模式。
4. 有一个“贪婪”标志，用于指定整体匹配是贪婪还是非贪婪。不支持在单个正则表达式中组合贪婪匹配和懒惰匹配（例如a.*?b.*）。

用法示例

该函数已用于回答以下StackOverflow问题：

• 如何在MySQL /正则表达式替换器中计数单词？
• 如何从MySQL字符串中提取第n个单词并计算单词出现次数？
• 如何从文本字段中提取两个连续的数字在MySQL中？
• 如何从MySQL字符串中删除所有非字母数字字符？
• 如何在MySQL字符串中替换每个其他实例的特定字符？
• 如何从MySQL表的多个列中获取所有指定最小长度的不同单词？

- Steve Chambers

---

与该函数的文档所建议的不同，至少在mariadb 5.5.60中，null不能用于maxMatchLength或minMatchLength参数的0位置。 - gvlasov

很好的发现 - 我现在已经更新了SQL语句以允许NULL。 - Steve Chambers

Answer 8

很高兴地报告，自从这个问题被提出以来，现在已经有了一个令人满意的答案！看一下这个绝妙的软件包：

https://github.com/mysqludf/lib_mysqludf_preg

SQL示例：

SELECT PREG_REPLACE('/(.*?)(fox)/' , 'dog' , 'the quick brown fox' ) AS demo;

我在这篇博客文章中找到了这个软件包的链接，该文章是作为这个问题的答案。

- dotancohen

Answer 9

你“可以”这样做……但这不是很明智……这就是我会尝试的最大冒险……就完全支持正则表达式而言，使用Perl或类似语言更好。

UPDATE db.tbl
SET column = 
CASE 
WHEN column REGEXP '[[:<:]]WORD_TO_REPLACE[[:>:]]' 
THEN REPLACE(column,'WORD_TO_REPLACE','REPLACEMENT')
END 
WHERE column REGEXP '[[:<:]]WORD_TO_REPLACE[[:>:]]'

- Edward J Beckett

---

不，那样行不通。假设你的列包含“asdfWORD_TO_REPLACE WORD_TO_REPLACE”。你的方法会导致“asdfREPLACEMENT REPLACEMENT”，而正确的答案应该是“asdfWORD_TO_REPLACE REPLACEMENT”。 - Ryan Shillington

@Ryan...这正是我为什么说这不是很明智的原因...在你提供的用例中，这肯定会失败。简而言之，使用“类似正则表达式”的结构是一个坏主意。更糟糕的是...如果你删除where子句，所有的值都将变成NULL... - Edward J Beckett

实际上，在这种情况下，Ryan你的说法是不正确的，因为标记只会找到零长度单词'boundaries'的匹配项，所以只有在单词前后有边界的单词才会匹配...尽管如此，这仍然是一个糟糕的想法... - Edward J Beckett

Answer 10

我认为有一个简单的方法可以实现这个，对我来说它很好用。 使用正则表达式选择行

SELECT * FROM `table_name` WHERE `column_name_to_find` REGEXP 'string-to-find'

使用正则表达式更新行

UPDATE `table_name` SET column_name_to_find=REGEXP_REPLACE(column_name_to_find, 'string-to-find', 'string-to-replace') WHERE column_name_to_find REGEXP 'string-to-find'

正则表达式参考： https://www.geeksforgeeks.org/mysql-regular-expressions-regexp/

（注：此内容为网站链接，无需翻译）

- Silambarasan R.D

---

谢谢 :) 自从版本8以后，这很容易实现。 - Piskvor left the building