OK, A mistake was made. The database was made with a latin1 charset in the age of emoji. Sorry I mean Unicode. Should be easy enough to fix. You quickly find out that you want to use the charset utf8mb4 and the collation utf8_unicode_ci for you database / tables / columns, in order to take full advantage how most text is stored today.
What about Postgres? This may have been a good chance to migrate over to PostgreSQL.