Mysql批量修改排序规则

改变表排序规则

SELECT TABLE_SCHEMA '数据库',TABLE_NAME '表',TABLE_COLLATION '原排序规则',
CONCAT( 'ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' COLLATE=utf8mb4_general_ci;' ) '修正SQL' 
FROM information_schema.`TABLES` WHERE TABLE_COLLATION LIKE '需要修改的排序编码';

改变字段排序规则

SELECT
CONCAT('ALTER TABLE `',table_name,'` MODIFY `',column_name,'` ',DATA_TYPE,'(',CHARACTER_MAXIMUM_LENGTH,') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',(CASE WHEN IS_NULLABLE='NO' THEN ' NOT NULL' ELSE '' END),(CASE WHEN IFNULL(column_comment,'')='' THEN '' ELSE concat(' COMMENT \'',column_comment,'\'') END),';') AS `sql`
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA='要修改的数据库名称' AND DATA_TYPE='varchar' AND COLLATION_NAME !='被改的编码';