mysql change charset

Years ago, you may consider converting the charset of your mysql database from latin1 to utf8 as utf8 was prevailing and the default charset of mysql database is latin1. Today, you might want to change charset from utf8 to utf8mb4 as the (at most) 3 bytes of utf8 cannot represent some emoji characters. Before changing charset in query, you should know how to check charset and collation in mysql. You can get database charset and collation by querying the information_schema database:

• get charset of db
  
  MySQL

  SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "domainhostseotool";

  1	SELECT default_character_set_name FROM information_schema.SCHEMATA  WHERE schema_name = "domainhostseotool";

  
   

  Note that we check the default charset. The default charset means if you create tables for the database and do not specify a charset, the tables will use the default database charset as its charset.

• get charset of table
  MySQL

  SELECT CCSA.character_set_name FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "domainhostseotool" AND T.table_name = "wp_posts"; +--------------------+ | character_set_name | +--------------------+ | utf8 | +--------------------+ 1 row in set (0.00 sec)

  1 2 3 4 5 6 7 8

  SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,     information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation   AND T.table_schema = "domainhostseotool"   AND T.table_name = "wp_posts"; +--------------------+ | character_set_name | +--------------------+ | utf8               | +--------------------+ 1 row in set (0.00 sec)

• get charset of column
  MySQL

  mysql> SELECT character_set_name FROM information_schema.`COLUMNS` WHERE table_schema = "domainhostseotool" AND table_name = "wp_posts" AND column_name = "post_content"; +--------------------+ | character_set_name | +--------------------+ | utf8 | +--------------------+ 1 row in set (0.00 sec)

  1 2 3 4 5 6 7

  mysql> SELECT character_set_name FROM information_schema.`COLUMNS`  WHERE table_schema = "domainhostseotool"   AND table_name = "wp_posts"   AND column_name = "post_content"; +--------------------+ | character_set_name | +--------------------+ | utf8               | +--------------------+ 1 row in set (0.00 sec)

After checking current character set, you may want to use mysql query to convert charset.

• Change default charset of database
  
  MySQL

  mysql> ALTER DATABASE domainhostseotool CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

  1	mysql> ALTER DATABASE domainhostseotool CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

  
   

  The above mysql query converts the default charset from utf8 to utf8mb4 and the default collation from utf8_general_ci to utf8mb4_unicode_ci. That does not mean to change charset for all tables of the database. If you check table character set or check column charset, you will find the existing tables and columns do not change their charset at all. As said before, the default charset is only used when creating new tables.

• change charset of table
  MySQL

  mysql> ALTER TABLE wp_posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

  1	mysql> ALTER TABLE wp_posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

  The above mysql query converts table charset from utf8 to utf8mb4 and collate from utf8_general_ci to utf8mb4_unicode_ci. This includes the charset and collate of all columns of the table.

The charset and collate is an interesting topic in database. Using the following commands, you can find many character set or collate related variables in your system:

To know more about charset and collate, please refer to this article.