mysql字段的字符集批量修改

通常要批量改字段的字符集和排序字符集时，网上查出的基本上都是

SELECT concat('ALTER TABLE `',

TABLE_SCHEMA,'`.`',

TABLE_NAME,

'` MODIFY COLUMN `',

COLUMN_NAME,'` ',

COLUMN_TYPE,

' CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;')

FROM information_schema.COLUMNS

但这样是错误的，有两点，1.样连视图的字段也会查出，需要过滤，2.这样会把是否为空，默认值和注释给去掉，修改后这三个属性都变成默认。

因而修改如下：

SELECT concat('ALTER TABLE `',

c.TABLE_SCHEMA,'`.`',

c.TABLE_NAME,

'` MODIFY COLUMN `',

c.COLUMN_NAME,'` ',

c.COLUMN_TYPE,

' CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci',

(case when c.IS_NULLABLE='YES' then ' null' else ' not null' end),

(CASE WHEN c.COLUMN_DEFAULT IS NULL THEN ' ' ELSE concat(' DEFAULT ','\'',c.COLUMN_DEFAULT,'\'') END),

' COMMENT \'',c.COLUMN_COMMENT,'\';')

FROM information_schema.COLUMNS c

left join information_schema.tables t

on c.TABLE_SCHEMA =t.TABLE_SCHEMA and t.TABLE_NAME =c.TABLE_NAME

WHERE c.TABLE_SCHEMA = 'sys_basedb'

AND c.DATA_TYPE IN ('varchar','char')

and (c.CHARACTER_SET_NAME <>'utf8mb4' or c.COLLATION_NAME <>'utf8mb4_0900_ai_ci')

and t.TABLE_TYPE ='BASE TABLE'