The Oracle mysql client has an odd behavior if your server uses latin1 as default character-set-server.

Command

mysql --version
mysql  Ver 8.0.31-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

mysql --default-character-set utf8mb4 -e "SHOW VARIABLES LIKE '%char%';"

Expectation

Rails professionals since 2007

Our laser focus on a single technology has made us a leader in this space. Need help?

• We build a solid first version of your product
• We train your development team
• We rescue your project in trouble

Read more Show archive.org snapshot

utf8mb4 will be used as character set in this session.

Reality

The mysql client falls back to latin1:

+--------------------------+----------------------------------------------+
| Variable_name            | Value                                        |
+--------------------------+----------------------------------------------+
| character_set_client     | latin1                                       |
| character_set_connection | latin1                                       |
| character_set_database   | utf8mb4                                      |
| character_set_filesystem | binary                                       |
| character_set_results    | latin1                                       |
| character_set_server     | latin1                                       |
| character_set_system     | utf8                                         |
+--------------------------+----------------------------------------------+

Explanation

The client can't handle the --default-character-set utf8mb4 command line option and falls back to the servers default character set. You could use --default-character-set utf8 and set the character set Show archive.org snapshot inside the session to circumvent this behavior.

Better solutions

Use MariaDB mysql client

The MariaDB mysql client handles the --default-character-set option correct:

mysql --version
mysql  Ver 15.1 Distrib 10.3.37-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

mysql --default-character-set utf8mb4 -e "SHOW VARIABLES LIKE '%char%';"
+--------------------------+----------------------------------------------+
| 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     | latin1                                       |
| character_set_system     | utf8                                         |
+--------------------------+----------------------------------------------+

Switch the servers character set

Both mysql clients will use the servers default character set. If you configure your server to use utf8mb4 even Oracles mysql can handle utf8mb4.

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