在日常的数据库开发和测试中,需要创建一些测试的表,并构造一下假的数据。这时就需要向表中插入随机数据,特别是插入大量随机数据以获取更好的验证。笔者在开发和应用中,也遇到了很多类似的问题,对于不同的数据库,其插入随机数据的方式是不一样的。当前比较流行的关系型数据库有甲骨文Oracle,微软的SQLServer,开源的MySQL和PostgreSQL。本文总结了这四种数据库插入大量/批量随机数据的SQL语句的方法。
笔者在测试中插入一千万行随机数据来做验证。如果数据库的列是主键值,那么要求数据的唯一性,否则是不需要唯一性的。
创建表t1,只有一列id,类型int,非主键。
create table t1(id int);
oracle数据库插入批量随机数据的代码:
begin
for i in 1..10000000 loop
insert into t1 values(dbms_random.value()*10000000);
--如果列是主键
--insert into t1 values(i);
end loop;
end;
/
SQLServer数据库插入批量随机数据的代码:
--这里使用事务进行插入,否则插入批量数据特别慢
begin tran;
declare @i int
set @i=1
while @i <=10000000
begin
insert into t1 values (rand()*10000000)
--如果是主键使用insert into t1 values (@i)
set @i=@i+1
end
commit;
go
MySQL数据库插入批量随机数据的代码:
--MySQL也是用函数来实现插入随机数据
delimiter $$
create function rand_num() returns int
begin
declare i int default 0;
set i = floor(rand()*10000000);
return i;
end
$$
delimiter $$
create procedure insert_t1(in start int,in max_num int)
begin
declare i int default 0;
set autocommit = 0;
repeat
set i=i+1;
insert into t1 (id) values(rand_num());
--如果是主键,使用insert into t1 (id) values(i);
until i = max_num
end repeat;
commit;
end $$
--调用函数插入一千万数据
delimiter ;
call insert_t1(1, 10000000);
PostgreSQL数据库插入批量随机数据的代码:
--PG使用PL/PGSQL函数插入数据
create or replace function insert_t1()
returns void as $$
DECLARE i int :=0;
BEGIN
WHILE i<10000000 LOOP
insert into t1 values((random()*10000000)::integer);
--同理,主键使用insert into t1 values(generate_series(1, 10000000))
i:=i+1;
END LOOP;
END;
$$ language plpgsql;
select insert_t1();
虽然都是插入一千万条随机数据,但是四个数据库的时间还是有很大差距的。基于测试,Oracle和SQLServer插入一千万行数据都超过5分钟,MySQL需要接近一分半,PG是最快的,23秒。
数据库 | oracle | sqlserver | MySQL | PostgreSQL |
---|---|---|---|---|
插入数据时间 | 5分36秒 | 6分13秒 | 1分39秒 | 23秒 |