order_id |
room_id |
company_id |
room_id |
hosue_id |
house_id |
company_id |
如上图所示,需要根据house_company_mapping把order表的company_id更新为正确的数据。数据量大概有10W条,肯定是不能通过程序写了,肯定很费时间。于是我采用了存储过程,这个虽然不推荐使用,但是我好像并没有其他好办法。代码如下:
## 公司列表维护数据
DROP PROCEDURE IF EXISTS FountTable;
DELIMITER $$
CREATE PROCEDURE FountTable()
BEGIN
DECLARE v_room_id VARCHAR(32);
DECLARE v_house_id VARCHAR(32);
DECLARE v_company_id VARCHAR(32);
DECLARE v_order_id VARCHAR(32);
#声明游标
DECLARE cur_FountTable CURSOR FOR SELECT id
FROM
order t
WHERE
company_id = 'a0000000000000000000000000000000'
AND exists(SELECT 1
FROM house_company_mapping a
WHERE a.house_id IN (
SELECT b.house_id
FROM room b
WHERE b.id = t.house_id
));
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_FountTable;
#打开游标
OPEN cur_FountTable;
REPEAT
FETCH cur_FountTable
INTO v_order_id;
SELECT room_id
INTO v_room_id
FROM order
WHERE id = v_order_id;
SELECT house_id
INTO v_house_id
FROM room
WHERE id = v_room_id;
SELECT ifnull(company_id, 'a0000000000000000000000000000000')
INTO v_company_id
FROM house_company_mapping m
WHERE m.house_id = v_house_id;
UPDATE order
SET company_id = v_company_id
WHERE id = v_order_id;
UNTIL 0 END REPEAT;
#关闭游标
CLOSE cur_FountTable;
END $$
DELIMITER ;
CALL FountTable();
我尽量让所有的查询和修改都通过索引操作,但是要遍历一个10w条数据的cursor 还是很费时间,大概需要2.5小时。后来知道了update join这种写法,于是我把存储过程改写成了下面这种写法:
UPDATE pay.pay_order a
JOIN (
SELECT
t.id order_id,
b.id house_id,
ifnull(c.company_id, 'a0000000000000000000000000000000') company_id
FROM pay_order t
LEFT JOIN room b
ON t.room_id = b.id
LEFT JOIN house_company_mapping c
ON b.house_id = c.house_id
WHERE t.company_id = 'a0000000000000000000000000000000'
) temp
ON a.id = temp.order_id
SET a.company_id = temp.company_id;
尝试了一下,基本在1分钟内就可以更新完10万条数据。所以推荐还是使用update join这种写法:
update table_a join table_b on a.id=b_id set a.name=b.name where 1=1;