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

     

    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

  • get charset of column

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

  • Change default charset of database

     

    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

    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.

 

 

 

 

 

Posted in tips of hosting