mysql - 搜索表中的所有列

Question

我想要一个程序，该过程将搜索所有列中的非键盘 ascii 字符(12 月 16 日至 12 月 31 日或 DLE 至美国)，并通过用空格 ' ' 替换它们或什么都不替换来更新列''.

我有一个 SELECT 语句来查找需要更新的行，但我必须自己手动更改所有列。

SELECT column_name
FROM table_name
WHERE column_name REGEXP '[[.DLE.]-[.US.]]'

这是用于修改列值的UPDATE脚本

UPDATE table
SET
column = replace(column,char(16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31), '')

我希望将这两者融合到一个函数或存储过程中，但我不知道如何实现，因为我刚刚开始学习 MySQL。

Answer 1

免责声明

在使用 REGEXP 和 CURSOR 循环遍历每个表和列之间，这些示例不会快如闪电。速度显然会根据您的环境而有所不同，我建议在生产之前对它们进行开发测试

一个表格中的一列

要搜索单个表上的单个列，您基本上可以根据需要进行UPDATE。

UPDATE t1
SET
column_name = replace(column_name, 
  char(16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31), '')
WHERE column_name REGEXP '[[.DLE.]-[.US.]]'

一张表中的所有列

要执行表中的所有列，您需要识别该表，然后循环遍历列 using a cursor

DELIMITER $$
CREATE PROCEDURE table_regexp_replace(in_table VARCHAR(128))
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE search_column VARCHAR(64);

  DECLARE cur1 CURSOR FOR
    SELECT DISTINCT `COLUMN_NAME` FROM `information_schema`.`COLUMNS`
      WHERE `TABLE_NAME` = in_table ORDER BY `ORDINAL_POSITION` ;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;

  read_loop: LOOP
    -- Process the next column
    FETCH cur1 INTO search_column;

    -- If we're done, stop the loop
    IF done THEN
      LEAVE read_loop;
    END IF;

    -- Replace everything in this column matching the regexp
    SET @new_query := CONCAT ('UPDATE ', in_table, 
        ' SET `', search_column, '` = replace(', search_column, 
          ', char(16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31), \'\')
        WHERE ', search_column, ' REGEXP \'[[.DLE.]-[.US.]]\'') ;

    PREPARE stmt FROM @new_query;
    EXECUTE stmt ;
  END LOOP;

  CLOSE cur1;
END$$

DELIMITER ;

然后使用

CALL table_regexp_replace('my_table');

工作原理

看起来很复杂，实际上很简单。

1. 我们创建一个带有一个参数 in_table 的过程，该参数用于指定要使用的表。
2. 设置一个游标，以正确的顺序从 information_schema 表中提取列名称
3. 循环遍历每一列，对每一列执行手动创建的 UPDATE 语句。

您会注意到 UPDATE 查询中任何需要引号的地方，都必须使用 \ 进行转义。

\'[[.DLE.]-[.US.]]\'

所有表格中的所有列

然后，您可以使用与上面类似的方法在所有表的循环中使用此过程。以下是从 information_schema 中提取所有表名称的方法:

从information_schema中选择不同的TABLE_NAME。TABLES WHERE TABLE_SCHEMA = 'your_database_name';