WebScHoLaR Dot NET

Collation refers to the character set used to store data in text fields and is necessary to provide support for all of the many written languages of the world. Collation can be changed for the database, tables or even columns.

You can change the collation specified for a database by using the ALTER DATABASE statement, for example:

ALTER DATABASE database_name COLLATE collation_name;

You can change the collation specified for a TABLE by using the ALTER TABLE statement, for example:

ALTER TABLE table_name [CONVERT TO CHARACTER SET character_set_name] [COLLATE collation_name];

You can change the collation specified for a column by using the ALTER TABLE statement, for example:

ALTER TABLE table_name ALTER COLUMN column_name [COLLATE collation_name];

Below is a simple php script that can be used to change the collation in MySQL:

<?php
header(‘Content-type: text/plain;’);
mysql_connect(‘localhost’,'username’,'password’);
mysql_select_db(‘databasename’);
$res = mysql_query(“SHOW TABLES”);
while ($r = mysql_fetch_array($res)){
$tablename = $r[0];
echo $sql = “ALTER TABLE $tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci”;
echo “\r\n”;
mysql_query($sql);
}
?>

PhpMyAdmin can also be used to change the collation for the tables, columns.