//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 ;
记录一次存储过程批量修改表结构
于 2022-07-06 20:42:19 首次发布