Have Geek Brain

MySQL Replace characters in every table and column of the database

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

Thanks in advance:)

Edit: This is because I found Gibbershish in some places and it will take me a few days to fix it one at a time.

0


a source to share


3 answers


You can't do without programming.
The simplest approach is to use the show / describe commands in your favorite language and run on the result set (column / table names) and create a list of UPDATE queries from it to work on your database.



Why don't you write it and open source, you are not the first one looking for something like this (or maybe it could be a script somewhere).

+1


a source


Found this answer from another thread:



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

+1


a source


I was looking for this myself when we changed the domain on our wordpress site. It cannot be done without some kind of 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 this helps anyone who stumbles upon this issue in the future :)

0


a source






More articles:


All Articles