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

时间:2023-03-09 08:28:39
【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 >= '')-
(SELECT MIN(userId) FROM users WHERE userId<= '')
) * RAND() + (SELECT MIN(userId) FROM users WHERE userId <= 65)
LIMIT 5 那么上面的随机取五条可以换算成 SELECT * FROM users
WHERE userId >= (5000-1)*rand()+1
limit 5