mysql批量修改数据表的字符集和排序规则

SELECT
	CONCAT(
		'ALTER TABLE `',
		table_name,
		'` MODIFY `',
		column_name,
		'` ',
		DATA_TYPE,
		'(',
		CHARACTER_MAXIMUM_LENGTH,
		') CHARACTER SET UTF8 COLLATE utf8_general_ci',
		' default ',
		(
			CASE
			WHEN COLUMN_DEFAULT = '0' THEN
				'0'
			ELSE
				"''"
			END
		),
		' comment ',
		"'",
		column_comment,
		"'",
		(
			CASE
			WHEN IS_NULLABLE = 'NO' THEN
				' NOT NULL'
			ELSE
				''
			END
		),
		';'
	)
FROM
	information_schema. COLUMNS
WHERE
	TABLE_SCHEMA = 'test111' // 数据库
AND DATA_TYPE = 'varchar'
AND (
	CHARACTER_SET_NAME != 'utf8'
	OR COLLATION_NAME != 'utf8_general_ci'
);

只修改数据表 

SELECT
	CONCAT(
		"ALTER TABLE ",
		table_name,
		" CONVERT TO CHARACTER  SET utf8mb4 COLLATE utf8mb4_general_ci;"
	) AS sql_statements
FROM
	information_schema. TABLES
WHERE
	table_schema = "database_name"
AND table_type = "BASE TABLE"
ORDER BY
	table_name DESC;

生成的sql

最后将生成的sql语句执行就好了