MySQL 8, character set change from latin1 to utf8mb4

The vendor defaults changed from latin1 in MySQL 5.7 to utf8mb4 in MySQL 8. A given server's configuration may differ. Upgrading MySQL does not automatically convert every existing table; the character set the application negotiates when it connects also matters.

This change is not usually a problem, because applications set the charset to use in their MySQL connection layer. Very old applications or custom code may not do this, though, and after the change you may find data showing on screen with "strange" characters.

Before changing anything, check the actual encoding of the tables and the configuration the application expects. Forcing latin1 is only correct when the project and its data still use that character set; it does not repair data that has already been converted or stored with the wrong encoding.

Check where the problem appears

Look at a known sample of text in the database and on the website, without saving it again yet. If phpMyAdmin shows it correctly and your application does not, check the connection and the encoding of the HTML response. If it is already stored incorrectly, changing the connection will not repair it. Work on a copy before trying to convert data.

From the same connection the application uses, you can run:

SELECT @@character_set_client, @@character_set_connection,
       @@character_set_results, @@collation_connection;

Running it from phpMyAdmin shows phpMyAdmin's session, not your application's. Also check the character set of the affected columns; it may differ from the table default.

Keep a latin1 application running for now

Set the character set in the connector itself. With MySQLi:

mysqli_set_charset($connection, 'latin1');

With PDO, include it in the DSN when you create the connection:

$dsn = 'mysql:host=localhost;dbname=BASE_DE_DATOS;charset=latin1';
$connection = new PDO($dsn, $user, $password);

Do not use mysql_select_db() or mysql_query(): they belong to the old mysql extension, removed from PHP in version 7. Sending SET NAMES manually with MySQLi is not recommended either, because mysqli_set_charset() keeps the character set the client uses for escaping strings in sync.

Prepare the upgrade

If the project supports full Unicode, plan the conversion to utf8mb4 with a clone, a restorable backup and read and write tests. Do not switch an application that already uses utf8mb4 correctly to latin1.