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
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
