MySql动态生成SQL并执行

场景：由于一些表中设计了一些冗余字段，因此在主表修改了该冗余字段的值得时候，需要动态更新在其他表中冗余字段的值

 1 BEGIN
 2     #Routine body goes here...
 3    
 4   /*SQL语句变量*/
 5   DECLARE vstrSql VARCHAR(3000) DEFAULT ''; 
 6   /*映射表字段*/
 7   DECLARE vSourceTableName VARCHAR(50);
 8   DECLARE vSourceIdFieldName VARCHAR(50);
 9   DECLARE vSourceNameFieldName VARCHAR(50);
10   DECLARE vTargetTableName VARCHAR(50);
11   DECLARE vTargetIdFieldName VARCHAR(50);
12   DECLARE vTargetNameFieldName VARCHAR(50); 
13 
14   /*更新数据*/
15   DECLARE vnewValue VARCHAR(100);
16   DECLARE vSourceIdFieldValue VARCHAR(100);
17 
18   DECLARE Done INT DEFAULT 0;
19   /*声明游标*/
20   DECLARE curRow CURSOR FOR 
21   SELECT SourceTableName,SourceIdFieldName,SourceNameFieldName,TargetTableName,TargetIdFieldName,TargetNameFieldName
22   FROM db_redundancy.TableFieldMap;
23   /*设置终止标记*/
24   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
25   /*打开游标*/
26   OPEN curRow;
27     /*循环取出数据*/
28     FETCH NEXT FROM curRow INTO vSourceTableName,vSourceIdFieldName,vSourceNameFieldName,vTargetTableName,vTargetIdFieldName,vTargetNameFieldName;
29     WHILE Done<>1 DO
30        #获取需要更新的数据
31        SELECT SourceNameFieldNewValue,SourceIdFieldValue INTO vnewValue,vSourceIdFieldValue FROM db_redundancy.TableFieldValueModifyLog 
32        WHERE  IsProcess=0 AND SourceTableName=vSourceTableName AND SourceIdFieldName=vSourceIdFieldName AND SourceNameFieldName=vSourceNameFieldName
33        ORDER BY CreateDate DESC  limit 1;
34        IF ISNULL(vnewValue) <> NULL || LENGTH(trim(vnewValue))>1 THEN
35          #拼接语句
36          SET vstrSql=CONCAT(' UPDATE ',vTargetTableName,' SET ', vTargetNameFieldName ,' = "',vnewValue,'" WHERE ', vTargetIdFieldName ,' = ', vSourceIdFieldValue,';');
37          #注意很重要，将连成成的字符串赋值给一个变量（可以之前没有定义，但要以@开头）
38          SET @vSql= vstrSql;
39          #预处理需要执行的动态SQL，其中stmt是一个变量
40          PREPARE stmt  FROM @vSql;
41          #执行语句
42          EXECUTE stmt ;
43          #释放语句
44          DEALLOCATE PREPARE  stmt;
45          #更新历史表中状态标记
46          UPDATE db_redundancy.TableFieldValueModifyLog SET IsProcess=1 WHERE IsProcess=0 AND SourceTableName=vSourceTableName AND SourceIdFieldName=vSourceIdFieldName AND SourceIdFieldValue=vSourceIdFieldValue;
47        END IF;
48        #重置数据
49        SET vnewValue='';
50        SET vstrSql='';
51       FETCH NEXT FROM curRow INTO vSourceTableName,vSourceIdFieldName,vSourceNameFieldName,vTargetTableName,vTargetIdFieldName,vTargetNameFieldName;
52     END WHILE;
53   /*关闭游标*/
54   CLOSE curRow;
55 END

在此记录一下，方便以后查询以及他人参照。