Convert all tables in a MySQL database to utf8_general_ci

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 all table collation and character set. Then you copy the result and run it as the final SQL command that will do the conversion.

Created on: 12th October, 2022

Last updated: 12th October, 2022

Tagged With:

• mysql