使用临时表替换游标：提高效率

小灰灰 2022-07-28 04:26 172阅读 0赞

![spinner.gif][]CREATE DEFINER=\`root\`@\`%\` PROCEDURE \`debug\`(IN \`beginTime\` int, IN \`checkTime\` int)

1.  BEGIN 
2.      DECLARE t\_id VARCHAR(64) DEFAULT ''; 
3.      DECLARE t\_item TINYINT DEFAULT 0; 
4.      DECLARE t\_result VARCHAR(8192) DEFAULT ''; 
5.       
6.      DECLARE maxCnt INT DEFAULT 0; 
7.      DECLARE i INT DEFAULT 0; 
8.   
9.      DROP TABLE IF EXISTS Gather\_Data\_Tmp; 
10.     CREATE TEMPORARY TABLE Gather\_Data\_Tmp( 
11.         \`Tmp\_Id\` INT UNSIGNED NOT NULL AUTO\_INCREMENT, 
12.         \`Asset\_Id\` VARCHAR(16) NOT NULL, 
13.         \`Check\_Item\` TINYINT(1) NOT NULL, 
14.         \`Check\_Result\` VARCHAR(8192) NOT NULL, 
15.         PRIMARY KEY (\`Tmp\_Id\`) 
16.     )ENGINE=MyISAM DEFAULT CHARSET=utf8; 
17.  
18.     SET @tSql = CONCAT('INSERT INTO Gather\_Data\_Tmp (\`Asset\_Id\`, \`Check\_Item\`, \`Check\_Result\`)  
19.                                             SELECT Asset\_Id, Check\_Item, Check\_Result  
20.                                             FROM IDC\_Gather\_Info  
21.                                             WHERE Check\_Time > ',beginTime,' AND Check\_Time <= ',checkTime); 
22.     PREPARE gatherData FROM @tSql; 
23.     EXECUTE gatherData; 
24.  
25.     SELECT MIN(\`Tmp\_Id\`) INTO i FROM Gather\_Data\_Tmp; 
26.     SELECT MAX(\`Tmp\_Id\`) INTO maxCnt FROM Gather\_Data\_Tmp; 
27.  
28.     WHILE i <= maxCnt DO 
29.         SELECT Asset\_Id, Check\_Item, Check\_Result INTO t\_id, t\_item, t\_result FROM Gather\_Data\_Tmp WHERE Tmp\_Id = i; 
30.         SET i = i + 1; 
31.     END WHILE; 
32. END

[spinner.gif]: /images/20220727/7ebaeecf58294b96a76ba311abd48aee.png