【mysql】关于循环插入数据 存储设计

要求插入的数据有一定的规律

新建实例列表
CREATE TABLE    users
(
  userId                    INT(11) NOT NULL,
 userName               VARCHAR(255)  NOT NULL,
Serves                  INT(11)  NOT NULL,
PRIMARY    KEY   (userId)
);

创建存储过程 例如：随机写入5000条数据

begin 
  declare i int default 1;
  start transaction;
   while i<=5000 do
    insert into users(userId,userName,Serves) values(i,concat('z00',i),123); 
    set i=i+1; 
   end while; 
  commit;
 end

 

设置随机函数取前几条

select * from users order by rand() LIMIT 3 ;

　　

users表刚才插入5000条数据

SELECT * FROM users
WHERE userId >= (
(SELECT MAX(userId) FROM users WHERE userId >= '10')-
(SELECT MIN(userId) FROM users WHERE userId<= '22')
) * RAND() + (SELECT MIN(userId) FROM users  WHERE userId <= 65) 
 LIMIT 5

那么上面的随机取五条可以换算成

SELECT * FROM users
WHERE userId >= (5000-1)*rand()+1
limit 5