SELECT CONCAT('ALTER TABLE `',table_schema,'`.`',table_name,'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ') AS _sqlįROM `TABLES` WHERE table_schema LIKE 'YOUR_DATABASE_NAME' AND TABLE_TYPE='BASE TABLE' GROUP BY table_schema, table_name UNION SELECT CONCAT('ALTER DATABASE `',table_schema,'` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ') AS _sqlįROM `TABLES` WHERE table_schema LIKE 'YOUR_DATABASE_NAME' AND TABLE_TYPE='BASE TABLE' GROUP BY table_schema UNION You should always backup your database in case something goes wrong.Ĭopy the following query into gen_queries.sql, replacing the 4 occurrences of YOUR_DATABASE_NAME with the name of the database you wish to convert: USE information_schema It converts all columns of the type varchar, text, tinytext, mediumtext, longtext, char. This solution will generate and then run queries needed to convert databases, tables and columns.

Section 10.1.11 of the MySQL 5.5 Reference Manual has some more information on this. As described in step 4 of the abovementioned guide, you’ll need to check the maximum length of columns and index keys, as the number you specify has a different meaning when utf8mb4 is used instead of utf8. Note, however, that you cannot fully automate the conversion from utf8 to utf8mb4.

The above line is just an example for a VARCHAR column.) (Don’t blindly copy-paste this! The exact statement depends on the column type, maximum length, and other properties. From my guide How to support full Unicode in MySQL databases, here are the queries you can run to update the charset and collation of a database, a table, or a column:

Mysql create database utf8