MySQL使用存储过程为数据库中全部的表增加备用字段

落日映苍穹つ 2022-04-06 01:44 220阅读 0赞

**需求描述  
要为数据库里的所有的表统一加上十六个备用字段，前提是备用字段名取表名前三位，拼接上备用1-16 ，  
16个字段中，其中8个varchar， 4个int， 4个datetime**

### 1.创建一个简单的表 ###

DROP TABLE customer_detail_info；
    
    CREATE TABLE `customer_detail_info` (
      `cus_id` int(11) NOT NULL,
      `name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`cus_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- 查看表结构
    DESC customer_detail_info；

表结构截图：  
![在这里插入图片描述][20181207122006964.png]

### 2.创建一个存储过程 ###

DROP PROCEDURE IF EXISTS `procedure_update_table`;
    DELIMITER ;;
    CREATE  PROCEDURE `procedure_update_table`(IN table_name VARCHAR(50))
    BEGIN
    declare i int;
    DECLARE sq VARCHAR(8000);
    SET  i = 1 ;
    WHILE i < 17 DO
    --  取表名前三位
    set @qz = LEFT(table_name,3);
    --  拼接字段名
    set @pp = CONCAT(@qz,i);
    -- 前八个字符类型
    IF i < 9 THEN
    SET  sq = CONCAT('ALTER table ',table_name,' add column ',@pp, ' VARCHAR(8) ');
    -- 中间四个int
    ELSEIF (i > 8 && i < 13) THEN
    SET  sq = CONCAT('ALTER table ',table_name,' add column ',@pp, ' INT(11)');
    -- 其他的时间类型
    ELSE 
    SET  sq = CONCAT('ALTER table ',table_name,' add column ',@pp, ' DATETIME ');
    end IF;
    SET i = i + 1 ;
    SET @_SQL = sq ;
    -- 预定义sql
    PREPARE stmt FROM @_SQL ;
    -- 执行sql
    EXECUTE stmt ;
    -- 释放连接
    DEALLOCATE PREPARE stmt;
    -- 结束While 循环
    END WHILE;
    
    END;

### 3.整个库中全部表获取的存储过程 ###

-- 已存在的存储删除
    DROP PROCEDURE IF EXISTS procedure_get_all_tables;
    DELIMITER ;;
    CREATE PROCEDURE procedure_get_all_tables()
    BEGIN
    -- 用于判断是否结束循环
    DECLARE done int DEFAULT 0;
    -- 存储表名称的变量
    DECLARE cur VARCHAR(200);
    
    DECLARE tbs_list CURSOR FOR SELECT TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_Schema = 'hqh_log';
    -- 定义 设置循环结束标识done值怎么改变 的逻辑
    declare continue handler for not FOUND set done = 1; 
    OPEN tbs_list;
    -- 循环开始 
    REPEAT
    FETCH tbs_list INTO cur;
    if not done THEN
       CALL procedure_update_table(cur);
    end if;
    until done end repeat;
    CLOSE tbs_list;
    END;

### 4.执行存储过程 ###

-- 调用存储过程
    CALL procedure_get_all_tables();
    -- 查看表结构
    DESC customer_detail_info；

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwNDY5MDg_size_16_color_FFFFFF_t_70]

给大家在提供一个mybatis调用存储过程的例子

[Mybatis调用MySQL存储过程][Mybatis_MySQL]

[20181207122006964.png]: /images/20220406/131018e7e3744ce68178bec26c3856dc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwNDY5MDg_size_16_color_FFFFFF_t_70]: /images/20220406/52609485aa1e4b338a598d825fcca142.png
[Mybatis_MySQL]: https://mp.csdn.net/mdeditor/69944959