BEGIN
DECLARE done INT DEFAULT 0;
#字段
DECLARE insertid INT;
DECLARE name CHAR(50) CHARACTER SET utf8;
DECLARE cellphone CHAR(50);
DECLARE result INT DEFAULT 0;
DECLARE insertable CHAR(50);
DECLARE star INT DEFAULT (page-1)*size;
DECLARE checkmember INT DEFAULT 0;
DECLARE rs_cursor CURSOR FOR select `member_id`, `member_name`, `member_phone` from base_member_info ORDER BY `member_id` ASC LIMIT star,size;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
open rs_cursor;
#set autocommit=0;
set unique_checks=0;
set foreign_key_checks=0;
SET result = 0;
cursor_loop:loop
FETCH rs_cursor INTO insertid, name,cellphone;
if done = 1 then
LEAVE cursor_loop;
END IF;
SET result = result + 1;
SELECT count(`id`) INTO checkmember FROM member WHERE `id` = insertid LIMIT 1;
SELECT insertable;
SET insertable = insertid%10;
IF checkmember <= 0 THEN
if insertable = 0 THEN
#DELAYED
INSERT DELAYED INTO member_0(`id`,`user_name`,`member_name`,`cellphone`,`status`)VALUES(insertid,name,name,cellphone,'active');
ELSEIF insertable = 1 THEN
INSERT DELAYED INTO member_1(`id`,`user_name`,`member_name`,`cellphone`,`status`)VALUES(insertid,name,name,cellphone,'active');
ELSEIF insertable = 2 THEN
INSERT DELAYED INTO member_2(`id`,`user_name`,`member_name`,`cellphone`,`status`)VALUES(insertid,name,name,cellphone,'active');
ELSEIF insertable = 3 THEN
INSERT DELAYED INTO member_3(`id`,`user_name`,`member_name`,`cellphone`,`status`)VALUES(insertid,name,name,cellphone,'active');
ELSEIF insertable = 4 THEN
INSERT DELAYED INTO member_4(`id`,`user_name`,`member_name`,`cellphone`,`status`)VALUES(insertid,name,name,cellphone,'active');
ELSEIF insertable = 5 THEN
INSERT DELAYED INTO member_5(`id`,`user_name`,`member_name`,`cellphone`,`status`)VALUES(insertid,name,name,cellphone,'active');
ELSEIF insertable = 6 THEN
INSERT DELAYED INTO member_6(`id`,`user_name`,`member_name`,`cellphone`,`status`)VALUES(insertid,name,name,cellphone,'active');
ELSEIF insertable = 7 THEN
INSERT DELAYED INTO member_7(`id`,`user_name`,`member_name`,`cellphone`,`status`)VALUES(insertid,name,name,cellphone,'active');
ELSEIF insertable = 8 THEN
INSERT DELAYED INTO member_8(`id`,`user_name`,`member_name`,`cellphone`,`status`)VALUES(insertid,name,name,cellphone,'active');
ELSEIF insertable = 9 THEN
INSERT DELAYED INTO member_9(`id`,`user_name`,`member_name`,`cellphone`,`status`)VALUES(insertid,name,name,cellphone,'active');
END IF;
ELSE
UPDATE member SET `user_name` = member_name,`member_name` = member_name,`cellphone` = cellphone WHERE `id` = insertid;
END IF;
SET done = 0;
IF result%100 = 0 THEN
COMMIT;
END IF;
end loop cursor_loop;
close rs_cursor;
#set autocommit=1;
set unique_checks=1;
set foreign_key_checks=1;
END
INSERT DELAYED 延迟插入把插入语句放到mysql后台执行,快速返回利用mysql存储过程循环更新会员
最新推荐文章于 2024-02-26 13:58:30 发布