修改字符集

alter database xxx character set utf8;

SELECT TABLE_NAME,CONCAT(‘ALTER TABLE ‘,TABLE_NAME,’ DEFAULT CHARACTER SET ‘,a.DEFAULT_CHARACTER_SET_NAME,’ COLLATE ‘,’utf8_general_ci’,’;’) executeSQL FROM information_schema.SCHEMATA a,information_schema.TABLES b
WHERE a.SCHEMA_NAME=b.TABLE_SCHEMA
AND ‘utf8_general_ci’!=b.TABLE_COLLATION
AND b.TABLE_SCHEMA=’xxx’

select CONCAT(‘ALTER TABLE ‘,b.table_name,’ MODIFY ‘,b.column_name,’ ‘,b.DATA_TYPE,'(‘,b.CHARACTER_MAXIMUM_LENGTH,’) CHARACTER SET utf8 COLLATE utf8_general_ci ‘,CASE WHEN b.COLUMN_DEFAULT IS NULL THEN ” ELSE CONCAT(‘DEFAULT \”,b.COLUMN_DEFAULT,’\”) END,’ COMMENT \”,b.COLUMN_COMMENT,’\’;’) executeSQL
from information_schema.TABLES a,information_schema.COLUMNS b where b.character_set_name IS NOT NULL and a.TABLE_SCHEMA=b.TABLE_SCHEMA AND a.TABLE_NAME=b.TABLE_NAME
AND ‘utf8_general_ci’!=b.COLLATION_NAME
and a.TABLE_SCHEMA=’xxx’

查询之后，拷贝sql到excel整理执行