mysql 批量修改表/字段字符集/排序规则

注：如下SQL执行时需修改 table_schema 的值为 数据库名， 默认的 db_name 是示例；运行后复制 ' SQL脚本 ' 列的SQL语句到数据库中执行即可。

1.  批量生成修改数据表编码及字符集脚本

SELECT
  table_schema		as '数据库',
  table_name 	    as '表名称',
  table_comment 	as '表描述',
  table_collation   as '原字符集',
  concat ( 'ALTER TABLE ', table_schema, '.', table_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') as 'SQL脚本'
FROM
  information_schema.`TABLES`
WHERE
  table_schema = "db_name" AND table_type = "BASE TABLE"

2. 批量生成修改表字段字符集/排序规则脚本

SELECT
	table_schema       as   '数据库',
	table_name         as   '表名称',
	column_name        as   '列名称',
	column_comment     as   '列描述',
	character_set_name as   '原字符集',
	collation_name     as   '原排序规则',
	concat ( 'ALTER TABLE ', table_schema, '.', table_name, ' MODIFY COLUMN ', column_name, ' ', column_type, 'CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT', '''', column_comment ,''';') as 'SQL脚本' 
FROM
	information_schema.`COLUMNS` 
WHERE
	table_schema= 'db_name';