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

Question

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.

Answer 1

This will give you random numbers FLOOR(RAND()*(31-23+1))+23 And this will give you data in every 5 second date MOD(SECOND(curdate()) ,5)=0. You can use this sql -

SELECT
FLOOR(RAND()*(31-23+1))+23
FROM table
WHERE MOD(SECOND(curdate()) ,5)=0 

Answer 2

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.

Answer 3

Alternatively, you can use PHP file and using script timeInterval with AJAX to insert the random value to the database. I will tell you step by step but without code.

Steps:

1. create PHP function to connect your database (you can Googling with keysearch mysqli_connect)

2. create PHP function to handle your AJAX request and save it in database (keysearch: mysqli_query)

3. create script function (ex: named ajax_query) to send AJAX request with RAND function to generate random number as you wish. (You can read this question)

4. create script interval function to call function "ajax_query"

PS: don't forget to include/use jQuery library in your file