Mysql – How to change collation for all tables and columns in MySQL database

collationMySQLstored-procedures

I have a problem with MySQL collation. It turns out that MySQL uses latin1_swedish_ci by default. Collation setup step has been missed on MySQL database instantiation for some reason. Thus, application setup wizard has created tables in MySQL database without regard to the required collation. As a result, all tables and columns have latin1_swedish_ci collation. Changing database collation with ALTER DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci does not help as long as it does not affect tables and columns.

I have many tables and columns in my database, it will take a lot of time to traverse whole database and set collation manually. It will be also difficult to write long SQL altering every table and column as long as there are too many of them.

The question is where there any way to change collation for tables and columns in the whole MySQL database? If there is no dedicated mysql command for this purpose, maybe there are some examples of stored procedures available?

PS: I cannot use scripting languages as long as I do not have access to the serverside. MySQL is installed in restricted network area. All I can do is use phpmyadmin in order to run SQL queries.

Best Answer

In order to change the collation for all of the tables in certain database you should create a custom script and execute it on the server.

Here is a sample PHP script:

<?php

$database_name='dbname';
$database_username='dbusername';
$database_password='dbpassword';

$connection = mysql_connect('localhost',$database_username,$database_password);

if(!$connection) {
    echo "Cannot connect to the database – incorrect details";
} else{
    mysql_select_db($database_name);
    $result=mysql_query('show tables');

    while($tables = mysql_fetch_array($result)) {

        foreach ($tables as $key => $value) {
            mysql_query("ALTER TABLE ".$value." CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
        }
    }
echo "Successfull collation change!"; 
}
?>

Related Solutions

Mysql – Setting default charset/collation for MySQL database

Yes, you can set the default character set and collation on various levels.

First, in your question, on the database level:

CREATE DATABASE IF NOT EXISTS foo 
 DEFAULT CHARACTER SET = 'utf8' DEFAULT COLLATE 'utf8_general_ci'

ALTER DATABASE foo
 DEFAULT CHARACTER SET = 'utf8' DEFAULT COLLATE 'utf8_general_ci'

All new tables in that database should be created with the new character set and database if it's not specified in the table creation command (if it is specified in table command, the specification will override the database default).

You can set the default at a server level (so that all tables are created with the default for all databases unless explicitly stated otherwise) by providing the --character-set-server and --collation-server options in your my.cnf and restarting the server.

And finally, verify the client connection settings. This doc explains how the client connection can fine-tune (and override) the default settings.

Mysql – Changing collation type database-wide in MySQL

I found an answer on this website: http://kb.siteground.com/article/How_to_change_the_collation_for_all_tables_in_db_to_UTF8.html

I cleaned up the code to print out better from a linux shell and added the $new_charset and $new_collation variables to the alter database statement.

vi change_collation_everything.php

<?php

// Configuration Section
$server = 'localhost'; //probably localhost but change if required
$username = 'root';
$password = 'password';
$database = 'database';
$new_charset = 'latin1'; 
$new_collation = 'latin1_general_cs';

// Connect to database
$db = mysql_connect($server, $username, $password); if(!$db) die("Cannot connect to database server -".mysql_error());
$select_db = mysql_select_db($database); if (!$select_db) die("could not select $database: ".mysql_error());

// change database collation
mysql_query("ALTER DATABASE $database DEFAULT CHARACTER SET $new_charset COLLATE $new_collation");

// Loop through all tables changing collation
$result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {
    $table = $tables[0];
    mysql_query("ALTER TABLE $table DEFAULT CHARACTER SET $new_charset COLLATE $new_collation");

    // loop through each column changing collation
    $columns = mysql_query("SHOW FULL COLUMNS FROM $table where collation is not null");
    while($cols = mysql_fetch_array($columns)) {
        $column = $cols[0];
        $type = $cols[1];
        mysql_query("ALTER TABLE $table MODIFY $column $type CHARACTER SET $new_charset COLLATE $new_collation");
        print "changed $table collation of $column to $new_collation\n";
    }

    print "changed collation of $table to $new_collation\n";
}
print 'The collation of your database has been successfully changed!\n';
?>

I tested the answer on my own database changing from charset latin7 to latin1 and latin7_general_cs to latin1_general_cs for the collation.

I chose _cs instead of _ci for the collation because if you choose _ci which is the default collation for charset latin_1, the collation no longer shows up in the show create table tablename column output.

Try it out

$> php change_collation_everything.php

You can see all of the available charsets and their corresponding collations with the show collation sql query

Best Answer

Related Solutions

Mysql – Setting default charset/collation for MySQL database

Mysql – Changing collation type database-wide in MySQL

Related Question