刷新字符编码集

将数据库字符编码集修改为utf8mb4_general_ci

-- 数据库字符编码集修改为utf8mb4_general_ci
SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    CHARACTER_SET_NAME,
    COLLATION_NAME,
		column_comment,
    CONCAT( 'ALTER TABLE `', TABLE_SCHEMA, '`.',
            TABLE_NAME, ' MODIFY COLUMN ',
            COLUMN_NAME, ' ', COLUMN_TYPE, 
            ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ', CASE WHEN column_default IS NOT NULL OR column_default!='' THEN CONCAT(' DEFAULT \'',column_default,'\' ') ELSE '' END, case when is_nullable = 'YES' THEN ' NULL' ELSE ' NOT NULL' END,' COMMENT \'', column_comment, '\'; ' ) '修正SQL'
FROM
    information_schema.`COLUMNS`
WHERE
        TABLE_SCHEMA = '库名'
				and table_name not like '%act_%'
				and collation_name != 'utf8mb4_general_ci'
AND ( DATA_TYPE = 'varchar' OR DATA_TYPE = 'char' OR DATA_TYPE='longtext');
  
  
  
-- 生成 ALTER TABLE 语句  
SELECT CONCAT('ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') AS alter_statement  
FROM information_schema.tables   
WHERE table_schema = '主题名'   
AND table_type = 'BASE TABLE' and table_name != 'utf8mb4_general_ci'