进城务工人员小梅SQL写得真溜：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
SELECT /* Disable foreign key checks temporily to be able to make these changes */ 'SET FOREIGN_KEY_CHECKS = 0;' AS alter_statement UNION SELECT /* Alter the default character set of each database */ CONCAT('ALTER DATABASE `', SCHEMA_NAME,'` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS alter_statement FROM information_schema.SCHEMATA WHERE DEFAULT_CHARACTER_SET_NAME!='utf8mb4' AND SCHEMA_NAME NOT IN('mysql','information_schema','performance_schema','sys') UNION SELECT /* Alter the default character set of each table . This also converts all text columns in the table, So there is no need to have a statement to alter each column individually */ DISTINCT CONCAT('ALTER TABLE `', TABLE_SCHEMA,'`.`',TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS alter_statement FROM ( SELECT /* Find all tables with a text column that isn't utf8mb4 */ TABLE_SCHEMA, TABLE_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema','sys') AND CHARACTER_SET_NAME IS NOT NULL AND CHARACTER_SET_NAME!='utf8mb4' UNION SELECT /* Also find all tables that don't have the correct default character set */ TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES AS T JOIN information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C ON C.collation_name = T.table_collation WHERE CHARACTER_SET_NAME!='utf8mb4' AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema','sys') ) AS TABLE_UPDATES UNION SELECT /* Re-enable forign key checks */ 'SET FOREIGN_KEY_CHECKS = 1;' AS alter_statement ; |
参考资料：
1、https://dba.stackexchange.com/questions/257962/upgrade-all-mysql-columns-tables-and-databases-from-utf8mb3-to-utf8mb4
转载时请保留出处，违法转载追究到底：进城务工人员小梅 » MySQL批量修改编码为utf8mb4