Aug 25 2008

[PHP, MySQL] Batch changing charset and collation on databases

Category: Articles,MySQL,PHPFractalizeR @ 11:51 pm

Recently I needed to quickly change charset on all tables and change each field’s chanset in each table in a specific database to latin1 and collation to latin1_swedish_ci. I googled a little and found this solution by shimon doodkin. I used it and it did work, but also it attempted to change charset at MySQL information_schema system database which is something I didn’t want to. It also proceessed all databases at MySQL server. Luckily, there was only two :) It was a test server, that’s why I was uncareful.

Ok. I wrote a bit more elegant solution I would like to share with you today. It is pretty self-explanatory and is based on ALTER TABLE CONVERT TO charset MySQL statement.

<?php
// Script written by Vladislav "FractalizeR" Rastrusny
// http://www.fractalizer.ru
 
//MySQL connection settings
$db_server = 'localhost';
$db_user="root";
$db_password="";
 
mysql_connect($db_server, $db_user, $db_password) or die(mysql_error());
 
//Put here a list of databases you need to change charset at or leave array empty to change all existing
$dblist=array();
 
//If changing at all databases, which databases to skip? information_schema is mysql system databse and no need to change charset on it.
$skip_db_list = array('information_schema', 'mysql');
 
//Which charset to convert to?
$charset="utf8";
 
//Which collation to convert to?
$collation="utf8_general_ci";
 
//Only print queries without execution?
$printonly=true;
 
//Getting database names if they are not specified
$skip_db_text = '"'.implode('", "', $skip_db_list).'"';
if(count($dblist)<1) {
	$sql="SELECT GROUP_CONCAT(`SCHEMA_NAME` SEPARATOR ',') AS FRST FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME` NOT IN ($skip_db_text)";
	$result = mysql_query($sql) or die(mysql_error());
	$data = mysql_fetch_assoc ($result);
	$dblist=explode(",", $data["FRST"]);
}
 
//Iterating databases
foreach ($dblist as $dbname) {
	$sql="SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '` CONVERT TO CHARACTER SET $charset COLLATE $collation;') as FRST FROM `information_schema`.`TABLES` t WHERE t.`TABLE_SCHEMA` = '$dbname' ORDER BY 1";
 
	$result = mysql_query($sql) or die(mysql_error());
	while ($row = mysql_fetch_assoc($result)) {
		echo $row["FRST"]."\r\n";
		if(!$printonly) {
			mysql_query($row["FRST"]) or die(mysql_error());
		}
	}
}
?>

That is all. It is not long and easy-to-use

Tags: , , , , ,

Leave a Reply

You must be logged in to post a comment. Login now.