MYSQL查询，修改，删除分表后数据的存储过程

CREATE  PROCEDURE `cps`(in s varchar(4000) )
begin

	##########################################################################################
	#使用说明：
	#1、普通查询返回结果集的最后一个字段是分表的下标
	#2、普通查询不支持子查询
	##########################################################################################
	
	
	declare i int   default -1;	
	declare strsql varchar(4000) default '';	
	declare tablenmae varchar(128) default '';	
	declare tmpsql varchar(4000) default '';	
	declare opt varchar(6) default '';	
	
	set s = trim(s);
	set s = replace(s,'WHERE','where');
	set s = replace(s,'FROM','from');
	set s = replace(s,'SELECT','select');
	set s = replace(s,'UPDATE','update');
	set s = replace(s,'DELETE','delete');

	set opt = substr(s,1,6);
	
	#####分表个数，可以根据自己需求将循环的这个值作为参数传进来
	while i<9 do 
		set i=i+1;
		case opt
		when 'select'  then 
			##获取表名称
			set tablenmae = trim(substring_index(substring_index(s,'where',1),'from',-1));
			##获取表下标
			set tmpsql = if(trim(substring_index(s,'count(*)',1)) = 'select',s,replace(s,'*',concat('*,',i)));
			##合并分表
			set	strsql = concat(strsql,replace(tmpsql,tablenmae,concat(tablenmae,'_',i)),' union all \r\t');
			
		when 'update' then
				set tablenmae = trim(substring_index(substring_index(s,'set',1),'update',-1));
				set	strsql = concat(strsql,replace(s,tablenmae,concat(tablenmae,'_',i)),';\r');
			
		when 'delete' then
				set tablenmae = trim(substring_index(trim(substring_index(s,'where',1)),'from',-1));
				set	strsql = concat(strsql,replace(s,tablenmae,concat(tablenmae,'_',i)),';\r');
				
		end case;	
	end while;
	##替换最后 union 
	set strsql = substring_index(strsql,'union',i+1);
	if opt = 'select'  then 
		##外层包子查询
		set tmpsql = substring_index(strsql,'from',1);
		if  instr(tmpsql,'count' )>0 then		 
			set strsql = replace(strsql,tmpsql,concat(tmpsql,' as ct '));
			set strsql = concat('select sum(ct) from','(\r\t',strsql,')','a');
		else
			set strsql = concat('select * from','(\r\t',strsql,')','a');
		end if;
		set @sqll = strsql;
		prepare rt from @sqll;
		execute rt; 
	end if;
	set i = -1;
	select strsql;
end

说明：

result 1 为查询结果，result 2 为查询sql
使用样例：
1、call cps(‘select * from rep where sn =“1”’)
2、call cps('select count(*) from rep where sn =“1” ')
3、call cps('delete from rep where sn =“1” ')
4、call cps(‘update rep set sn = 1 where sn =“1”’)

不支持：
1、不支持子查询

交流：
754105453，有更好方式，请多多交流。