How do I populate a MySQL table with many random numbers?

by

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

More Related Contents:

  1. INSERT INTO SELECT statement with more columns which is not in another table
  2. Why does MYSQL higher LIMIT offset slow the query down?
  3. MySQL load NULL values from CSV data
  4. Hierarchical queries in MySQL
  5. #1071 – Specified key was too long; max key length is 1000 bytes
  6. JOIN queries vs multiple queries
  7. Calculate difference between two datetimes in MySQL
  8. What is the maximum length of data I can put in a BLOB column in MySQL?
  9. Why does MySQL allow “group by” queries WITHOUT aggregate functions?
  10. MySql Error 150 – Foreign keys
  11. MySql: MyISAM vs. Inno DB! [closed]
  12. SQLException: No suitable driver found [duplicate]
  13. Add Auto-Increment ID to existing table?
  14. MySQL 8.0.11 error connect to caching_sha2_password the specified module could not be found
  15. Using group by on two fields and count in SQL
  16. MySQL performance: multiple tables vs. index on single table and partitions
  17. How do I get the first day of the week of a date in mysql?
  18. Can MySQL convert a stored UTC time to local timezone?
  19. Returning a value even if no result
  20. How to force case sensitive table names?
  21. MySQL concat() to create column names to be used in a query?
  22. How to delete duplicate records in mysql database?
  23. mysql select sum group by date
  24. Is there a way to get the row number in Mysql like the rownum in oracle [duplicate]
  25. How to get a list of months between two dates in mysql
  26. My SQL Dynamic query execute and get ouput into a variable in stored procedure
  27. MySQL Integer vs DateTime index
  28. Order by FIELD in MYSQL
  29. How to check if field is null or empty in MySQL?
  30. How to escape apostrophe (a single quote) in MySql?

Leave a Comment