Setting UTF-8 as the default encoding for MySQL configuration

MySQL proclaims itself as the most popular open source database. In this recipe, we will tell you how to set UTF-8 as the default encoding for it. Note that if you don't set this encoding in the database configuration, you might get into a situation where LATIN1 is used by default with your UTF-8-encoded data. This will lead to database errors whenever symbols such as € are used. Also, this recipe will save you from the difficulties of converting the database data from LATIN1 to UTF-8, especially when you have some tables encoded in LATIN1 and others in UTF-8.

Getting ready

Make sure that the MySQL database management system and the MySQLdb Python module are installed and you are using the MySQL engine in your project's settings.

How to do it...

Open the /etc/mysql/my.cnf MySQL configuration file in your favorite editor and ensure that the following settings are set in the [client], [mysql], and [mysqld] sections, as follows:

# /etc/mysql/my.cnf
[client]
default-character-set = utf8

[mysql]
default-character-set = utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect = ‘SET NAMES utf8’
character-set-server = utf8

If any of the sections don't exist, create them in the file. If the sections do already exist, add these settings to the existing configurations. Then, restart MySQL in your command-line tool, as follows:

$ /etc/init.d/mysql restart

How it works...

Now, whenever you create a new MySQL database, the databases and all of their tables will be set in UTF-8 encoding by default. Don't forget to set this on all computers on which your project is developed or published.

There's more...

For a Docker project, these settings can be added to the config/my.cnf file and saved to version control. This file will automatically be added as /etc/mysql/my.cnf within the container at build time. Furthermore, any developer that pulls down the code will automatically gain the configuration.

See also

• The Creating a virtual environment project file structure recipe
• The Creating a Docker project file structure recipe