MySQL MariaDB Set utf8mb4 as default charset

Categories:

  2 minute read  

By default charset is set to to latin1 and utf8. This can be checked by executing the following command

MariaDB [(none)]> SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_client     | utf8              |
| character_set_connection | utf8              |
| character_set_database   | latin1            |
| character_set_filesystem | binary            |
| character_set_results    | utf8              |
| character_set_server     | latin1            |
| character_set_system     | utf8              |
| collation_connection     | utf8_general_ci   |
| collation_database       | latin1_swedish_ci |
| collation_server         | latin1_swedish_ci |
+--------------------------+-------------------+

To set the charset as utf8mb4 do the following steps:

sudo vi /etc/mysql/conf.d/mariadb.cnf

Uncomment the following lines

 [client]
 #default-character-set = utf8

 [mysqld]
 #character-set-server  = utf8
 #collation-server      = utf8_general_ci
 #character_set_server  = utf8
 #collation_server      = utf8_general_ci

replace the above section as follows:

 [client]
 default-character-set = utf8mb4

 [mysqld]
 character-set-server  = utf8mb4
 collation-server      = utf8mb4_unicode_ci
 character_set_server  = utf8mb4
 collation_server      = utf8mb4_unicode_ci

Note:

  • changed all utf8 to utf8mb (More info on utf8mb4 check reference section below)
  • changed utf8_general_ci to utf8mb4_unicode_ci

Check the variable again.

MariaDB [(none)]> SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

Note:

Including

skip-character-set-client-handshake

or

character-set-client-handshake = FALSE

in my.cnf will change collation_connection variable display as utf8mb4_unicode_ci instead of utf8mb4_general_ci, however it force the connection to use utf8mb4_uncode_ci regardless of whatever requested by client.

References