Skip to content

Instantly share code, notes, and snippets.

@mareksuscak

mareksuscak/utf8mb4.sql

Last active February 23, 2024 10:23
Show Gist options
  • Star 4 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mareksuscak/712f6c9e6ead557897d7db164a04f136 to your computer and use it in GitHub Desktop.
Save mareksuscak/712f6c9e6ead557897d7db164a04f136 to your computer and use it in GitHub Desktop.
Download ZIP
MySQL utf8 to utf8mb4 conversion
Raw
utf8mb4.sql
-- adapted from https://dba.stackexchange.com/a/104866/247902
USE information_schema;
SELECT CONCAT("SET foreign_key_checks = 0;") AS _sql UNION
SELECT CONCAT("ALTER DATABASE `",table_schema,"` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;") AS _sql
FROM `TABLES` WHERE table_schema LIKE "<YOUR_DATABASE>" AND TABLE_TYPE='BASE TABLE' GROUP BY table_schema UNION
SELECT CONCAT("ALTER TABLE `",table_schema,"`.`",table_name,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;") AS _sql
FROM `TABLES` WHERE table_schema LIKE "<YOUR_DATABASE>" AND TABLE_TYPE='BASE TABLE' GROUP BY table_schema, table_name UNION
SELECT CONCAT("ALTER TABLE `",`COLUMNS`.table_schema,"`.`",`COLUMNS`.table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type,"(",character_maximum_length,") CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci",IF(column_default IS NOT NULL," DEFAULT \'",""),IF(column_default IS NOT NULL,column_default,""),IF(column_default IS NOT NULL,"\'",""),IF(is_nullable="YES"," NULL"," NOT NULL"),IF(column_comment<>''," COMMENT \'",""),IF(column_comment<>'',column_comment,""),IF(column_comment<>'',"\'",""),";") AS _sql
FROM `COLUMNS` INNER JOIN `TABLES` ON `TABLES`.table_name = `COLUMNS`.table_name WHERE `COLUMNS`.table_schema like "<YOUR_DATABASE>" and data_type in ('varchar','char') AND TABLE_TYPE='BASE TABLE' UNION
SELECT CONCAT("ALTER TABLE `",`COLUMNS`.table_schema,"`.`",`COLUMNS`.table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type," CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci",IF(column_default IS NOT NULL," DEFAULT \'",""),IF(column_default IS NOT NULL,column_default,""),IF(column_default IS NOT NULL,"\'",""),IF(is_nullable="YES"," NULL"," NOT NULL"),IF(column_comment<>''," COMMENT \'",""),IF(column_comment<>'',column_comment,""),IF(column_comment<>'',"\'",""),";") AS _sql
FROM `COLUMNS` INNER JOIN `TABLES` ON `TABLES`.table_name = `COLUMNS`.table_name WHERE `COLUMNS`.table_schema like "<YOUR_DATABASE>" and data_type in ('text','tinytext','mediumtext','longtext') AND TABLE_TYPE='BASE TABLE' UNION
SELECT CONCAT("SET foreign_key_checks = 1;") AS _sql;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment