Wednesday, March 11, 2009

Rename multiple tables in MySQL database

Having to rename multiple tables with a similar pattern (prefix, suffix, ...) by replacing the recurring pattern, isn't an easy task, because there is no simple function or statement for doing it, not as far as I know.
Renaming tables like 'table_1234' and 'table_3456' to 'new_table_1234' and 'new_table_3456', is pretty straightforward in SQL:


RENAME TABLE `database`.`table_1234`
TO `database`.`new_table_1234`;
RENAME TABLE `database`.`table_3456`
TO `database`.`new_table_3456`;

or in one SQL statement :

RENAME TABLE
`database`.`table_1234` TO `database`.`new_table_1234`,
`database`.`table_3456` TO `database`.`new_table_3456`;

But when renaming a lot of tables, this method becomes tedious and time consuming.

This PHP script automates the renaming of multiple tables in a MySQL database. It lists all tables in a MySQL database, which contain a defined string pattern. The script creates and executes a series of SQL statements, which rename the table by replacing the search pattern in the original table name with another pattern in the new table name.

This script can easily be modified to rename multiple databases, or when stripping the original pattern (f.e. a prefix) and adding a new pattern (f.e. a suffix) is needed.

<?php
$db_server = "localhost"; // hostname MySQL server
$db_username = "username"; // username MySQL server
$db_password = "password"; // password MySQL server
$db_name = "database"; // database name

$pattern = "pattern_"; // search string
$new_pattern = "new_pattern_"; // replacement string,
// can be empty

// login to MySQL server
$link = mysql_connect( $db_server, $db_username, $db_password);

if (!$link)
{
die('Could not connect: ' . mysql_error());
}

// list all tables in the database containing the search pattern
$sql = "SHOW TABLES FROM `" . $db_name . "`";
$sql .= " LIKE '%" . $pattern . "%'";

$result = mysql_query ( $sql, $link );
if (!$result)
{
die("Invalid query: " . mysql_error( $link ));
}

$renamed = 0;
$failed = 0;

while ( $row = mysql_fetch_array ($result) )
{
// rename every table by replacing the search pattern
// with a new pattern
$table_name = $row[0];
$new_table_name = str_replace ( $pattern, $new_pattern, $table_name);

$sql = "RENAME TABLE `" . $db_name . "`.`" . $table_name . "`";
$sql .= " TO `" . $db_name . "`.`" . $new_table_name . "`";

$result_rename = mysql_query ( $sql, $link );
if ($result_rename)
{
echo "Table `" . $table_name . "` renamed to :`";
echo $new_table_name . "`.\n";
$renamed++;
}
else
{
// notify when the renaming failed and show reason why
echo "Renaming of table `" . $table_name . "` has failed: ";
echo mysql_error( $link ) . "\n";
$failed++;
}
}

echo $renamed . " tables were renamed, " . $failed . " failed.\n";

// close connection to MySQL server
mysql_close( $link );
?>

Syntax Highlighting in Blogger

I've added some Javascript to the template of my blog to get Syntax Highlighting working. This means source code is formatted so it is easier to read. If it works allright, you should see an example of some highlighted php-code below. (The source code is not formatted/coloured when reading this post through an RSS-feed.)


<?php
/* Sample php code
This code doesn't do anything at all.
It just contains some php elements that should be formatted
and coloured.
*/

$some_variable = "some_string";

// Some comment
$result = some_function( $some_variable );

if (!$result)
{
die("It doesn't work!");
}

$some_array = new array();

$some_array[0] = $result;
?>