- 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
- --该片段来自于http://www.codesnippet.cn/detail/110320132365.html
来源: http://www.codesnippet.cn/detail/110320132365.html