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

-- 字符集为utf8mb4,排序规则为utf8mb4_unicode_ci

-- 修改数据库
ALTER DATABASE 数据库名 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 设置每张表的字符集和排序规则为为 utf8mb4、utf8mb4_unicode_ci
SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET  utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '数据库名';

-- 批量修改字符编码和排序编码
select concat('ALTER TABLE `', table_name, '` MODIFY `', column_name, '`', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH,
              ') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci', ';')
FROM information_schema.COLUMNS
WHERE table_schema = '数据库名'
  AND DATA_TYPE IN ('varchar' ,'char');