Search code examples
mysqlsqlfunctionprocedure

How to create random numbers at set time interval in mysql?

I want to insert random numbers between 23 and 31 at every 5 seconds in a Mysql table.

I know that I have to use RAND() function. But i don't know how to use it for inserting random numbers at set time interval.

Solution

  • You can use the event scheduler for this:

    create table foo (id int primary key auto_increment, value int);
create event insert_random_value_every_5_sec on schedule every 5 second do insert into foo (value) select floor(23+rand()*9) as value;

    If the event scheduler is disabled, you will need to enable it:

    set global event_scheduler=on;

    You can specify start and or end times for the event in the create event statement or later in alter event.