MySQL Forums
Forum List  »  Stored Procedures

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Insert on time difference greater than
407
May 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.