mysql 批量修改表及字段字符集

-- 修改表字符集

SELECT  CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') 

FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='test'
AND TABLE_TYPE='BASE TABLE';

 

 

 

--修改字段字符集

 

select 
  CONCAT('ALTER TABLE `', TABLE_NAME,'` CHANGE `',COLUMN_NAME,'` `',COLUMN_NAME,'` VARCHAR(',CHARACTER_MAXIMUM_LENGTH,') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ',
 case when COLUMN_COMMENT is null or COLUMN_COMMENT = ''   then ' ' else CONCAT(' COMMENT ',"'",COLUMN_COMMENT,"'") end ,
';') 

from INFORMATION_SCHEMA.Columns t where 1=1 and table_schema='test'
and data_type='varchar'
and table_name  not like 't%';