Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
How to easily convert utf8 tables to utf8mb4 in MySQL 5.5
# For each database:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# For each table:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# For each column:
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# (Don’t blindly copy-paste this! The exact statement depends on the column type, maximum length, and other properties. The above line is just an example for a `VARCHAR` column.)
@ivangrynenko

This comment has been minimized.

Copy link

commented May 4, 2017

An easier way to convert the database is to follow the first step from your example converting the database and then you can use this SQL to get the required convert
SELECT CONCAT('ALTER TABLE', TABLE_NAME,'CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS mySQL FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= "beetbox" AND TABLE_TYPE="BASE TABLE"
It will print the queries to run for all tables in your database - beetbox in my case

@eSilverStrike

This comment has been minimized.

Copy link

commented May 4, 2017

And the columns for those tables? Do you have an easy way to convert those as well? There is no point altering the table if you don't alternate the required columns as well in those same tables.

I've updated your select statement slightly to put the table names in quotes:

SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') AS mySQL FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= "geeklogwork" AND TABLE_TYPE="BASE TABLE" 
@3manuek

This comment has been minimized.

Copy link

commented Jun 13, 2017

Something like that will be for by-column conversion:

select CONCAT('ALTER TABLE `', TABLE_SCHEMA, '.', TABLE_NAME,'` CHANGE ',COLUMN_NAME,' ', COLUMN_NAME, ' ', DATA_TYPE,'(',CHARACTER_MAXIMUM_LENGTH,') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') as column_alter from COLUMNS WHERE DATA_TYPE = 'varchar' AND TABLE_SCHEMA = '<YOUR_DATABASE>'
@MrCsabaToth

This comment has been minimized.

Copy link

commented Oct 23, 2018

@3manuek If I convert the table, will it convert all the columns as well to utf8mb4?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.