CODEWITHSUNDEEP
mysqlstringreplace
Harry
Harry

Reputation:

MySQL Replace characters in every table and column of a database

I'd like to replace a string in every column and every table of a specific database. Is there a way to do this ?

Thanks in advance :)

Edit: That is because I have spotted Gibberish at some locations and it will take me days to fix it one by one

Upvotes: 0

Views: 1612

Answers (3)

jimmy.zoger
jimmy.zoger

Reputation: 89

I was looking for this myself when we changed domain on our Wordpress website. It can't be done without some programming so this is what I did.

<?php  
  header("Content-Type: text/plain");

  $host = "localhost";
  $username = "root";
  $password = "";
  $database = "mydatabase";
  $string_to_replace  = 'old.example.com';
  $new_string = 'new.example.com';

  // Connect to database server
  mysql_connect($host, $username, $password);

  // Select database
  mysql_select_db($database);

  // List all tables in database
  $sql = "SHOW TABLES FROM ".$database;
  $tables_result = mysql_query($sql);

  if (!$tables_result) {
    echo "Database error, could not list tables\nMySQL error: " . mysql_error();
    exit;
  }

  echo "In these fields '$string_to_replace' have been replaced with '$new_string'\n\n";
  while ($table = mysql_fetch_row($tables_result)) {
    echo "Table: {$table[0]}\n";
    $fields_result = mysql_query("SHOW COLUMNS FROM ".$table[0]);
    if (!$fields_result) {
      echo 'Could not run query: ' . mysql_error();
      exit;
    }
    if (mysql_num_rows($fields_result) > 0) {
      while ($field = mysql_fetch_assoc($fields_result)) {
        if (stripos($field['Type'], "VARCHAR") !== false || stripos($field['Type'], "TEXT") !== false) {
          echo "  ".$field['Field']."\n";
          $sql = "UPDATE ".$table[0]." SET ".$field['Field']." = replace(".$field['Field'].", '$string_to_replace', '$new_string')";
          mysql_query($sql);
        }
      }
      echo "\n";
    }
  }

  mysql_free_result($tables_result);  
?>

Hope it helps anyone who's stumbling into this problem in the future :)

Upvotes: 0

Russell
Russell

Reputation: 13

Found this answer from another thread:

http://www.anovasolutions.com/content/mysql-search-and-replace-stored-procedure

Upvotes: 1

Itay Moav -Malimovka
Itay Moav -Malimovka

Reputation: 53606

Not without doing some programming.
The most simple approach would be to use show/describe commands in your favorite language and run on the result set (names of columns/tables) and create from that a list of UPDATE queries to run on your DB.

Why don't you write it and Open Source it, you are not the first to look for something like that (Or google it, there might be a script ready somewhere).

Upvotes: 1

Related Questions