mysql多表添加同一字段,为表名不同但属性相同的多个表添加字段的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;