mysql字符集批量修改

修改所有数据库字符集
SELECT CONCAT('ALTER DATABASE ',SCHEMA_NAME,' CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;') as 'Fanrncho'
FROM information_schema.`SCHEMATA` WHERE DEFAULT_CHARACTER_SET_NAME RLIKE 'utf8mb4' AND
SCHEMA_NAME NOT IN ('admindb', 'mysql', 'information_schema', 'performance_schema', 'sys');

----------------------------------------------------------------------------------------------------------------
修改表字符集
SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;') AS Fanrncho
FROM information_schema.`TABLES` WHERE TABLE_SCHEMA IN (SELECT SCHEMA_NAME FROM information_schema.`SCHEMATA`
WHERE DEFAULT_CHARACTER_SET_NAME RLIKE 'utf8mb4' AND SCHEMA_NAME NOT IN ('admindb', 'mysql', 'information_schema', 'performance_schema', 'sys'))
AND TABLE_TYPE="BASE TABLE";
---------------------------------------------------------------------------------------------------------------------
修改表排序规则
SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.',TABLE_NAME, ' MODIFY COLUMN ',COLUMN_NAME,' ',COLUMN_TYPE,' CHARACTER SET ascii COLLATE ascii_bin', ' ',
IF(IS_NULLABLE = 'NO', 'NOT NULL', 'NULL'), IF(ISNULL(COLUMN_DEFAULT), '', CONCAT(' DEFAULT \'', COLUMN_DEFAULT, '\'')), IF(ISNULL(COLUMN_COMMENT), '',
CONCAT(' COMMENT \'', COLUMN_COMMENT, '\';'))) AS 'Fanrncho' FROM information_schema.`COLUMNS` WHERE
TABLE_SCHEMA IN (SELECT SCHEMA_NAME FROM information_schema.`SCHEMATA` WHERE DEFAULT_CHARACTER_SET_NAME RLIKE 'utf8mb4'
AND SCHEMA_NAME NOT IN ('admindb', 'mysql', 'information_schema', 'performance_schema', 'sys')) and COLLATION_NAME RLIKE 'ascii';