How to change Collation to utf8_bin in one go

Question

I have set up the Collation of all my database tables as latin1_swedish_ci and now I realise that I should use utf8_bin or utf8_general_ci.

How can I change the Collation in the tables to utf8_bin or utf8_general_ci in one go? Can I use a query or something?

Answer 1

You'll simply need to run an ALTER on each of the tables as follows:

ALTER TABLE <table name> COLLATE utf8_general_ci;

If you also need to update the existing character encoding (unlikely by the sounds of things), you can use:

ALTER TABLE <table name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Answer 2

Here are two ways. First one worked for me. From the terminal (Just remember to backup before.)

mysql --database=dbname -B -N -e "SHOW TABLES"  | awk '{print "ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;"}' | mysql --database=dbname &

Source: Commandlineinfu.com

From MySQL you will have to use the Concat command

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` = 'dbname'

Answer 3

You can also update the database collation with:

ALTER DATABASE  `DATABASE_NAME` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci

Answer 4

You can change the collation of a table with ALTER TABLE:

alter table table_name collate=utf8_general_ci;