为表名不同但属性相同的多个表添加字段的mysql存储过程示例

为多张相似表添加字段的mysql存储过程示例

为多张相似表添加字段的mysql存储过程示例
/* 在hmp DB服务器上执行一下sql */
/* modify table [hmp_data_glucose_XXX] structure */
USE  bwfHmp;
DROP PROCEDURE IF EXISTS  bwfHmp.addColumns;
CREATE PROCEDURE bwfHmp.addColumns(IN _tabName VARCHAR(30),IN _dbName VARCHAR(30))
BEGIN
DECLARE RESULT TINYINT(1) DEFAULT 0;
DECLARE sporttagcons VARCHAR(50);
DECLARE mytagcons VARCHAR(50);
DECLARE mealtimecons VARCHAR(50);


SET @tabName=_tabName;
SET @dbName=_dbName;
#要添加的字段
SET @col_sport='sporttag';
SET @col_my='mytag';
SET @col_meal='mealtime';
#所添加字段的属性
SET sporttagcons=' TINYINT(2) NOT NULL DEFAULT 0';
SET mytagcons=' VARCHAR(300)';
SET mealtimecons=' VARCHAR(30) NOT NULL DEFAULT \'\'';


SET @querysql='SELECT 1 INTO @RES FROM information_schema.`COLUMNS` WHERE COLUMN_NAME=? AND TABLE_NAME=? AND TABLE_SCHEMA=?'; 
SET @addsql=CONCAT('ALTER TABLE ',@tabName,' ADD COLUMN ',@col_sport,sporttagcons);
SET @addsql1=CONCAT('ALTER TABLE ',@tabName,' ADD COLUMN ',@col_my,mytagcons);
SET @addsql2=CONCAT('ALTER TABLE ',@tabName,' ADD COLUMN ',@col_meal,mealtimecons);

#添加字段sporttag
SET @RES=0;
PREPARE stmt FROM @querysql;
EXECUTE stmt USING @col_sport,@tabName,@dbName;
SET RESULT=@RES;
IF RESULT!=1 THEN
PREPARE stmt FROM @addsql;
EXECUTE stmt;
END IF;
#添加字段mytag
SET @RES=0;
PREPARE stmt FROM @querysql;
EXECUTE stmt USING @col_my,@tabName,@dbName;
SET RESULT=@RES;
IF RESULT!=1 THEN
PREPARE stmt FROM @addsql1;
EXECUTE stmt;
END IF;
#添加字段mealtime
SET @RES=0;
PREPARE stmt FROM @querysql;
EXECUTE stmt USING @col_meal,@tabName,@dbName;
SET RESULT=@RES;
IF RESULT!=1 THEN
PREPARE stmt FROM @addsql2;
EXECUTE stmt;
END IF;
#释放执行中使用的数据库资源
DEALLOCATE PREPARE stmt;
END;


CALL  bwfHmp.addColumns('hmp_data_glucose_000','bwfHmp');
SELECT 1 INTO @RES FROM information_schema.`COLUMNS` WHERE COLUMN_NAME='mytag' AND TABLE_NAME='hmp_data_glucose_000' AND TABLE_SCHEMA='bwfHmp';
SELECT @RES;


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值