mysql 备用字段_MySQL使用存储过程为数据库中全部的表增加备用字段-阿里云开发者社区...

需求描述

要为数据库里的所有的表统一加上十六个备用字段，前提是备用字段名取表名前三位，拼接上备用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；

表结构截图：

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；

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