通常要批量改字段的字符集和排序字符集时,网上查出的基本上都是
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'