【编程技巧】SQL脚本快速生成随机测试数据

Oracle

create table TestTable as
  select lower(sys_guid()) AS guid,
         rownum as inc_id,
         trunc(dbms_random.value(0, 100)) as random_id,
         trunc(dbms_random.value()*power(10, 3), 8) as random_decimal1,
         trunc(dbms_random.value()*power(10, 8), 2) as random_decimal2,
         sysdate - 1 + rownum/24/3600 as inc_datetime,
         to_timestamp(systimestamp -1 + trunc(dbms_random.value(1, power(10, 8)))/24/3600)  as random_timestamp,
         substr( dbms_random.string('x', 100), 0, trunc(dbms_random.value(10, 100))) as random_string,
         to_clob(dbms_random.string('x', power(10, 6))) as random_clob
  from dual
     connect by level <= 1000000;

 

PostgreSQL

SELECT gen_random_uuid() guid
     , 'user_' || (power(10, 7) + rowIndex)  code
     , repeat('1',(random()*25)::integer) as name
     , rowIndex
     , (random()*100.)::numeric(4,2) random_decimal
     , TIMESTAMP '2022-01-01 00:00:00' + (TIMESTAMP '2022-12-31 23:59:59' - TIMESTAMP '2022-01-01 00:00:00') * random() AS random_timestamp
FROM generate_series(1, 1000000) rowIndex;

 

SQL Server & MySQL

select  floor(rand()*N)     --FLOOR 函数返回小于或等于所给数字表达式的最大整数。

select  ceiling(rand()*N)  --CEILING 函数返回大于或等于所给数字表达式的最小整数。

循环 或借助其他表进行查询插入

 

参考资料：

https://blog.csdn.net/lizhangyong1989/article/details/45013509 

https://blog.csdn.net/eagle89/article/details/116276102

https://www.cnblogs.com/terrence/p/4218741.html

https://www.jianshu.com/p/cf5d381ef637

https://www.cnblogs.com/CareySon/archive/2012/02/20/2359444.html

https://www.cnblogs.com/superfeeling/p/9199707.html

https://blog.csdn.net/mysqltop/article/details/105230327