记录一次存储过程批量修改表结构

//DELIMITER先将分割符设置成$,因为语句中有许多;，避免中断，修改分隔符
DELIMITER $
CREATE PROCEDURE alter_table_enegine()
BEGIN
DECLARE count int DEFAULT 0 ;
DECLARE var_name VARCHAR(300);
DECLARE `@sqlstr` VARCHAR(2560);
DECLARE tableNames CURSOR FOR SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE 'terminal%';
SELECT count(*) into count FROM (SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE 'terminal%') t1;
OPEN tableNames;
WHILE count > 0 DO
	FETCH tableNames INTO var_name;
    SELECT count(*) INTO @colName FROM information_schema.columns WHERE table_name = var_name AND column_name = 'url';
    IF @colName = 0 THEN
        SET @sqlstr = CONCAT(
        "alter table ",
        var_name,
        " add url varchar(255) DEFAULT NULL"
        );
        PREPARE stmt FROM @sqlstr;
        EXECUTE stmt;
    END IF;
	SET count = count - 1;
END WHILE;
END;
-- 执行存储过程
CALL alter_table_enegine();
-- 执行完，删掉临时用的存储过程
DROP PROCEDURE alter_table_enegine;
$
DELIMITER ;