mysql表实现没有某个字段则添加某个字段的存储过程
我要修改一个库中所有没有该字段的表
这是我写的存储过程:
BEGIN
DECLARE _tableName VARCHAR(32);
DECLARE _done INT DEFAULT 0;
DECLARE _cur CURSOR FOR
SELECT table_name FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA='test' AND TABLE_NAME NOT LIKE 'ACT%' group by TABLE_NAME ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done=1;#错误定义,标记循环结束
OPEN _cur;
REPEAT
FETCH _cur INTO _tableName;
IF NOT _done THEN
BEGIN
IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema = 'test' AND table_name = _tableName AND column_name = 'PROJECT_ID') THEN
SET @STMT := CONCAT("ALTER TABLE ",_tableName," ADD COLUMN PROJECT_ID VARCHAR(32);");
PREPARE STMT FROM @STMT;
EXECUTE STMT;
#ALTER TABLE _tableName ADD COLUMN PROJECT_ID VARCHAR(32);
END IF;
END;
END IF;
UNTIL _done END REPEAT;
CLOSE _cur;
SET _done = 0;#只有定义为0,新的循环才能继续。
END
执行提示:1265 - Data truncated for column '_tableName' at row 13
急求解!!