logo标签列表

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

mysqlregexmysql-udf
606

我有一张包含约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=..."，但看起来这似乎是一个最后的手段，并且效率低下且难看)

9
这是自2007年以来的一个功能请求：http://bugs.mysql.com/bug.php?id=27389。如果你真的想要这个功能，请登录并点击“影响我”按钮，希望它能得到足够的投票支持。 - Tomas
6
@Tomas：我在2009年寻找时做过这件事情......由于目前没有任何进展 - 显然这不是一个很重要的功能。（顺便说一句，Postgres有它：https://dev59.com/V2gt5IYBdhLWcg3w7BkE） - Piskvor left the building
2
与此问题相关的简化版本：https://dev59.com/D2w05IYBdhLWcg3w6GLH - Kzqai
2
我已经创建了“regexp_split”（函数+过程）和“regexp_replace”，这些都是使用“REGEXP”运算符实现的。对于简单的查找，它很管用。你可以在这里找到它：https://github.com/almadomundo/mysql-regexp，这就是使用MySQL存储代码的方式，而不是UDF。如果您发现一些未被已知限制所覆盖的错误，请随时开启问题。 - Alma Do
在我看来，选择使用PHP再更新MySQL并不是一种糟糕的“hack”方式。当然，如果可能的话，我更喜欢使用MySQL本地方法——据说这样会更快速。但是我的系统大量使用了PHP，在几秒钟内就能完成数十万次这样的替换操作（我构建的系统正是专门为此类工作设计的）。 - Buttle Butkus
1
在另一个 SO 线程中发现了这个库：https://github.com/mysqludf/lib_mysqludf_preg，它完美地工作。 - Kyle
13个回答
6
我们可以在SELECT查询中使用IF条件，如下所示：
假设对于任何包含“ABC”、“ABC1”、“ABC2”、“ABC3”等内容的项目，我们都想用“ABC”替换，那么使用REGEXP和IF()条件，在SELECT查询中就可以实现这一点。
语法：
SELECT IF(column_name REGEXP 'ABC[0-9]$','ABC',column_name)
FROM table1 
WHERE column_name LIKE 'ABC%';

例子：

SELECT IF('ABC1' REGEXP 'ABC[0-9]$','ABC','ABC1');
你好，感谢您的建议。我一直在尝试类似的东西，但是在我的数据集上表现不佳。对于较小的数据集，这可能是可行的。 - Piskvor left the building
4
下面的代码基本上是从左边找到第一个匹配项，然后替换所有出现的匹配项（在 中测试过）。 用法： 
SELECT REGEX_REPLACE('dis ambiguity', 'dis[[:space:]]*ambiguity', 'disambiguity');

实现：

DELIMITER $$
CREATE FUNCTION REGEX_REPLACE(
  var_original VARCHAR(1000),
  var_pattern VARCHAR(1000),
  var_replacement VARCHAR(1000)
  ) RETURNS
    VARCHAR(1000)
  COMMENT 'Based on https://techras.wordpress.com/2011/06/02/regex-replace-for-mysql/'
BEGIN
  DECLARE var_replaced VARCHAR(1000) DEFAULT var_original;
  DECLARE var_leftmost_match VARCHAR(1000) DEFAULT
    REGEX_CAPTURE_LEFTMOST(var_original, var_pattern);
    WHILE var_leftmost_match IS NOT NULL DO
      IF var_replacement <> var_leftmost_match THEN
        SET var_replaced = REPLACE(var_replaced, var_leftmost_match, var_replacement);
        SET var_leftmost_match = REGEX_CAPTURE_LEFTMOST(var_replaced, var_pattern);
        ELSE
          SET var_leftmost_match = NULL;
        END IF;
      END WHILE;
  RETURN var_replaced;
END $$
DELIMITER ;

DELIMITER $$
CREATE FUNCTION REGEX_CAPTURE_LEFTMOST(
  var_original VARCHAR(1000),
  var_pattern VARCHAR(1000)
  ) RETURNS
    VARCHAR(1000)
  COMMENT '
  Captures the leftmost substring that matches the [var_pattern]
  IN [var_original], OR NULL if no match.
  '
BEGIN
  DECLARE var_temp_l VARCHAR(1000);
  DECLARE var_temp_r VARCHAR(1000);
  DECLARE var_left_trim_index INT;
  DECLARE var_right_trim_index INT;
  SET var_left_trim_index = 1;
  SET var_right_trim_index = 1;
  SET var_temp_l = '';
  SET var_temp_r = '';
  WHILE (CHAR_LENGTH(var_original) >= var_left_trim_index) DO
    SET var_temp_l = LEFT(var_original, var_left_trim_index);
    IF var_temp_l REGEXP var_pattern THEN
      WHILE (CHAR_LENGTH(var_temp_l) >= var_right_trim_index) DO
        SET var_temp_r = RIGHT(var_temp_l, var_right_trim_index);
        IF var_temp_r REGEXP var_pattern THEN
          RETURN var_temp_r;
          END IF;
        SET var_right_trim_index = var_right_trim_index + 1;
        END WHILE;
      END IF;
    SET var_left_trim_index = var_left_trim_index + 1;
    END WHILE;
  RETURN NULL;
END $$
DELIMITER ;
0

可以的。

UPDATE table_name 
  SET column_name = 'seach_str_name'
  WHERE column_name REGEXP '[^a-zA-Z0-9()_ .\-]';
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接