MySQL批量修改数据库及表及字段字符集

alter database 数据库名 character set utf8mb4;

ALTER TABLE  表名 DEFAULT CHARACTER SET utf8 COLLATE utf8mb4_general_ci;

SELECT TABLE_NAME,CONCAT('ALTER TABLE  ',TABLE_NAME,' DEFAULT CHARACTER SET ',a.DEFAULT_CHARACTER_SET_NAME,' COLLATE ',a.DEFAULT_COLLATION_NAME,';') executeSQL FROM information_schema.SCHEMATA a,information_schema.TABLES b
WHERE a.SCHEMA_NAME=b.TABLE_SCHEMA
AND a.DEFAULT_COLLATION_NAME!=b.TABLE_COLLATION
AND b.TABLE_SCHEMA='数据库名'

ALTER TABLE  表名 CHANGE  列名  列名  VARCHAR( 100 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;

select b.table_name,b.column_name,b.character_set_name,b.collation_name
,CONCAT('ALTER TABLE ',b.table_name,' MODIFY ',b.column_name,' ',b.DATA_TYPE,'(',b.CHARACTER_MAXIMUM_LENGTH,') ',CASE WHEN b.COLUMN_DEFAULT IS NULL THEN ''  ELSE CONCAT('DEFAULT \'',b.COLUMN_DEFAULT,'\'') END,' COMMENT \'',b.COLUMN_COMMENT,'\';') executeSQL
 from information_schema.TABLES a,information_schema.COLUMNS b where  b.character_set_name IS NOT NULL and a.TABLE_SCHEMA=b.TABLE_SCHEMA AND a.TABLE_NAME=b.TABLE_NAME
AND a.TABLE_COLLATION!=b.COLLATION_NAME
and a.TABLE_SCHEMA='数据库名'

DROP PROCEDURE IF EXISTS `chanageCharSet`;
CREATE PROCEDURE `chanageCharSet`() 
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE scheamName VARCHAR(100);
    DECLARE tableName VARCHAR(100);
    DECLARE columnName VARCHAR(100);
    DECLARE columnType VARCHAR(100);
    DECLARE alertSql VARCHAR(200);
    DECLARE _Cur CURSOR FOR (
            SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE 
            FROM `information_schema`.`COLUMNS` 
            WHERE DATA_TYPE='varchar' AND COLLATION_NAME <> 'utf8mb4_general_ci' AND TABLE_SCHEMA like '数据库名%'
    );
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN _Cur;
    REPEAT  
        FETCH _Cur INTO scheamName, tableName, columnName, columnType;  
            IF NOT done THEN 
                SET alertSql = CONCAT(
                    'ALTER TABLE `',
                    scheamName,
                    '`.`',
                    tableName,
                    '` MODIFY COLUMN `',
                    columnName,
                    '` ',
                    columnType,
                    ' CHARACTER SET utf8 COLLATE utf8mb4_general_ci;'
                );        
                SET @ESQL = alertSql;
                PREPARE stmt1 FROM @ESQL;
                EXECUTE stmt1;
                DEALLOCATE PREPARE stmt1;
            END IF;
    UNTIL done END REPEAT;
    CLOSE _Cur;
END;
CALL chanageCharSet();
DROP PROCEDURE IF EXISTS `chanageCharSet`;