1

I have a system that involves an ajax post request that inserts a row into a table. It seems that sometimes the ajax call is called twice, meaning that the same data is inserted twice in two adjacent rows.

I'm planning to add a query first to try and select a row with the same data, then if the number of rows = 0 add it. Is there a better way that avoids having to do two queries?

Rich Bradshaw
  • 67,265
  • 44
  • 170
  • 236
  • This has been previously discussed, see http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql – cfedermann Apr 14 '12 at 14:37
  • Consider an alternative approach that addresses the root problem on the page that is causing the AJAX call to happen twice rather than writing defensive SQL queries. An "IsDirty" flag on the web page perhaps? – Bob Kaufman Apr 14 '12 at 14:37
  • 2
    Don't you have a primary key or other unique constraint on your tables to ensure that you do not get duplicate data in your DB? You should. And the ID column isn't sufficient; it is convenient and autogenerated, but you should have some other combination of attributes that are unique. Then the attempt to insert the duplicate data fails. There must be a way to tell that you are inserting the 'same row twice'; otherwise, you couldn't be asking the question. – Jonathan Leffler Apr 14 '12 at 15:00
  • Jonathan, so, making a checksum row somehow? I considered hashing all the columns other than the auto primary key to deal with this - is that what you are suggesting? – Rich Bradshaw Apr 15 '12 at 09:08

2 Answers2

3

You can use the INSERT IGNORE INTO syntax or INSERT...ON DUPLICATE KEY UPDATE on your insert statement.

If you use INSERT IGNORE, then the row won't actually be inserted if it results in a duplicate key. But the statement won't generate an error. It generates a warning instead.

INSERT IGNORE INTO mytable
    (primaryKey, field1, field2)
VALUES
    ('abc', 1, 2);
Steve Stedman
  • 2,462
  • 3
  • 19
  • 21
0

I'd say the best way to deal with this, is to use a temporary token in your application (which can double as a CSRF token, which you probably need anyway..).

If the token is sent along with the operation, expire the token so it cannot be repeated.

Evert
  • 75,014
  • 17
  • 95
  • 156