mysql批量生成修改表和列注释语句

当我们在一个数据库修改完备注后，需要将其导致另一个服务器上的数据库中，需要生成批量执行语句，方便操作，注意用change 或modified 进行列的属性修改时，没有写全的话会造成其它属性的丢失，如

t_users 表中有字段 name 备注名为 姓名，默认值为0，如果用alter table t_users change column name name varchar(10) ;那么备注名就会消失，默认值也会消失。故通过如下语句可以生成完事的alter 语句

 

生成表修改备注语句

修改数据库备注名：
单条语句写法：
alter table t_version comment '版本'

生成批量语句写法：
select concat("alter table " ,TABLE_NAME, " comment ", "'",TABLE_COMMENT,"'",";") from information_schema.tables 
where information_schema.tables.TABLE_SCHEMA='dwzx' and information_schema.tables.TABLE_TYPE='BASE TABLE'

 

生成列修改

 

单条语句写法：ALTER TABLE t_content CHANGE name name [type] [character set utf8]  [COLLATE 'utf8_general_ci']  [not null] DEFAULT ['xxx' | null] [auto_increment]  COMMENT  'xxx'
以下有几种比较典型的写法(全格式写)：
ALTER TABLE `t_month_detail` CHANGE `bpu_time` `bpu_time` timestamp NOT NULL  DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP  COMMENT  '发布时间';--timestamp 类型比较特别，得写全，如下写法：
ALTER TABLE `t_month_detail` CHANGE `bpu_time` `bpu_time` timestamp COMMENT  '发布时间';
默认值不为null。
ALTER TABLE `t_award_user` CHANGE `id` `id` bigint(20)    NOT NULL   auto_increment  COMMENT  ''; -- 没有加auto_increment会丢失此属性
ALTER TABLE `t_agens` CHANGE `intro` `intro` char(10) character set utf8  COLLATE 'utf8_general_ci'  null  DEFAULT null    COMMENT  '机构简介';
ALTER TABLE `t_news` CHANGE `use` `use` bit(1)    null  DEFAULT b'0'   COMMENT  '是否显示';



生成批量语句写法--完整格式写法(呕心沥血之作)
select concat("ALTER TABLE ","`",col.TABLE_NAME,"`"," CHANGE ","`",col.COLUMN_NAME,"`"," ","`",col.COLUMN_NAME,"`"," ",col.COLUMN_TYPE , " ",
if(col.CHARACTER_SET_NAME is null," ",concat(" character set ",col.CHARACTER_SET_NAME," ")),
if(col.COLLATION_NAME is null," ",concat(" COLLATE ","'",col.COLLATION_NAME,"' ")),
if(col.IS_NULLABLE='NO'," NOT NULL "," null "), 
if(col.COLUMN_DEFAULT is null , if(col.EXTRA='auto_increment' or col.IS_NULLABLE='NO'," "," DEFAULT null ") ,concat(" DEFAULT ",if(col.DATA_TYPE='timestamp' or col.DATA_TYPE='bit' ,col.COLUMN_DEFAULT,concat("'",col.COLUMN_DEFAULT,"'")))),
if(col.EXTRA is null ," ",concat(" ",col.EXTRA," "  )),
" COMMENT "," ","'",col.COLUMN_COMMENT,"'",";") change_column_type
from information_schema.COLUMNS col 
join information_schema.tables  tbl  on col.TABLE_SCHEMA=tbl.TABLE_SCHEMA and col.TABLE_NAME=tbl.TABLE_NAME
where col.TABLE_SCHEMA='dwzx' and tbl.TABLE_TYPE='BASE TABLE'  ;