一次开发过程中遇到一个问题：由于同一个openID对应的不同类型的userID不正确，所以需要改成一样的。
编写逻辑：
①先查询类型为5002的openID,得到需要修改的数据
②根据便利得到的openID，查询类型5001的userID
③根据openID修改相应openID且类型为5002的数据
函数如下：
CREATE DEFINER=`root`@`localhost` PROCEDURE `updatePhone`()
BEGIN
-- 定义变量
DECLARE done INT DEFAULT FALSE;
DECLARE openId varchar(32);
DECLARE userID varchar(32);
DECLARE userID1 varchar(32);
DECLARE openList CURSOR FOR select DISTINCT open_id from authorization_record where auth_type = '5002';
-- 游标中的内容执行完后将done设置为true
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
OPEN openList;
read_loop : LOOP
FETCH openList INTO openId;
-- 判断是否结束循环，一定要放到FETCH之后，因为在fetch不到的时候才会设置done为true
-- 如果放到fetch之前，先判断done，这个时候done的值还是之前的循环的值，因此就会导致循环一次
IF done THEN
LEAVE read_loop;
END IF;
-- 执行SQL操作
SELECT distinct a.user_id INTO userID FROM authorization_record a WHERE a.open_id = CONVERT(openId USING utf8) COLLATE utf8_unicode_ci AND a.auth_type = '5001';
-- GROUP BY a.open_id;
-- SELECT t.user_id INTO userID1 FROM ( SELECT a.user_id FROM authorization_record a WHERE a.open_id = openId AND a.auth_type = '5001' GROUP BY a.open_id ) t ;
IF userID != '' THEN
UPDATE authorization_record SET user_id = CONVERT(userID USING utf8) COLLATE utf8_unicode_ci WHERE open_id = CONVERT(openId USING utf8) COLLATE utf8_unicode_ci AND auth_type = '5002';
COMMIT;
END IF;
END LOOP read_loop;
-- 释放游标
CLOSE openList;
END;
