mysql - 基于 REGEX 的替换在 MySQL 过程中不起作用

Question

我有一组关键字，我想在表格的列中更改它们出现的位置。因此，有一个主表和关键字表，其中有 2 列:要查找的关键字以及将其更改为的内容。例如，有一行用于

findtxt repltxt 有限公司$ 有限公司

然后我有一个过程，其中重要部分引用如下:

DECLARE cur1 CURSOR FOR SELECT findtxt,repltxt FROM keywords ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
SET done = 0;
OPEN cur1;

myLoop: LOOP
    FETCH cur1 INTO ftxt,rtxt;      
    IF done = 1 THEN 
        LEAVE igmLoop; 
    END IF;
    SET @t1 = CONCAT('UPDATE ',tbl,' SET ',sanecol,' = REPLACE(',sanecol,',?,','?) WHERE ',sanecol,' REGEXP ?'); 

    PREPARE stmt FROM @t1;

    SET @ftxt = ftxt;
    SET @rtxt = rtxt;
    EXECUTE stmt USING @ftxt,@rtxt,@ftxt; 
    DEALLOCATE PREPARE stmt;
END LOOP myLoop;
CLOSE cur1;

sanecol 和 tbl 作为参数传递到代表要更改的列和具有该列的表的过程中。

此过程循环遍历所有记录(我知道，因为这需要一段时间)，但最终没有错误。但关键字并未更改(例如 LTD 不会变为 LIMITED。)

我做错了什么？

我已经测试过通过

来隔离问题

SET @t2 = CONCAT('UPDATE ',tbl,' SET idd = 1 WHERE ',sanecol,' REGEXP ?'); 

其中 idd 只是创建的一个测试标志，以确保 REGEXP 正常工作。它确实是通过将 LIM 或 LTD 的每个记录的 idd 列设置为 1。

所以问题确实出在这部分代码上:

CONCAT('UPDATE ',tbl,' SET ',sanecol,' = REPLACE(',sanecol,',?,','?)

REPLACE 不起作用。

要替换的表中的示例数据

id        replacecol
1         FOREVER UNITED LTD
2         APPLE DEVICES LIMITED
3         QUICKFIX DESIGNS LIM
4         FINANCIAL TIMES LTD

这应该更改为:

id        replacecol
1         FOREVER UNITED LIMITED
2         APPLE DEVICES LIMITED
3         QUICKFIX DESIGNS LIMITED
4         FINANCIAL TIMES LIMITED

Answer 1

尝试如下:

mysql> DROP PROCEDURE IF EXISTS `sp_test`;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS `keywords`, `to_replace`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `to_replace` (
    ->  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->  `text` VARCHAR(255) NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `keywords` (
    ->  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->  `regexptxt` VARCHAR(255) NOT NULL,
    ->  `findtxt` VARCHAR(255) NOT NULL,
    ->  `repltxt` VARCHAR(255) NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `to_replace` (`text`)
    ->  VALUES ('LTD$ AAAA'),
    ->         ('BBBB LIM$'),
    ->         ('AAAA LTD$ LIM$ BBBB'),
    ->         ('FOREVER UNITED LTD'),
    ->         ('APPLE DEVICES LIMITED'),
    ->         ('QUICKFIX DESIGNS LIM'),
    ->         ('FINANCIAL TIMES LTD'),
    ->         ('BEAUTI SLIM'),
    ->         ('FINANCIAL TIMES LTD & FOREVER UNITED LTD'),
    ->         ('FOREVER UNITED LTD & QUICKFIX DESIGNS LIM');
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `keywords`
    ->    (`regexptxt`, `findtxt`, repltxt)
    ->  VALUES
    ->    (' LTD$', 'LTD', 'LIMITED'),
    ->    (' LIM$', 'LIM', 'LIMITED');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> DELIMITER //

mysql> CREATE PROCEDURE `sp_test`(`tbl` VARCHAR(64), `sanecol` VARCHAR(64))
    ->  BEGIN
    ->    DECLARE `done` BOOL DEFAULT FALSE;
    ->    DECLARE `retxt`, `ftxt`, `rtxt` VARCHAR(255) DEFAULT '';
    ->  
    ->    DECLARE `cur1` CURSOR FOR
    ->    SELECT `regexptxt`, `findtxt`, `repltxt`
    ->    FROM `keywords`;
    ->  
    ->    DECLARE CONTINUE HANDLER FOR NOT FOUND SET `done` := TRUE;
    ->  
    ->    OPEN `cur1`;
    ->  
    ->    `myLoop`: LOOP
    ->      FETCH `cur1` INTO `retxt`, `ftxt`, `rtxt`;
    ->      IF `done` THEN
    ->        CLOSE `cur1`;
    ->        LEAVE `myLoop`;
    ->      END IF;
    ->  
    ->      SET @`t1` = CONCAT('UPDATE `', `tbl`,'`
    '>                          SET `', `sanecol`, '` = REPLACE(`', `sanecol`, '`, ?, ?)
    '>                          WHERE `', `sanecol`, '` REGEXP ?');
    ->      PREPARE `stmt` FROM @`t1`;
    ->      SET @`ftxt` = `ftxt`,
    ->          @`rtxt` = `rtxt`,
    ->          @`retxt` = `retxt`;
    ->      EXECUTE `stmt` USING @`ftxt`, @`rtxt`, @`retxt`;
    ->      DEALLOCATE PREPARE `stmt`;
    ->    END LOOP `myLoop`;
    ->  END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> SELECT `id`, `text`
    ->  FROM `to_replace`;
+----+-------------------------------------------+
| id | text                                      |
+----+-------------------------------------------+
|  1 | LTD$ AAAA                                 |
|  2 | BBBB LIM$                                 |
|  3 | AAAA LTD$ LIM$ BBBB                       |
|  4 | FOREVER UNITED LTD                        |
|  5 | APPLE DEVICES LIMITED                     |
|  6 | QUICKFIX DESIGNS LIM                      |
|  7 | FINANCIAL TIMES LTD                       |
|  8 | BEAUTI SLIM                               |
|  9 | FINANCIAL TIMES LTD & FOREVER UNITED LTD  |
| 10 | FOREVER UNITED LTD & QUICKFIX DESIGNS LIM |
+----+-------------------------------------------+
10 rows in set (0.00 sec)

mysql> CALL `sp_test`('to_replace', 'text');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT `id`, `text`
    ->  FROM `to_replace`;
+----+--------------------------------------------------+
| id | text                                             |
+----+--------------------------------------------------+
|  1 | LTD$ AAAA                                        |
|  2 | BBBB LIM$                                        |
|  3 | AAAA LTD$ LIM$ BBBB                              |
|  4 | FOREVER UNITED LIMITED                           |
|  5 | APPLE DEVICES LIMITED                            |
|  6 | QUICKFIX DESIGNS LIMITED                         |
|  7 | FINANCIAL TIMES LIMITED                          |
|  8 | BEAUTI SLIM                                      |
|  9 | FINANCIAL TIMES LIMITED & FOREVER UNITED LIMITED |
| 10 | FOREVER UNITED LTD & QUICKFIX DESIGNS LIMITED    |
+----+--------------------------------------------------+
10 rows in set (0.00 sec)