sepdek February 5, 2012
mysql logo

Even though all default settings include “utf8-general-ci” every newly created database keeps getting that “swedish” collation and character set!
So one way to convert to utf8 is to go table by table and type the SQL command:

ALTER TABLE dbase.table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

where ‘dbase’ is the name of the database and ‘table’ the name of each table.

In the general case, there are far too many tables to do it this way (and still be happy…)

So there is a shortcut although indirect. You have to execute an SQL command to read all tables and produce the SQL commands that will be used to actually do the job for you!
The first step is to collect all table names and produce new SQL commands to alter them:

SELECT CONCAT('ALTER TABLE ', tbl.TABLE_SCHEMA, '.', tbl.TABLE_NAME, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') FROM information_schema.TABLES tbl WHERE tbl.TABLE_SCHEMA = 'dbase'

again, ‘dbase’ is the name of the database to alter.

What you get is a list of SQL commands to alter the collation and character set of all tables. Then you copy the result and run it as the final SQL command that will do the conversion.

Discussion

comments

6 thoughts on “Convert all tables in a MySQL database to utf8_general_ci

  1. If you want to convert all tables in My Sql database into utf8_general_ci then use the ALTER DATABASE and ALTER TABLE commands.

    At first use this command to change your database
    ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_general_ci;

    then use it to change your table
    ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

  2. Just what I was looking for. Thank you.

    With;
    ALTER DATABASE dbase CHARACTER SET utf8 COLLATE utf8_general_ci;

    I was able to make phpMyAdmin show, under all the tables, next to; ‘Sum, InnoDB’ ‘utf8_general_ci’

Leave a Reply to OScar Cossio Cancel reply

Your email address will not be published. Required fields are marked*

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: