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 TABLEdbase.tableCONVERT 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;') FROMinformation_schema.TABLEStbl 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.
You are my hero… you just saved me tons of tedious work!
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;
Perfect, thanks a lot.
Thanks, excellent contribution … you saved a lot of work