SELECT CONCAT(
'ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,
' MODIFY `',COLUMN_NAME,'` ',COLUMN_TYPE,' CHARACTER SET utf8 COLLATE utf8_general_ci ',
CASE IS_NULLABLE WHEN 'NO' then 'NOT NULL ' ELSE 'NULL ' END,
CASE when ISNULL(COLUMN_DEFAULT) then 'DEFAULT NULL ' WHEN COLUMN_DEFAULT='' THEN 'DEFAULT '''' ' WHEN COLUMN_DEFAULT<>'' THEN CONCAT('DEFAULT ''',COLUMN_DEFAULT,'''') ELSE '' END,
'COMMENT ''',COLUMN_COMMENT,''';'
)
FROM information_schema.`COLUMNS`
WHERE table_schema = "schema"
AND COLLATION_NAME like 'gb2312%'
mysql批量更新表的列信息
最新推荐文章于 2021-03-18 16:28:10 发布