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.) |
This comment has been minimized.
This comment has been minimized.
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:
|
This comment has been minimized.
This comment has been minimized.
Something like that will be for by-column conversion:
|
This comment has been minimized.
This comment has been minimized.
@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
This comment has been minimized.
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