查询(这是执行一个查询sql,不是执行修改,需要复制出来,执行,下面得类似)
SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` CHARACTER SET = utf8mb4, COLLATE = utf8mb4_general_ci;')
FROM information_schema.`TABLES`
WHERE
`TABLE_SCHEMA` = '数据库名'
SELECT
TABLE_SCHEMA '数据库名',
CHARACTER_SET_NAME '原字符集',
COLLATION_NAME '原排序规则',
CONCAT(
'ALTER TABLE ',
TABLE_NAME,
' MODIFY COLUMN ',
COLUMN_NAME,
' ',
COLUMN_TYPE,
-- - 设置新的编码和排序规则
' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',
( CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END ),
( CASE WHEN COLUMN_COMMENT = '' THEN ' ' ELSE concat( ' COMMENT''', COLUMN_COMMENT, '''' ) END ),
';'
) '修正SQL'
FROM
information_schema.`COLUMNS`
WHERE
-- -过滤正确排序规则
COLLATION_NAME != 'utf8mb4_general_ci'
-- -数据库名称
AND TABLE_SCHEMA = '数据库名';