How To Change Existing MYSQL Tables Characterset and Collation to UTF-8

This post is also available in: 日本語 (Japanese)

This article is about change characterset(and collation) of MYSQL database and table which is already used.
Before you try to command in this article, you should backup your MYSQL data.
Environment is PHP5.6 and ubuntu 14.04.

Change default MYSQL characterset and collation

This section is about changing default MYSQL characterset and collation.
Not be changed existing characterset and collation of MYSQL database and table(It is mentioned next section).

If you do not change default characterset and collation, new database and table are created by default characterset and collation,
By the way, there are 2 types collation about UTF-8(utf8mb4_unicode_ci,utf8mb4_general_ci).
If you want to use old MYSQL(MySQL ver < 5.5.3) or join with old MYSQL, I recomend to use collation which is "utf8_general_ci" or "utf8_unicode_ci".

At first, open MYSQL option-seting file(my.cnf), and additionally write as follows.
Location of the file(my.cnf) is different with OS.

sudo vi /etc/mysql/my.cnf 

##my.cnf
# Add end of [client] section
default-character-set=utf8
# Add end of [mysqld] section
character-set-server=utf8

Next Log in to MYSQL, and check the MYSQL default setting values.

mysql -u root -p
mysql> show databases;
mysql> show variables like "chara%";

Then, change characterset and collation of exsinting MYSQL database setting(This instruction not change for existing tables inside existing database).
Input command as follows.

#Sample DB name:database_name
mysql> ALTER DATABASE database_name CHARACTER SET utf8 COLLATE utf8mb4_unicode_ci;

After changing, the response of command "mysql> show variables like..." displayed like as follows.

Finally, restart MYSQL.

sudo /etc/init.d/mysql restart

Change existing MYSQL table's characterset and collation

Log in to MYSQL, and check the MYSQL DB list and table list.
And check the characterset and collation of existing tables.

mysql -u root -p
#Sampel DB Name:database_name, Sample Table Name:table_name
mysql> show databases;
mysql> USE `database_name`;
mysql> show tables from database_name;
mysql> select * from table_name;
mysql> show table status from database_name like 'table_name';

Next, change characterset and collation by specifying tables as following commands.

#Sample Table Name:table_name
mysql> ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8mb4_unicode_ci;

Finally, restart MYSQL.

sudo /etc/init.d/mysql restart

In case you want to change existing MYSQL tables in bulk

In case existing tables are too many, you can generate command in bulk as following command.
(Just generate, not executed!)

#Sample DB Name:database_name
mysql> SELECT CONCAT('ALTER TABLE `', tbl.`TABLE_SCHEMA`, '`.`', tbl.`TABLE_NAME`, '` CONVERT TO CHARACTER SET utf8 COLLATE utf8mb4_unicode_ci;') FROM `information_schema`.`TABLES` tbl WHERE tbl.`TABLE_SCHEMA` = 'database_name';

Finally, restart MYSQL.

sudo /etc/init.d/mysql restart

No tags for this post.