Converting old WordPress tables to InnoDB / utf8mb4 / utf8mb4_unicode_ci

Hi,

I started using WordPress 1.2 for my personal blog in 2005. Today, after 18 years, my blog is powered by the latest WordPress 6.0 and it works great, except that some of the text is not shown correctly but is still readable…

Over the years, I have noticed that MySQL / MariaDB tables have been created with different charsets and collations. This is what I have today:

• 15 tables using ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
• 7 tables using ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
• 7 tables using ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
• 3 tables using ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
• 2 tables using ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
• 2 tables using ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
• and, finally, 1 table using ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

and you will all agree that this is a mess!

Ideally, I would like to convert all the database tables to the default format of modern WordPress which is ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci. Would a database dump, changing the ENGINE / CHARSET / COLLATE in the SQL file and then recreating the database will work?

I understand that the “difficult part” is converting the data to the correct format. But, as I tend to write in English and, rarely, in French, this is not a big issue in the sense that most of the text is 8-bit ASCII. In fact, I would like to try to convert the tables first without actually converting the data. Do you think this will work?

Thanks.

(PS: I am proficient with SQL, mysqldump, Linux, etc.)

The page I need help with: [log in to see the link]