规范化命名外键注释

 

 

DROP PROCEDURE if EXISTS rename_fk_col_comment;
DELIMITER //
CREATE PROCEDURE `rename_fk_col_comment`(
    IN `prefix_tbl_name` VARCHAR(255)
)
BEGIN
 DECLARE tb_name VARCHAR (200) ;
 DECLARE cons_name VARCHAR (200) ;
 DECLARE col_name VARCHAR (200) ;
 DECLARE ref_tbl_name VARCHAR (200) ;
 DECLARE ref_col VARCHAR (200) ;
 DECLARE rf_table_comment VARCHAR(1024);
 
 -- 表名后缀，去除tbl_bat_之后的内容
 DECLARE suffix_tbl_name VARCHAR(200);


  
 
DECLARE no_more_record INT DEFAULT 0 ;
 -- 查询每个表下的约束。（外键和唯一键）
 DECLARE cons CURSOR FOR 
 SELECT 
     t.TABLE_NAME,
     t.CONSTRAINT_NAME,
     k.COLUMN_NAME,
     k.REFERENCED_TABLE_NAME,
     k.REFERENCED_COLUMN_NAME,
     (SELECT tt.table_comment FROM INFORMATION_SCHEMA.tables tt WHERE tt.TABLE_NAME=k.REFERENCED_TABLE_NAME and tt.TABLE_SCHEMA=DATABASE()) AS rf_table_comment
 FROM
 information_schema.TABLE_CONSTRAINTS t 
 JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k 
 ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
 AND t.TABLE_NAME = k.TABLE_NAME 
 AND t.CONSTRAINT_SCHEMA=k.CONSTRAINT_SCHEMA
 WHERE  t.CONSTRAINT_TYPE='FOREIGN KEY' AND t.table_schema = DATABASE() ;
 
 -- 游标游到底，找不到的时候标志位。注：如果select into var时候，如果找不到，游标的while循环也结束了，no_more_record =1
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record = 1 ;
 
 -- 如果前缀为空，默认使用tbl_作为前缀。
if(prefix_tbl_name IS NULL OR prefix_tbl_name='' OR prefix_tbl_name=' ') then
    SET @prefix_tbl_name='tbl_';
ELSE
    SET @prefix_tbl_name=prefix_tbl_name;
END if;

 -- 禁用外键
 SET FOREIGN_KEY_CHECKS=0;
 -- 开游标
 OPEN cons;
 -- 第一个要预先取得，之后在循环里面取
 FETCH cons INTO tb_name,cons_name,col_name,ref_tbl_name,ref_col,rf_table_comment;
 WHILE no_more_record !=1 DO

 -- 截取表名后半部分。去除tbl_bat_字样
SET suffix_tbl_name =REPLACE(tb_name,@prefix_tbl_name,'');


-- 拼接alter 语句
-- alter table `tbl_order_work` change `id` `id` bigint(20) unsigned NOT NULL auto_increment  comment '标识，物理主键' ;
SELECT 
      CONCAT_WS(
        '',
        'alter table `',
        t1.TABLE_NAME,'` change ',
        '`', COLUMN_NAME,'` ', 
        '`', COLUMN_NAME,'` ', -- 新字段名
        COLUMN_TYPE,
        ' ',
        IF(
          IS_NULLABLE = 'NO',
          'NOT NULL',
          'NULL'
        ),
        ' ',
        extra,
        ' ',
        IF(
          COLUMN_DEFAULT = NULL,
          '',
          CONCAT('default ', COLUMN_DEFAULT)
        ),
        " comment '",
        -- 可以重复执行，保留原有注释内容
        CONCAT_WS('',SUBSTRING_INDEX(COLUMN_COMMENT, ' [外键]', 1), CONCAT_WS('',' [外键]',': 来自 ',ref_tbl_name,'(',rf_table_comment,')','表(',ref_col,')字段')), -- 新注释COLUMN_COMMENT
        "' ;"
      ) INTO @alter_sql
    FROM
      information_schema.COLUMNS t1 
    WHERE t1.table_schema = DATABASE()
      AND t1.TABLE_NAME = tb_name AND t1.COLUMN_NAME=col_name;

-- 替换某个字符串之后所有内容
-- SELECT CONCAT(SUBSTRING_INDEX('Hello World', ' ', 1), ' Universe') ==> Hello Universe
 
 -- 动态执行拼接出来的sql
 PREPARE stmt FROM @alter_sql;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 
 -- 游标继续向下走，类似于i=i+1
 FETCH cons INTO tb_name,cons_name,col_name,ref_tbl_name,ref_col,rf_table_comment;
 END WHILE;
 
 -- 开启外键
 SET FOREIGN_KEY_CHECKS=1;

 
 END//
DELIMITER ;