替换MySQL中的所有字段 [英] Replace all fields in 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' );

因此执行该命令将使用 e 字符来更改my_table表的my_column列中所有出现的 a .

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;

这篇关于替换MySQL中的所有字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！