分析2个mysql存储过程
1.产生一批按照标号结尾的表
delimiter //
drop procedure if exists genarate_app_user;
CREATE PROCEDURE `genarate_app_user`()
begin
set @i=1;
while @i<= 200 do
set @result= concat('drop TABLE if exists `app_user_',@i,'`;');
PREPARE sqlstr from @result;
Execute sqlstr;
set @result= concat('CREATE TABLE `app_user_',@i,'` (
`uid` bigint(20) NOT NULL DEFAULT ''0'' COMMENT ''用户id'',
`cell` varchar(18) DEFAULT NULL DEFAULT '''' COMMENT ''虚拟手机号'',
`province` varchar(16) DEFAULT NULL DEFAULT '''' COMMENT ''省份'',
`phone_type` varchar(10) DEFAULT NULL DEFAULT '''' COMMENT ''手机平台类型：SYMBIAN/ANDROID/IPHONE'',
`tag` varchar(100) NOT NULL DEFAULT '''' COMMENT ''用户的tag'',
primary key (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''app用户信息分表'';');
PREPARE sqlstr from @result;
Execute sqlstr;
set @i=@i+1;
end while;
end;
//
call genarate_app_user;
//
2.对批量表记录数进行汇总统计的过程
delimiter //
drop procedure if exists sumnum_app_user;
CREATE PROCEDURE `sumnum_app_user`()
begin
declare res int DEFAULT 0;
set @i=1;
while @i<= 200 do
set @result= concat('select coalesce(COUNT(1),0) into @COT from `app_user_',@i,'`;');
PREPARE sqlstr from @result;
Execute sqlstr;
set res := res + @COT;
set @i=@i+1;
end while;
select res;
end;
//