Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jan 18 '13 at 17:10

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 0 down vote accepted

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')
}
share|improve this 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!"; 
}
?>
share|improve this answer
    
@altern Please let me know if this helps. –  OMG Jan 18 '13 at 14:37
    
sorry, this won't work as long as I do not have access to the server side :( –  altern Jan 18 '13 at 17:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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