Mysql 查询结果列表作为参数循环生成插入

新建插入语句的带参存储过程

DROP PROCEDURE IF EXISTS _marketjson;
DELIMITER //
CREATE PROCEDURE _marketjson(IN refid VARCHAR(32)) 
BEGIN
	## 插入数据前判断是否已存在
	IF NOT EXISTS (SELECT * FROM column WHERE LOCATION_REFID = refid and DISPLAY_TITLE = '身份证号') THEN 
		INSERT INTO `column` (`REFID`,`LOCATION_REFID`,`DISPLAY_TITLE`)
		VALUES
			( MD5(RAND() * 10000),refid,'身份证号') ;
	END IF;	
END//  
DELIMITER ; 

游标遍历查询结果并操作数据库

DROP PROCEDURE IF EXISTS insert_extra_column;
DELIMITER //
CREATE PROCEDURE insert_extra_column() 
BEGIN
	##定义判断变量
	DECLARE _flag varchar(50);
	## 定义查询变量
	DECLARE _cur CURSOR FOR 
		SELECT `REFID` FROM person;
	### 循环赋初始值
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET _flag=NULL;
	### 打开
	OPEN _cur;
	## 赋值
	FETCH _cur INTO _flag;
	###循环判断
	WHILE (_flag is not null ) DO
		CALL _marketjson(_flag);
		## 赋值下一个游标
		FETCH _cur INTO _flag;
	END WHILE;
	## 关闭
	CLOSE _cur; 
END//  
DELIMITER ; 
CALL insert_extra_column();