How to store UTF8 characters in MySQL

MySQL allows you to store UTF8 characters in database. This is useful while working with non-ascii or unicode data. In fact, you can even convert all tables to UTF8, if you want. In this article, we will look at how to support full unicode characters in MySQL. Here’s how to store UTF8 characters in MySQL.

How to Store UTF8 characters in MySQL

Here are the steps to store UTF8 characters in MySQL. By default, MySQL supports only ASCII characters and stores data in latin1 character set.

1. Check Character Set

Open terminal and run the following command replacing username below with your database username

$ mysql -u username -p

You will see a password prompt. Enter password and log into MySQL.

Run the following command to list the character set

mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | cp850                                                   |
| character_set_connection | cp850                                                   |
| character_set_database   | latin1                                                  |
| character_set_filesystem | binary                                                  |
| character_set_results    | cp850                                                   |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.6\share\charsets\ |
+--------------------------+---------------------------------------------------------+

Bonus Read : How to Rank Over Partition in MySQL

2. Change Character Set to UTF8

Open my.cnf file in terminal

$ sudo vi /etc/my.cnf

Add /replace the line after [client] and [mysqld]

[client] 
default-character-set=utf8mb4 
[mysqld] 
character-set-server = utf8mb4

Bonus Read : How to Change MySQL Timezone in Query

3. Restart MySQL Server

Restart MySQL Server to apply changes

$ sudo service mysql restart

From now on, all your future databases will be able to store UTF8 characters. However, your existing databases will continue to support their old character set.

So we need to convert your database to UTF8

Bonus Read : How to Change MySQL Server Time Zone

4. Convert MySQL database to UTF8

Here are the steps to convert all tables to UTF8. Log into MySQL as shown in step 1.

Run the following command to change the character set and collation of your database to UTF8. Replace dbname below with your database name.

mysql> ALTER DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

The above command will convert all tables in your database to UTF8.

Hopefully, the above tutorial will help you store UTF8 data in MySQL.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!