mysql 生成修改列编码的语句

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.',TABLE_NAME, ' MODIFY COLUMN `',COLUMN_NAME,'` ',COLUMN_TYPE,' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci', ' ', IF(IS_NULLABLE = 'NO', 'NOT NULL', 'NULL'), IF(ISNULL(COLUMN_DEFAULT), '', CONCAT(' DEFAULT \'', COLUMN_DEFAULT, '\'')), IF(ISNULL(COLUMN_COMMENT), '', CONCAT(' COMMENT \'', COLUMN_COMMENT, '\';'))) AS 修改字段编码格式语句
FROM information_schema.`COLUMNS`
WHERE TABLE_SCHEMA  NOT IN ('admindb', 'mysql', 'information_schema', 'performance_schema', 'sys')
    and COLLATION_NAME <> 'utf8mb4_general_ci'
    and COLLATION_NAME is not null                 -- 可以不用，上面'<>'的条件可以过滤空值
        and table_name not in (
            SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE ='VIEW'
        )
                
                

 

SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;') AS 修改表编码格式语句
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA IN (
    SELECT SCHEMA_NAME
    FROM information_schema.`SCHEMATA`
    WHERE DEFAULT_CHARACTER_SET_NAME = 'utf8mb4'
        AND SCHEMA_NAME NOT IN ('admindb', 'mysql', 'information_schema', 'performance_schema', 'sys'))
    AND TABLE_TYPE="BASE TABLE"
            and table_name not in (
            SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE ='VIEW'
        )