mysql 添加字段_mysql存储过程全表添加字段

BEGIN

#Routine body goes here...

DECLARE tName VARCHAR (64) ;

  DECLARE done INT DEFAULT FALSE ;

  DECLARE cur CURSOR FOR 

  SELECT 

    table_name 

  FROM

    information_schema.tables 

  WHERE table_schema = dateName and table_name like 'test%' ;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE ;

  OPEN cur ;

  posLoop :

  LOOP

    FETCH cur INTO tName ;

    IF done 

    THEN LEAVE posLoop ;#

    END IF;

#判断表中是否有这个字段

IF (SELECT count(1) FROM information_schema.COLUMNS

WHERE

COLUMN_NAME = 'is_del'

AND TABLE_NAME = tName)<=0 then

#执行语句同上CONCAT来拼接动态参数(注意拼接过程中的空格，引号等)

SET @_sqlStr=CONCAT('ALTER TABLE ',tName ," ADD COLUMN `is_del` varchar(8) NOT NULL DEFAULT '10010002' COMMENT '是否删除：10010001是,10010002否';");

#ALTER TABLE test ADD COLUMN `is_del`  varchar(8) NOT NULL DEFAULT '10010002' COMMENT '是否删除：10010001是,10010002否;'

# 预定义sql   预定义拼接好的sql语句

PREPARE stmt FROM @_sqlStr ;

# 执行sql

EXECUTE stmt ;

# 释放连接

DEALLOCATE PREPARE stmt ;

END if;

  END LOOP posLoop ;

  CLOSE cur ;

END

设置存储过程的参数时需要添加上参数长度