如何在 MySQL 中进行正则表达式替换? [英] How to do a regular expression replace in MySQL?

问题描述

我有一个大约有 50 万行的表格；varchar(255) UTF8 列 filename 包含文件名；

I have a table with ~500k rows; varchar(255) UTF8 column filename contains a file name;

我试图从文件名中去除各种奇怪的字符 - 我想我会使用一个字符类:[^a-zA-Z0-9()_ .\-]

I'm trying to strip out various strange characters out of the filename - thought I'd use a character class: [^a-zA-Z0-9()_ .\-]

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

Now, is there a function in MySQL that lets you replace through a regular expression? I'm looking for a similar functionality to REPLACE() function - simplified example follows:

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

Output: "stackoverflow"

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

Output: "-tackover-low"

我知道REGEXP/RLIKE，但那些只检查if是否匹配，而不是什么匹配.

I know about REGEXP/RLIKE, but those only check if there is a match, not what the match is.

(我可以做一个SELECT pkey_id,filename FROM foo WHERE filename RLIKE '[^a-zA-Z0-9()_ .\-]'" 从 PHP 脚本中，执行 preg_replace 然后"UPDATE foo ... WHERE pkey_id=..."，但这看起来像是最后的缓慢&丑陋的黑客)

(I could do a "SELECT pkey_id,filename FROM foo WHERE filename RLIKE '[^a-zA-Z0-9()_ .\-]'" from a PHP script, do a preg_replace and then "UPDATE foo ... WHERE pkey_id=...", but that looks like a last-resort slow & ugly hack)

推荐答案

使用 MySQL 8.0+ 你可以原生使用 REGEXP_REPLACE 函数.

With MySQL 8.0+ you could use natively REGEXP_REPLACE function.

12.5.2 正则表达式:

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

用替换字符串repl替换字符串expr中匹配模式pat指定的正则表达式的出现，并返回结果细绳.如果expr、pat或repl为NULL，则返回值为NULL.

Replaces occurrences in the string expr that match the regular expression specified by the pattern pat with the replacement string repl, and returns the resulting string. If expr, pat, or repl is NULL, the return value is NULL.

和正则表达式支持:

以前，MySQL 使用 Henry Spencer 正则表达式库来支持正则表达式运算符(REGEXP、RLIKE).

Previously, MySQL used the Henry Spencer regular expression library to support regular expression operators (REGEXP, RLIKE).

正则表达式支持已使用 Unicode 国际组件 (ICU) 重新实现，该组件提供完整的 Unicode 支持并且是多字节安全的.REGEXP_LIKE() 函数以 REGEXP 和 RLIKE 运算符的方式执行正则表达式匹配，它们现在是该函数的同义词.此外， REGEXP_INSTR()、 REGEXP_REPLACE() 和 REGEXP_SUBSTR() 函数可用于查找匹配位置并分别执行子字符串替换和提取.

Regular expression support has been reimplemented using International Components for Unicode (ICU), which provides full Unicode support and is multibyte safe. The REGEXP_LIKE() function performs regular expression matching in the manner of the REGEXP and RLIKE operators, which now are synonyms for that function. In addition, the REGEXP_INSTR(), REGEXP_REPLACE(), and REGEXP_SUBSTR() functions are available to find match positions and perform substring substitution and extraction, respectively.

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

DBFiddle 演示

这篇关于如何在 MySQL 中进行正则表达式替换?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！