Recently I migrated a drupal site that uses an ms SQL server to MySQL.
One of the conversions required me to convert all the tables from latin1_swedish_ci to utf8_general_ci character set.
If you only want to convert a few tables, just execute the following SQL statement.
ALTER TABLE tableName CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
We all know a typical drupal site can have100+ tables.
In my case, the database I needed to convert had 350+ tables, so doing it manually was simply not an option.
So I am sharing a simple script I used to do all the conversions.
// Change the following setting as needed.
$host = "localhost";
$db_name = 'dbname';
$db_username = "root";
$db_password = "root";
$convert_to = "utf8_general_ci";
try {
$conn = new PDO("mysql:host=$host;dbname=$db_name", $db_username, $db_password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SHOW TABLES";
print "Following SQL Statements have been executed.
";
foreach ($conn->query($sql) as $row) {
$table_name = $row['Tables_in_' . $db_name];
$sql = 'ALTER TABLE ' . $table_name . ' CONVERT TO CHARACTER SET utf8 COLLATE ' . $convert_to;
$output = $sql . '
';
print $output;
$conn->query($sql);
}
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}
Good luck and I hope this helps! Download the script using the link below.
Download Code
Need help with a Drupal project? The Drupal Development team at Cheeky Monkey does a lot of backend work with other agencies. If you’ve taken on a project that can use some extra hands, give us a call!
