Störm spoorun

mareksuscak / utf8mb4.sql

Last active June 16, 2025 16:10

MySQL utf8 to utf8mb4 conversion

	-- 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 NU