如何用许多随机数填充 MySQL 表?

2021-11-20 00:00:00 mysql

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

I'm going to ask a question that has been asked in very abstract terms, with (understandably) no concrete answers provided:

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

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?

类似于:

CREATE TABLE rand_numbers(number INT);

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

这个问题已经有人问过了，但是依赖循环的外部语言或者过于笼统.我想知道是否可以从典型的 Linux MySQL 提示中执行如此简单的操作.

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.

推荐答案

创建表格使用:

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

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

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

相关文章