过程执行后会随机从表中筛选指定数目的记录返回。过程包含三个参数，分别是表名，行数和主键id。过程只支持int类型单主键的表。主键可以省略为空，这样过程会自动查找主键，不过这样比较慢。 mysql create definer=`root`@`%` procedure `rand_data`(in `tbn
过程执行后会随机从表中筛选指定数目的记录返回。过程包含三个参数，分别是表名，行数和主键id。过程只支持int类型单主键的表。主键可以省略为空，这样过程会自动查找主键，不过这样比较慢。 mysql create definer=`root`@`%` procedure `rand_data`(in `tbname` varchar(50), in `rowcnt` int, in `tbkey` varchar(50)) language sql not deterministic contains sql sql security definer comment '随机获取若干记录,只适用于单主键表'begin #获取主键名 if tbkey is not null then set @tbkey=tbkey;#参数里面已经有，这种情况比较快 else #参数里面没，从系统表查找主键，比较耗时 select @tbkey:=c.column_name from information_schema.table_constraints as t, information_schema.key_column_usage as c where t.table_name = c.table_name and t.table_schema = c.table_schema and t.table_schema = database() and t.table_name = tbname and t.constraint_type = 'primary key'; end if; #获取最大id,最小id和记录数 set @getmaxidsql = concat('select @maxid:=max(', @tbkey, '),', '@minid:=min(', @tbkey, '),', '@totalcnt:=count(', @tbkey, ')', ' from `', tbname, '`;'); prepare getmaxid from @getmaxidsql; execute getmaxid; deallocate prepare getmaxid; #创建临时表 drop table if exists rand_tt; set @temtbsql = concat('create temporary table rand_tt select 0 aid,tb.* from `',tbname,'` tb limit 0;'); prepare temtb from @temtbsql; execute temtb; deallocate prepare temtb; #构建获取一条记录的sql set @randrowsql = concat('insert into rand_tt select @cnt:=@cnt+1 aid,tb.* from ', tbname, ' tb where tb.', @tbkey, '=?;'); prepare addrow from @randrowsql; #生成随机记录 set @cnt=0; insertloop: loop set @id=floor(rand()*(@maxid-@minid)+@minid); if not exists (select id from rand_tt where id=@id) then execute addrow using @id; if @cnt >= rowcnt or @cnt >= @totalcnt then leave insertloop; end if; end if; end loop insertloop; deallocate prepare addrow; #返回数据 alter table rand_tt drop column aid; select * from rand_tt;end