Skip to content

Instantly share code, notes, and snippets.

@ohvitorino
Last active February 7, 2020 10:23
Show Gist options
  • Save ohvitorino/275c7e7368caac5888574afc1be7c6bb to your computer and use it in GitHub Desktop.
Save ohvitorino/275c7e7368caac5888574afc1be7c6bb to your computer and use it in GitHub Desktop.
Generate ALTER statements to convert a database from utf8 to utf8mb4
use information_schema;
SELECT concat("ALTER DATABASE `",table_schema,"` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;") as _sql
FROM `TABLES` where table_schema like "database-name" group by table_schema;
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql
FROM `TABLES` where table_schema like "database-name" group by table_schema, table_name;
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",IF(is_nullable="YES"," NULL"," NOT NULL"),";") as _sql
FROM `COLUMNS` where table_schema like "database-name" and data_type in ('varchar','char');
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",IF(is_nullable="YES"," NULL"," NOT NULL"),";") as _sql
FROM `COLUMNS` where table_schema like "database-name" and data_type in ('text','tinytext','mediumtext','longtext');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment