Replace all fields in 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

回答1:

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:

1. prepared statements for dynamic SQL execution;
2. 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;

回答2:

I made one minor change:

select concat(
   'UPDATE ', table_name, ' SET ',
   column_name,
   ' = REPLACE(', column_name, ', ''OLDTEXT'', ''NEWTEXT'');')
from information_schema.columns
where table_name = 'TABLENAME';

Which will use the variable for TABLENAME (just a bit less typing) - so you only need to replace the stuff in caps.

Also, I didn't understand at first, but this will only output a list of SQL Queries which you then have to execute to actually replace the code. Hope this helps...

回答3:

This will do the trick with some PHP since MySQL stuff often includes PHP. Tested and working :)

<?php

        $host = 'localhost';
        $user = 'root';
        $pass = 'yourpass';
        $db = 'your_database_name';

        $connection = mysql_connect($host, $user, $pass);
        mysql_select_db($db);

        $thisword = "this one should be";
        $shouldbe = "like this";
        $thistable = "your_table_name";

        MySQL_replace_all($thisword, $shouldbe, $thistable);

        function MySQL_replace_all($thisword,$shouldbe,$thistable){
            $cnamnes = "SHOW columns FROM " . $thistable;
            $result = mysql_query($cnamnes);
            while($columnname = mysql_fetch_row($result)){
                $replace_SQL = "UPDATE $thistable SET ". $columnname[0] ." = REPLACE(". $columnname[0] .",'". $thisword ."', '". $shouldbe ."');";
                echo $replace_SQL . "<br>";
                mysql_query($replace_SQL);
            }
    }

?>

回答4:

You can't do what you want. If it was me, i'd take a list of column names and in my editor do a quick regex search and replace.

Find: (.+)

Replace: UPDATE my_table SET \1 = REPLACE (\1,'a','e' );