How to solve MySQL character encoding problem?

Last updated on September 11th, 2020 at 11:47 am

MySQL character encoding problem is where your MySQL database causes UTF8 characters (like å, ä and ö) to be displayed wrongly. This is because by default, MySQL uses latin1 character set. This problem has become important due to the global nature of web content these days. You can solve it by setting MySQL character encoding to UTF8. This can be done in multiple ways.

1. Run a query “SET NAMES ‘utf8’” at the start of each connection – this will force MySQL to use UTF8 for the lifetime of that connection. This is a good option if you don’t have access to the my.cnf (linux/mac) / my.ini (windows) file.
2. Find and edit the file my.cnf (linux/mac) / my.ini (windows) on your database server. Add the following lines to the mysqld section : [mysqld] default-character-set=utf8 skip-character-set-client-handshake Now if you restart MySQL server, the MySQL character encoding should default to UTF8
3. What if you already have a database? How to convert it’s content to UTF8? You can use the following 3 queries. Replace DATABASE_NAME, TABLE_NAME and FIELD_NAME with your values. alter table TABLE_NAME modify FIELD_NAME blob; alter database DATABASE_NAME charset=utf8; alter table TABLE_NAME modify FIELD_NAME varchar(255) character set utf8; Convert the field to BLOB, that’s a binary field type. Then we change the character set of the database to utf8. Finally we convert our field back to text or varchar.

You can check your MySQL character encoding with following query.

Default MySQL character encoding

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | latin1                     |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

Required MySQL character encoding

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

You will also need to ensure your programming framework like Django, Code Igniter, etc use UTF8 encoding.

Sreeram Sreenivasan

Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.