Re: Insert on time difference greater than
Posted by: Rick James ()
Date: May 24, 2016 10:37AM
... BEGIN IF NOT EXISTS ( SELECT * FROM code_reader WHERE tag_number = _tag_number AND time > NOW() - INTERVAL 10 SECOND ) THEN INSERT ...; END; END // and have INDEX(tag_number, time)`time` had better be DATETIME or TIMESTAMP, _not_ TIME if you have data spanning multiple days.
The index allows the NOT EXISTS(SELECT...) to be very efficient.
If you have two connections 'simultaneously' calling this STORED PROCEDURE, it is possibly that both will insert, and the times will be within 10 seconds. If this can be the case, we need to discuss "transactions". It may be as simple as adding FOR UPDATE to the SELECT and wrapping the code in START TRANSACTION and COMMIT.
|Insert on time difference greater than||2043||Manny B||05/17/2016 11:21AM|
|Re: Insert on time difference greater than||402||Peter Brawley||05/17/2016 11:28AM|
|Re: Insert on time difference greater than||346||Manny B||05/17/2016 11:44AM|
|Re: Insert on time difference greater than||338||Peter Brawley||05/17/2016 12:44PM|
|Re: Insert on time difference greater than||368||Rick James||05/24/2016 10:37AM|
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.