如何用许多随机数填充 MySQL 表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11042546/
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
How do I populate a MySQL table with many random numbers?
提问by drug_user841417
I'm going to ask a question that has been asked in veryabstract terms, with (understandably) no concrete answers provided:
我要问一个用非常抽象的术语提出的问题，（可以理解）没有提供具体的答案：
From the MySQL prompt, how do I create and populate a table, rand_numbers, with one column, number INT, and 1111 rows, where the numbercolumn holds a random number between 2222 and 5555?
在 MySQL 提示中，我如何创建和填充一个表、rand_numbers、number INT、 和 1111 行，其中该number列包含 2222 和 5555 之间的随机数？
Something like:
就像是：
CREATE TABLE rand_numbers(number INT);
#run following line 1111 times
INSERT INTO rand_numbers (number) VALUES (2222 + CEIL( RAND() * 3333));
This question has been asked, but either relieson externallanguages for the loop or is far too general. I would like to know if it's possible to do something this simple from a typical Linux MySQL prompt.
这个问题已被问到，但要么依赖于循环的外部语言，要么过于笼统。我想知道是否可以从典型的 Linux MySQL 提示中执行如此简单的操作。
回答by Zane Bien
To create the table use:
要创建表，请使用：
CREATE TABLE rand_numbers (
number INT NOT NULL
) ENGINE = MYISAM;
Then to populate it with random values, you can define a stored procedure(which supports looping):
然后用随机值填充它，您可以定义一个存储过程（支持循环）：
DELIMITER $$
CREATE PROCEDURE InsertRand(IN NumRows INT, IN MinVal INT, IN MaxVal INT)
BEGIN
DECLARE i INT;
SET i = 1;
START TRANSACTION;
WHILE i <= NumRows DO
INSERT INTO rand_numbers VALUES (MinVal + CEIL(RAND() * (MaxVal - MinVal)));
SET i = i + 1;
END WHILE;
COMMIT;
END$$
DELIMITER ;
CALL InsertRand(1111, 2222, 5555);
Then you can reuse that procedure to insert more random values based on different parameters.. say 600 rows with random values between 1200 and 8500:
然后你可以重用这个过程来根据不同的参数插入更多的随机值..说 600 行，随机值在 1200 和 8500 之间：
CALL InsertRand(600, 1200, 8500);
回答by rodnaph
Without creating a stored procedure, one technique I've applied is to use the table itself to add the columns. First seed it with a value...
在不创建存储过程的情况下，我应用的一种技术是使用表本身来添加列。首先用一个值播种它......
INSERT INTO rand_numbers ( number ) VALUES ( rand() * 3333 );
Then insert again, selecting from this table to double the rows each time...
然后再次插入，从该表中选择每次将行加倍...
INSERT INTO rand_numbers ( number ) SELECT number * rand() FROM rand_numbers;
You don't need to run the second query that many times to get quite a few random rows. Not as "neat" as using a stored procedure of course, just proposing an alternative.
您不需要多次运行第二个查询来获得相当多的随机行。当然，不像使用存储过程那样“整洁”，只是提出了一个替代方案。
As pointed out by mohamed23gharbi, you can run into duplicates if your test mass is too large. You can use INSERT IGNOREto skip duplicates if that is a problem.
正如mohamed23gharbi所指出的，如果您的测试质量太大，您可能会遇到重复项。INSERT IGNORE如果这是一个问题，您可以使用跳过重复项。
回答by user4898949
The task can be done also this way:
任务也可以通过这种方式完成：
-- scale from 0 to MAX
UPDATE `table` SET `column` = 1000 * RAND() WHERE 1;
-- scale from MIN to MAX
UPDATE `table` SET `column` = MIN + (MAX - MIN) * RAND() WHERE 1;
You can also use math function like FLOOR(), CEIL(), etc. in the expression..
您还可以在表达式中使用数学函数，如 FLOOR()、CEIL() 等。
回答by Optimus
I have always used this -
我一直用这个 -
insert into rand_numbers ( number ) select rand() from (
select 0 as i
union select 1 union select 2 union select 3
union select 4 union select 5 union select 6
union select 7 union select 8 union select 9
) as t1, (
select 0 as i
union select 1 union select 2 union select 3
union select 4 union select 5 union select 6
union select 7 union select 8 union select 9
) as t2, (
select 0 as i
union select 1 union select 2 union select 3
union select 4 union select 5 union select 6
union select 7 union select 8 union select 9
) as t3;
Inserts 1000 random numbers. On-the-fly tables t1, t2, t3are cross joined so we get 10x10x10 rows.
插入 1000 个随机数。动态表t1, t2,t3是交叉连接的，因此我们得到 10x10x10 行。
So, for like a million rows, just add 3 more of
(select 0 as i union select 1 ...) asstatements. This seems convenient to me, since there's not much effort copy-pasting a few lines a bunch of times.
因此，对于一百万行，只需再添加 3 条
(select 0 as i union select 1 ...) as语句。这对我来说似乎很方便，因为多次复制粘贴几行代码并不费力。
Hope this helps,
希望这可以帮助，
回答by emmmphd
If you are lazy and you have the query for creating the table, try http://filldb.info//
如果您很懒惰并且有创建表的查询，请尝试http://filldb.info//
