当我们在一个数据库修改完备注后，需要将其导致另一个服务器上的数据库中，需要生成批量执行语句，方便操作，注意用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 '时间';
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 '';
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' ;