MySQL search and replace across the whole database


When changing the domain of certain website or other similar operations at some point it comes to search and replace of certain term across a database. That is not in certain table or column, but all textual columns in all tables in a database.


A simple solution
 


Of course you can achieve this by just dumping the database to export file, open that file with text editor, do a global search and replace and import back the changed database. Unfortunately this method is not applicable on large databases where export files could reach several gigabytes. Then you'll have to do manual SQL queries using replace() MySQL function. If your database contains just several tables with a few columns you can write the queries yourself. But consider having database with hundreds of tables with tens of textual columns. You'll need automate solution for this.


Automatic generation of queries based on INFORMATION_SCHEMA
 


Here's another solutions that uses INFORMATION_SCHEMA to find out affected tables and columns. INFORMATION_SCHEMA is system database schema holding all meta information about tables and other database objects. What we need are the TABLES and COLUMNS pseudo tables. The former contains information for all tables while the latter holds the columns metadata. What we do here is to extract all tables from the desired database, then for each one figure out which are the TEXT and VARCHAR columns and based on this construct queries. And the code itself is:

<?php

function generate_queries($database, $user, $password, $host, $search, $replace) {

    $conn = mysql_connect($host, $user, $password);
    if (!$conn) {
        die('Unable to connect ' . mysql_error());
    }
   
    if (!mysql_select_db('INFORMATION_SCHEMA', $conn)) {
        die('Cannot use INFORMATION_SCHEMA');
    }
   
    $database_sql = mysql_real_escape_string($database, $conn);
    $query_tables = "select TABLE_NAME from TABLES where TABLE_SCHEMA = '$database_sql'";
    $tables_res = mysql_query($query_tables);
   
    $queries = '';
    $search_sql = mysql_real_escape_string($search, $conn);
    $replace_sql = mysql_real_escape_string($replace, $conn);
   
    while($tables_row = mysql_fetch_assoc($tables_res)) {
        $table_sql = mysql_real_escape_string($tables_row['TABLE_NAME'], $conn);
       
        $query_columns = "select COLUMN_NAME from COLUMNS where TABLE_SCHEMA = '$database_sql' and TABLE_NAME = '$table_sql' and DATA_TYPE in ('varchar', 'text')";
        $columns_res = mysql_query($query_columns);
        $columns = array();
        while ($column_row = mysql_fetch_assoc($columns_res)) {
            $columns[] = $column_row['COLUMN_NAME'];
        }
       
        if (!empty($columns)) {
            $queries .= "update `{$tables_row['TABLE_NAME']}` set ";
            foreach ($columns as $i => $column) {
                if ($i) {
                    $queries .= ", ";
                }
                $queries .= "`$column` = replace(`$column`, '$search_sql', '$replace_sql')";
            }
            $queries .= ";\n";
        }
    }
   
    return $queries;
}


This function returns a list of queries to be executed. Just do a DB backup, review the resulting queries and execute them.
 

 

No comments yet

Back to articles list

This page was last modified on 2018-12-09 08:06:38