I’ll introduce the steps to change the character encoding to UTF8 in MySQL 5.7.
Check character encoding with show variables
First, check the current character encoding with the show variables like ‘character_set%’; command.
mysql> show variables like 'character_set%';
+--------------------------+------------------------------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/Cellar/mysql/5.7.17/share/mysql/charsets/ |
+--------------------------+------------------------------------------------------+
8 rows in set (0.02 sec)
Add settings to my.cnf to set character_set_* to UTF8
Next, add settings to the MySQL configuration file my.cnf to set the character encoding to UTF8.
Adding the following settings will be sufficient:
[mysqld]
# character_set
character-set-server = utf8
[mysql]
default-character-set = utf8
Restart MySQL and check settings
Finally, restart MySQL to apply the settings.
To restart on Linux
$ service mysqld restart
$ /etc/init.d/mysqld restart
To restart on macOS
$ brew services restart mysql
Stopping `mysql`... (might take a while)
==> Successfully stopped `mysql` (label: homebrew.mxcl.mysql)
==> Successfully started `mysql` (label: homebrew.mxcl.mysql)
mysql> show variables like 'character_set%';
+--------------------------+------------------------------------------------------+
| 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/local/Cellar/mysql/5.7.17/share/mysql/charsets/ |
+--------------------------+------------------------------------------------------+
8 rows in set (0.01 sec)
It’s reassuring to set it to UTF8 from the beginning before running into character encoding issues.
Reference Information
That’s all from the Gemba.