mysql实现插入时存在则进行更新

客官°小女子只卖身不卖艺 2023-05-31 07:09 22阅读 0赞

**需求**：从项目表（site\_info）中把所有的项目按照接入时间到现在的最小时间和最大时间并且是天表（site\_day）中流量大于0的，以及接入的天数汇总到一张新表（monitor\_site）中。需要每天执行一次，即第一次汇总全部是插入操作，以后的操作都是更新或插入操作。每次汇总后记录操作时间到记录表（operation\_log）中。（数据约有10w条）

**要求**：用存储过程实现（存储过程压根就不会，比较尴尬）

### 实现方式一： 使用游标遍历 ###

delimiter $$
    CREATE PROCEDURE `monitorSite`()
    BEGIN
    	DECLARE siteId VARCHAR(200); 
    	-- 自定义控制游标循环变量,默认false  
    	DECLARE done INT DEFAULT FALSE; 
    	-- 定义游标并输入结果集 
    	DECLARE siteIds CURSOR FOR (select DISTINCT s.siteId from site_info s where s.siteId is not null); 
    	-- 绑定控制变量到游标,游标循环结束自动转true
    	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    	-- 打开游标
    	OPEN siteIds; 
    	-- 开始循环体,myLoop为自定义循环名,结束循环时用到  
      myLoop: LOOP 
    		-- 将游标当前读取行的数据顺序赋予自定义变量siteId  
        FETCH siteIds into siteId; 
    		-- 判断是否继续循环 
        IF done THEN  
    	    -- 结束循环  	
          LEAVE myLoop; 
        END IF;  
    		-- 判断是否存在（存在更新，不存在插入）
    		IF EXISTS (select * from monitor_site m where m.siteKey = siteId) THEN
    			UPDATE monitor_site ms set 
    			ms.minDataTime = (select min(r.datatime) from site_day r where r.id = siteId),
    			ms.maxDataTime = (select max(r1.datatime) from site_day r1 where r1.id = siteId),
    			ms.validDataDays = (select timestampdiff(day,min(r2.datatime),max(r2.datatime)) from site_day r2 where r2.id = siteId),
    			ms.modifyTime = localtime()
    			where ms.siteKey = siteId;
    		ELSE 
    			insert into monitor_site (uuid,siteKey,minDataTime,maxDataTime,validDataDays,modifyTime)
    			SELECT UUID(),si.siteid,min(sd.datatime) minDataTime, max(sd.datatime) maxDataTime, timestampdiff(day,min(sd.datatime),max(sd.datatime)) validDataDays, localtime()
    			from site_info si 
    			inner join site_day sd on sd.id = si.siteid
    			WHERE si.siteid = siteId 
    			and sd.num > 0;
    		END IF;
    	  -- 提交事务	
        COMMIT;
    	-- 结束自定义循环体  
      END LOOP myLoop; 
    	-- 关闭游标
      CLOSE siteIds;  
    	-- 记录更新时间
    	update operation_log ol set ol.datetime = localtime(), ol.source = ol.source+1 where ol.tablename = 'monitor_site';
    END $$
    delimiter ;

使用这种方式执行速度特别慢，将近用了半小时左右。

注：没用过存储过程，从网上查阅这写的，可能慢的原因是写的存储过程本身就存在性能问题。

### 实现方式二： 使用SQL（ON DUPLICATE KEY UPDATE） ###

delimiter $$
    CREATE PROCEDURE `monitorSite`()
    BEGIN
    	-- 插入数据时若存在则进行更新操作
    	insert into monitor_site (uuid,siteKey,minDataTime,maxDataTime,validDataDays,modifyTime)
    	SELECT UUID() uuid,si.siteid siteKey,min(rsd.datatime) minDataTime, max(sd.datatime) maxDataTime, timestampdiff(day,min(sd.datatime),max(sd.datatime)) validDataDays, localtime() modifyTime
    	from site_info si 
    	left join site_day sd on sd.id = si.siteId 
    	and sd.num > 0 
    	GROUP BY si.siteId
    	ON DUPLICATE KEY UPDATE  
    	siteKey = VALUES(siteKey),
    	minDataTime = VALUES(minDataTime),
    	maxDataTime = VALUES(maxDataTime),
    	validDataDays = VALUES(validDataDays),
    	modifyTime = VALUES(modifyTime);
    	-- 记录更新时间
    	update operation_log ol set ol.datetime = localtime(), ol.source = ol.source+1 where ol.tablename = 'monitor_site';
    END $$
    delimiter ;

使用SQL执行速度为两三秒，效率提升了很多。