MYSQL 分表 动态SQL

CREATE DEFINER=`root`@`localhost` PROCEDURE `query_snatch_player_list`(IN `PARAM_PLAYER_ID` BIGINT, IN `PARAM_ITEMS` VARCHAR(200), IN `PARAM_ITEMS_LEN` INT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER

begin

declare v_item_id bigint default 0;
declare v_cnt int default 0;
declare v_is_compose_flag int default 0;
declare v_table_count int default 0;
declare v_table_num int default 10;
declare v_table_name varchar(50) default '';
declare v_match_num int default 0;
declare v_pre_item_size int default 0;


drop table if exists tmp_snatch_player_list;
create temporary table tmp_snatch_player_list(
`tmp_id` bigint unsigned not null auto_increment,
`item_id` bigint not null,
`player_id` bigint not null,
`playername` varchar(50) not null,
`playerlv` int not null,
`playerviplv` int not null,
`headpicid` int not null,
`carpicid` int not null,
`caroverallperformance` int not null,
`carname` varchar(50) not null,
`playercarid` bigint not null,
primary key (`tmp_id`)
)engine=myisam default charset=utf8;



set @v_player_id = param_player_id;

repeat
set v_cnt=v_cnt+1;
set v_pre_item_size = 0;
--  get item_id
select func_get_split_string(param_items,',',v_cnt) into v_item_id;
set @tmp_item_id = v_item_id;
-- judge is compose item
select count(1) into v_is_compose_flag from s_car_equipment_chip_rel where equipment_id = v_item_id ;
if(v_is_compose_flag>0) then
set @v_condition = concat('select  equipment_chip_id from s_car_equipment_chip_rel where equipment_id=',v_item_id);
else
set @v_condition = concat('select ',v_item_id);
end if;
-- fetch table
set v_table_count=0;
repeat
set @v_table_name = concat(' player_item_0',v_table_count);
set @stmt = concat('select count(1) into @v_match_num from player t1, player_attribute_race t2
where t1.id= t2.id and (timestampdiff(minute,t2.snatch_protect_time, now())>480 or t2.snatch_protect_time is null) and t1.user_id !=(
select p.user_id  from player p where p.id=',@v_player_id,' ) and (select ifnull(sum(t3.purchased_num),0) from ', @v_table_name ,
' t3 where t3.player_id = t1.id and t3.item_id in (item_id))>0 ');
prepare stmt1 from @stmt;
execute stmt1;
deallocate prepare stmt1;
set v_match_num = @v_match_num  ;
if (v_match_num>0)  then
set @insetsql=concat('insert into tmp_snatch_player_list (`item_id`,`player_id`,`playername`,`playerlv`,`playerviplv`,`headpicid`,`carpicid`,`caroverallperformance`,`carname`,`playercarid`)
select ',@tmp_item_id, ' as item_id, t1.id as player_id,t1.player_name as playername,t1.player_lv as playerlv,t1.vip_lv as playerviplv,t1.header_pic_id as headpicid,
t5.pic_id as carpicid, t4.overallperformance as  caroverallperformance,t5.name as carname, t4.id as playercarid
from player t1, player_attribute_race t2,player_car t4,s_car t5
where t1.id= t2.id and (timestampdiff(minute,t2.snatch_protect_time, now())>480 or t2.snatch_protect_time is null)
and t1.user_id !=(select p.user_id from player p where p.id=',@v_player_id,' ) and (select ifnull(sum(t3.purchased_num),0) from ', @v_table_name ,
' t3 where t3.player_id = t1.id and t1.id = t4.player_id and t4.race_status=1 and t4.car_id = t5.id
and t3.item_id in (',@v_condition,' ))>0 limit 30 ;');
prepare stmt2 from @insetsql;
execute stmt2;
deallocate prepare stmt2;
commit;
end if;
set v_table_count=v_table_count+1;
set v_pre_item_size=v_pre_item_size+v_match_num;

until v_pre_item_size>=30 or v_table_count>v_match_num

end repeat;

until v_cnt>=param_items_len

end repeat;

select * from tmp_snatch_player_list;
end

CREATE DEFINER=`root`@`%` FUNCTION `func_get_split_string`(`f_string` varchar(1000), `f_delimiter` varchar(5), `f_order` int)
RETURNS varchar(255)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
declare item_id bigint ;
set item_id = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));
return item_id;
END