In MySQL/MariaDB, I set the connection character set to UTF-8:
SET NAMES 'utf8mb4'; SET CHARACTER SET 'utf8mb4';
There are some tables with a latin1_swedish_ci collation and latin1 encoded data in them. SELECTing from them gives me correct UTF-8 data.
When I once incorporated SET NAMES, SET CHARACTER SET in my program startup code, I assumed that I could give UTF-8 encoded data in INSERT strings to the MySQL server and it would convert it according to the table/column collation. That assumption came from me testing locally with MariaDB, where it worked. Then on the production site with MySQL: The UTF-8 data would not be converted to latin1 but saved as UTF-8 in Latin1 column. To fix that, I always encode values explicitly to latin1 when INSERTing into latin1 table.
Now, I revisited some code that did not SET NAMES, SET CHARACTER SET. I changed it to SET it. Now the MariaDB/MySQL difference strikes again. What is the difference between MariaDB and MySQL character set handling?