mysql 替换所有字段,替换MySQL中的所有字段

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' );

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.

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

Thanks

解决方案

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';

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

Untested after some googling

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.

The main idea is to use:

prepared statements for dynamic SQL execution;

cursors to iterate over all columns of a table.

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;