mysql random value change

Question

I have a database in mysql with 1 table composed by 5 fields. Two of these fields are FLOAT and generated by RAND function; now i want to change these values each x time, for example numeric values have to change every 0.01 s to simulate a financial market. Is there a method to do this thing? Thanks

Answer 1

Why do you need to change these values directly in table? It will be a huge load on database to update it every 0.01s. Maybe you can do it server-side, or maybe event with Javascript?

Answer 2

If you still want to do it directly inside the database, that could help : Schedule Tasks using Events on MySQL

Answer 3

Since it sounds like you're just testing, you could do this in a shell script. Something like this:

while [ true ] do 
  mysql -sse "update test.rand_table set rand_column = rand();"
  sleep 0.01
done

A stored proc would probably be more efficient since it could re-use the same connection:

DELIMITER $$

DROP PROCEDURE IF EXISTS test.UPDATE_RANDOM_TABLE $$

CREATE PROCEDURE test.UPDATE_RANDOM_TABLE ()
BEGIN
  WHILE TRUE
  DO
    update test.rand_Table set rand_column = rand();
    select sleep(0.01);
  END WHILE;
END $$

DELIMITER ;