How to Populate a MySQL Table with Random Numbers Using Stored Procedures?

Populate MySQL Table with Random Numbers Using Stored Procedures

Problem Statement

Create a MySQL table named rand_numbers with a single column named number of type INT. Populate the table with 1111 random numbers between 2222 and 5555.

Solution

To create and populate the rand_numbers table using MySQL stored procedures, follow these steps:

1. Create the Table

<code class="sql">CREATE TABLE rand_numbers (
    number INT NOT NULL
) ENGINE = MYISAM;</code>

2. Create the Stored Procedure

Define a stored procedure called InsertRand that loops and inserts random values into the table:

<code class="sql">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 ;</code>

3. Call the Stored Procedure

Call the InsertRand procedure with the desired number of rows, minimum value, and maximum value:

<code class="sql">CALL InsertRand(1111, 2222, 5555);</code>

Example

To insert 600 random values between 1200 and 8500 into the rand_numbers table:

<code class="sql">CALL InsertRand(600, 1200, 8500);</code>

Reusing the Procedure

You can reuse the InsertRand procedure to generate random values for different ranges and tables by calling it with different parameters.

The above is the detailed content of How to Populate a MySQL Table with Random Numbers Using Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!