Is there a way to find / replace a string across all tables in a mysql database?

I'm thinking this isn开发者_Go百科't possible without doing a dump, searching / replacing within the .sql file, and then reimporting it, but figured I'd ask anyway...

Basically, is there a way to search for "samplestring" within all of the fields, within all of the tables, within one database and replace it with "examplestring"?

I don't know of one. There especially isn't if you're willing/want to look at and modify column names and other non-data stuff.

If you don't have to do it very often, it's not that problematic.

mysqldump --username user --password pass database | sed 's/somestring/otherstring/g' | mysql -uroot -p

You can, but it requires using dynamic SQL (MySQL's Prepared Statements).

First, you need to get a list of the text based columns:

SELECT c.column_name, c.table_name
  FROM INFORMATION_SCHEMA.COLUMNS c
 WHERE c.table_schema = your_db_name
   AND c.data_type IN ('varchar') -- don't want to replace on an INT/etc

Then you need to iterate over that list to create the UPDATE statement(s)...

Here's a solution in PHP:

<?php

// edit this line to add old and new terms which you want to be replaced
$search_replace = array( 'old_term' => 'new_term', 'old_term2' => 'new_term2' );

//change the localhost,username,password and database-name according to your db
mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("database-name") or die(mysql_error());

$show_tables = mysql_query( 'SHOW TABLES' );
while( $st_rows = mysql_fetch_row( $show_tables ) ) {
    foreach( $st_rows as $cur_table ) {
        $show_columns = mysql_query( 'SHOW COLUMNS FROM ' . $cur_table );
        while( $cc_row = mysql_fetch_assoc( $show_columns ) ) {
            $column = $cc_row['Field'];
            $type = $cc_row['Type'];
            if( strpos( $type, 'char' ) !== false || strpos( $type, 'text' ) !== false ) {
                foreach( $search_replace as $old_string => $new_string ) {
                    $replace_query = 'UPDATE ' . $cur_table .
                        ' SET ' .  $column . ' = REPLACE(' . $column .
                        ', \'' . $old_string . '\', \'' . $new_string . '\')';
                    mysql_query( $replace_query );
                }
            }
        }
    }
}
echo 'replaced';
mysql_free_result( $show_columns );
mysql_free_result( $show_tables );
mysql_close( $mysql_link );

?>

Source