Q004Mysql批量修改字段注释

MYSQL本身没有这种批量的命令。不过你可以自己生成 alter table t modify  的脚本，一次性把所有需要修改的列的注释更新。

先利用sql进行表格的信息查询来制作执行语句（网上轮子）：

SELECT CONCAT(
               'alter table ',
               table_schema, '.', table_name,
               ' modify column ', column_name, ' ', column_type, ' ',
               IF(is_nullable = 'YES', ' ', 'not null '),
               IF(column_default IS NULL, '',
                  IF(
                              data_type IN ('char', 'varchar')
                              OR
                              data_type IN ('date', 'datetime', 'timestamp') AND column_default != 'CURRENT_TIMESTAMP',
                              CONCAT(' default ''', column_default, ''''),
                              CONCAT(' default ', column_default)
                      )
                   ),
               IF(extra IS NULL OR extra = '', '', CONCAT(' ', extra)),
               ' comment ''', column_comment, ''';'
           ) '组合语句'
  FROM information_schema.columns
 WHERE table_schema = 'study'              -- 库名
   AND table_name = 'jsl_bond_info_temp_t' -- 表名

--------------------------------------------------------------------------

alter table study.jsl_bond_info_temp_t modify column put_price decimal(15,3) comment '';
alter table study.jsl_bond_info_temp_t modify column turnover_rt decimal(15,3) comment '';
alter table study.jsl_bond_info_temp_t modify column curr_iss_amt decimal(15,3) comment '';
alter table study.jsl_bond_info_temp_t modify column rating_cd varchar(10) comment '';
alter table study.jsl_bond_info_temp_t modify column issuer_rating_cd varchar(10) comment '';
alter table study.jsl_bond_info_temp_t modify column guarantor varchar(240) comment '';
alter table study.jsl_bond_info_temp_t modify column repo_cd varchar(30) comment '';
alter table study.jsl_bond_info_temp_t modify column sincrease_rt varchar(10) comment '';
alter table study.jsl_bond_info_temp_t modify column premium_rt varchar(10) comment '';
alter table study.jsl_bond_info_temp_t modify column year_left decimal(15,3) comment '';
alter table study.jsl_bond_info_temp_t modify column ytm_rt varchar(10) comment '';
alter table study.jsl_bond_info_temp_t modify column increase_rt varchar(10) comment '';
alter table study.jsl_bond_info_temp_t modify column volume decimal(15,3) comment '';
alter table study.jsl_bond_info_temp_t modify column short_maturity_dt date comment '';
alter table study.jsl_bond_info_temp_t modify column dblow decimal(15,3) comment '';
alter table study.jsl_bond_info_temp_t modify column force_redeem_price decimal(15,3) comment '';
alter table study.jsl_bond_info_temp_t modify column put_convert_price varchar(20) comment '';
alter table study.jsl_bond_info_temp_t modify column convert_amt_ratio varchar(10) comment '';

 

自己再加注释。。。。