mysql批量修改数据表字段编码

获取数据库databasename的所有表名

SELECT table_name FROM information_schema.tables
WHERE table_schema = 'databasename' AND table_type = 'BASE TABLE';

获取数据库databasename的所有表的编码修改sql（推荐，修改后字段编码会被一并修改）

SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') as upsql
FROM information_schema.tables
WHERE table_schema = 'databasename' AND table_type = 'BASE TABLE';

临时外键约束关闭开启操作

-- 关闭外键约束
SET SESSION foreign_key_checks = 0;

-- 执行数据导入操作
-- ...

-- 重新启用外键约束
SET SESSION foreign_key_checks = 1;

获取数据库databasename的所有使用MyISAM存储引擎的表名并拼接转换为InnoDB存储引擎的sql

SELECT  CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ENGINE=InnoDB;') AS alter_statement
FROM information_schema.tables 
WHERE ENGINE = 'MyISAM' AND TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema');