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.

2 Answers 2


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:



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

if(!$connection) {
    echo "Cannot connect to the database – incorrect details";
} else{
    $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!"; 
  • @altern Please let me know if this helps.
    – OMG
    Commented Jan 18, 2013 at 14:37
  • sorry, this won't work as long as I do not have access to the server side :(
    – altern
    Commented Jan 18, 2013 at 17:04
  • 1
    Is it safe to do this in a production environment?
    – Vincent
    Commented Sep 1, 2019 at 2:14

Just found out that I can use groovy for scripting purposes:

import groovy.sql.Sql

def database = 'dbname’
def host = ‘localhost’
def port = 3306
def login = ‘root’
def password = ‘’
def sql = Sql.newInstance('jdbc:mysql://${host}:${port}/${database}', login, password, 'com.mysql.jdbc.Driver')
def tables = sql.rows('show tables from ' + database)
tables.collect{it.get('Tables_in_' + database)}.each{
       sql.execute('alter table ' + it + ' convert to character set utf8 collate utf8_general_ci')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.