Mysql存储过程批量修改数据库中的表

// 判断是否有相同的存储过程,有则删除
DROP PROCEDURE IF EXISTS test;
// 创建存储过程
CREATE PROCEDURE test()
//存储过程开始
BEGIN
//定义一个标记,初始默认值是0,目的是为了循序list
DECLARE flag INT DEFAULT 0;
//定义一个变量,后面在拼接SQL时使用
DECLARE id VARCHAR(100);
//前半部分是创建一个list集合,把后面select出来的数据放到集合中去循环
//我这边是查出来所有的表中含有某个字段的表名
DECLARE idList CURSOR FOR SELECT table_name FROM information_schema.columns WHERE TABLE_SCHEMA = ‘zwpan_mes’ AND COLUMN_NAME=‘project_name’;
//相当于hasNext 找到下一个标记
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
//打开游标 开始循环
OPEN idList;
//取出游标中的值,放在变量里
FETCH idList INTO id;
//开始循环
WHILE flag != 1 DO
//拼接SQL
SET @execSql = CONCAT(‘UPDATE ‘,id,’ SET project_name = “测试12345” WHERE project_id = 544’);
//调用拼接的SQL
PREPARE stmt FROM @execSql;
//关闭这个调用
EXECUTE stmt;
//游标往后移动一位!!!一定记得!!!
FETCH idList INTO id;
//结束循环
END WHILE;
//关闭游标
CLOSE idList;
//结束
END;

调用该存储过程
CALL test();

下面贴代码直接复制:

DROP PROCEDURE IF EXISTS test;
CREATE PROCEDURE test()
BEGIN
	DECLARE flag INT DEFAULT 0;
	DECLARE id VARCHAR(100);
	DECLARE idList CURSOR FOR SELECT table_name FROM information_schema.columns WHERE TABLE_SCHEMA = 'TABLE_SCHEMA' AND COLUMN_NAME='project_name' ;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
	OPEN idList;
		FETCH idList INTO id;
		WHILE flag != 1 DO
			SET @execSql = CONCAT('UPDATE ',id,' SET project_name = "XXX" WHERE project_id = XXX');
			PREPARE stmt FROM @execSql;
			EXECUTE stmt;
			FETCH idList INTO id;
		END WHILE;
	CLOSE idList;
END;
CALL test();