如何用许多随机数填充MySQL表? [英] How do I populate a MySQL table with many random numbers?

问题描述

我要问一个在中问过的问题非常抽象的术语，其中带有(可以理解)没有提供具体答案:

如何从MySQL提示符下创建并填充具有一列number INT和1111行的表rand_numbers，其中number列包含2222和5555之间的随机数?

类似的东西:

CREATE TABLE rand_numbers(number INT);

 #run following line 1111 times
INSERT INTO rand_numbers (number) VALUES (2222 + CEIL( RAND() * 3333));

有人问过这个问题，但是外部语言进行循环还是过于笼统.我想知道是否可以通过典型的Linux MySQL提示符来做这种简单的事情.

解决方案

要创建表，请使用:

CREATE TABLE rand_numbers (
    number INT NOT NULL
) ENGINE = MYISAM;

然后用随机值填充它，您可以定义一个存储过程(支持循环播放):

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);

然后，您可以重复使用该过程以根据不同的参数插入更多的随机值.例如说600行的随机值介于1200和8500之间:

CALL InsertRand(600, 1200, 8500);

I'm going to ask a question that has been asked in very abstract 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 number column holds a random number between 2222 and 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 relies on external languages 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.

解决方案

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:

CALL InsertRand(600, 1200, 8500);

这篇关于如何用许多随机数填充MySQL表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！