博客

#在数据库里获取拼接的处理sql
#解决字符串的字段
select  CONCAT('ALTER TABLE `',TABLE_NAME,'` MODIFY COLUMN `',COLUMN_NAME,'` ',COLUMN_TYPE
,case when CHARACTER_SET_NAME is not null then CONCAT(' CHARACTER SET ',CHARACTER_SET_NAME,' COLLATE ',COLLATION_NAME) else '' end
," DEFAULT '';") as alterSQL
from  information_schema.`COLUMNS` 
where TABLE_SCHEMA = '你的数据库名称' 
and data_type = 'varchar'
and IS_NULLABLE = 'NO' and COLUMN_KEY not in ('PRI') and EXTRA not in ('auto_increment')
and COLUMN_DEFAULT is null;
#解决数字类型的字段
select  CONCAT('ALTER TABLE `',TABLE_NAME,'` MODIFY COLUMN `',COLUMN_NAME,'` ',COLUMN_TYPE
,case when CHARACTER_SET_NAME is not null then CONCAT(' CHARACTER SET ',CHARACTER_SET_NAME,' COLLATE ',COLLATION_NAME) else '' end
,' DEFAULT  0;') as alterSQL
from  information_schema.`COLUMNS` 
where TABLE_SCHEMA = '你的数据库名称' 
and ( data_type = 'tinyint' or  data_type = 'int' or  data_type = 'int'  or  data_type = 'bigint'  )
and IS_NULLABLE = 'NO' and COLUMN_KEY not in ('PRI') and EXTRA not in ('auto_increment')
and COLUMN_DEFAULT is null;

#查看时间类型的字段,时间类型建议逐个更改
select *
from  information_schema.`COLUMNS` 
where TABLE_SCHEMA = '你的数据库名称' 
and COLUMN_KEY != 'PRI'
and ( data_type = 'timestamp' or  data_type = 'datetime'   )

and IS_NULLABLE = 'NO' and COLUMN_KEY not in ('PRI') and EXTRA not in ('auto_increment')
and COLUMN_DEFAULT is null;

#查看text类型的字段
select  CONCAT('ALTER TABLE `',TABLE_NAME,'` MODIFY COLUMN `',COLUMN_NAME,'` ',COLUMN_TYPE
,case when CHARACTER_SET_NAME is not null then CONCAT(' CHARACTER SET ',CHARACTER_SET_NAME,' COLLATE ',COLLATION_NAME) else '' end
," DEFAULT NULL;") as alterSQL
from  information_schema.`COLUMNS` 
where TABLE_SCHEMA = '你的数据库名称' 
and data_type = 'TEXT'
and IS_NULLABLE = 'NO' and COLUMN_KEY not in ('PRI') and EXTRA not in ('auto_increment')
and COLUMN_DEFAULT is null;

并非原创，是在别人的基础上进行改进