替换 MySQL 中的所有字段

2021-12-25 00:00:00 sql-update sql replace mysql

我需要使用 REPLACE 命令替换表列中的一些字符.
我知道 REPLACE 命令需要一个列名，然后是要更改的文本(在以下示例中为a"字符)和新文本(在以下情况下为e"字符).

I need to replace some chars in the columns of a table, by using the REPLACE command.
I know that the REPLACE command needs a column name, then the text to change (in the following example, the 'a' char) and the new text (in the following case, the 'e' char).

UPDATE my_table SET my_column = REPLACE (my_column,'a','e' );

因此，执行此命令将更改 my_table 表的 my_column 列中出现的所有a"，并带有>e' 字符.

So that executing this command will change all the 'a' occurrences in the my_column column of the my_table table with the 'e' char.

但是如果我需要为每一列而不是仅仅为一列执行 REPLACE 命令怎么办?这可能吗?

But what if i need to execute the REPLACE command for every column and not just for one? Is this possible?

谢谢

推荐答案

使用以下 SQL 查询生成需要替换所有列中的值的 SQL 查询.

Use the following SQL query to generate the SQL queries that you need to replace a value in all columns.

select concat(
       'UPDATE my_table SET ',
       column_name,
       ' = REPLACE(', column_name, ', ''a'', ''e'');')
from information_schema.columns
where table_name = 'my_table';

执行此 SQL 查询后，只需运行所有查询即可替换所有值.

After executing this SQL query simply run all queries to replace all values.

经过一些谷歌搜索后未经测试

用这样的核心创建一个存储过程.它可以接受表名、要查找的值和要替换的值.

Create a stored procedure with a core like this. It can accept the name of the table, the value to find and the value to replace for.

主要思想是使用:

  1. 为动态 SQL 执行准备的语句；
  2. 用于遍历表的所有列的游标.

请参阅下面的部分代码(未经测试).

See partial code (untested) below.

DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR
    SELECT column_name FROM information_schema.columns
    WHERE table_name = 'my_table';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur1;
REPEAT
    SET s = concat(
       'UPDATE my_table SET ',
       column_name,
       ' = REPLACE(', column_name, ', ''a'', ''e'');');
    PREPARE stmt2 FROM s;
    EXECUTE stmt2;
    FETCH cur1 INTO a;
UNTIL done END REPEAT;
CLOSE cur1;

相关文章