SQL 用随机数据填充表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3371503/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Populate table with random data
提问by no9
I have a table with two fields:
我有一个包含两个字段的表：
- id(UUID) that is primary Key and
- description (
var255)
- id(UUID) 是主键和
- 说明 (
var255)
I want to insert random data with SQL sentence. I would like that description would be something random.
我想用 SQL 语句插入随机数据。我希望这种描述是随机的。
PS: I am using PostgreSQL.
PS：我正在使用 PostgreSQL。
回答by leonbloy
I dont know exactly if this fits the requirement for a "random description", and it's not clear if you want to generate the full data: but, for example, this generates 10 records with consecutive ids and random texts:
我不知道这是否符合“随机描述”的要求，也不清楚是否要生成完整数据：但是，例如，这会生成 10 条具有连续 ID 和随机文本的记录：
test=# SELECT generate_series(1,10) AS id, md5(random()::text) AS descr;
id | descr
----+----------------------------------
1 | 65c141ee1fdeb269d2e393cb1d3e1c09
2 | 269638b9061149e9228d1b2718cb035e
3 | 020bce01ba6a6623702c4da1bc6d556e
4 | 18fad4813efe3dcdb388d7d8c4b6d3b4
5 | a7859b3bcf7ff11f921ceef58dc1e5b5
6 | 63691d4a20f7f23843503349c32aa08c
7 | ca317278d40f2f3ac81224f6996d1c57
8 | bb4a284e1c53775a02ebd6ec91bbb847
9 | b444b5ea7966cd76174a618ec0bb9901
10 | 800495c53976f60641fb4d486be61dc6
(10 rows)
回答by Maksim Dmitriev
The following worked for me:
以下对我有用：
create table t_random as select s, md5(random()::text) from generate_Series(1,5) s;
回答by 3manuek
Here it is a more elegant way using the latest features. I will use the Unix dictionary (/usr/share/dict/words) and copy it into my PostgreSQL data:
这是使用最新功能的更优雅的方式。我将使用 Unix 字典 (/usr/share/dict/words) 并将其复制到我的 PostgreSQL 数据中：
cp /usr/share/dict/words data/pg95/words.list
Then, you can easily create a ton of no sense description BUT searchable using dictionary words with the following steps:
然后，您可以通过以下步骤轻松创建大量无意义的描述，但可以使用字典单词进行搜索：
1) Create table and function. getNArrayS gets all the elements in an array and teh number of times it needs to concatenate.
1) 创建表和函数。getNArrayS 获取数组中的所有元素以及它需要连接的次数。
CREATE TABLE randomTable(id serial PRIMARY KEY, description text);
CREATE OR REPLACE FUNCTION getNArrayS(el text[], count int) RETURNS text AS $$
SELECT string_agg(el[random()*(array_length(el,1)-1)+1], ' ') FROM generate_series(1,count) g(i)
$$
VOLATILE
LANGUAGE SQL;
Once you have all in place, run the insert using CTE:
一切就绪后，使用 CTE 运行插入：
WITH t(ray) AS(
SELECT (string_to_array(pg_read_file('words.list')::text,E'\n'))
)
INSERT INTO randomTable(description)
SELECT getNArrayS(T.ray, 3) FROM T, generate_series(1,10000);
And now, select as usual:
现在，像往常一样选择：
postgres=# select * from randomtable limit 3;
id | description
----+---------------------------------------------
1 | ultracentenarian splenodiagnosis manurially
2 | insequent monopolarity funipendulous
3 | ruminate geodic unconcludable
(3 rows)
回答by Kuberchaun
I assume sentance == statement? You could use perl or plperl as perl has some good random data generators. Check out perl CPAN module Data::Random to start.
我假设句子 == 声明？您可以使用 perl 或 plperl，因为 perl 有一些很好的随机数据生成器。查看 perl CPAN 模块 Data::Random 开始。
Here's a sample of a perl script to generate some different random stuff taken from CPAN.
这是一个 perl 脚本示例，用于从CPAN生成一些不同的随机内容。
use Data::Random qw(:all);
my @random_words = rand_words( size => 10 );
my @random_chars = rand_chars( set => 'all', min => 5, max => 8 );
my @random_set = rand_set( set => \@set, size => 5 );
my $random_enum = rand_enum( set => \@set );
my $random_date = rand_date();
my $random_time = rand_time();
my $random_datetime = rand_datetime();
open(FILE, ">rand_image.png") or die $!;
binmode(FILE);
print FILE rand_image( bgcolor => [0, 0, 0] );
close(FILE);
