Avoid Duplicate data for specific time Interval

Question

I have a table which gets new data every few seconds. Consider my table PRODUCT:

+----+-------------+--------+--------------------------------+-------+--------+---------------------+
| id | business_id | name   | description                    | link  | status | created_at          |
+----+-------------+--------+--------------------------------+-------+--------+---------------------+
| 1  | 12          | qwerty | Description for product qwerty | zxcvb | 1      | 2015-12-07 23:49:33 |
+----+-------------+--------+--------------------------------+-------+--------+---------------------+
| 2  | 12          | abcde  | Description for product abcde  | mnopq | 0      | 2015-12-07 23:49:33 |
+----+-------------+--------+--------------------------------+-------+--------+---------------------+

Values in columns name and description are unique (I am not sure this is right).

The condition I want: if new data is the same as last inserted data but time stamp is greater than 5 min then perform update or else insert new row in table. Even though name and description are unique.

Query I have tried:

INSERT INTO product(business_id, name, description, link) 
VALUES ('$business_id' ,'$product_name','$product_description', '$short')
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)

Which condition should I change? Should I remove unique keys?

score 1 · Accepted Answer · edited May 23 '17 at 11:45

You want to insert duplicate name and description values when their time stamp is not more than 5 minutes later than that of the last inserted record.

With this requirement you effectively say that name and description are not unique. So, indeed, you will have to drop the unique key/index on those fields to make this possible.

Secondly, you assume that if the new values already exist in the table, that it must be by the last inserted record, but there seems to be no guarantee for that. You should also deal with the possibility that first values A are inserted, then values B one minute later, and then values A again after 10 minutes.

In the logic you presented, that last operation would be detected as duplicate, and would be translated into an update of the last inserted record. But that was not the record with values A. In fact, the ON DUPLICATE clause you suggest would produce in that case a duplicate error of its own (duplicate on id).

So here is what I would suggest to do:

Drop the UNIQUE key/index on name and description, but you could still benefit from using non-unique indexes;

Use the following INSERT statement:

INSERT INTO product(business_id, name, description, link) 
SELECT :business_id, :name, :description, :link
FROM   product
WHERE  NOT (    name = :name 
            AND description = :description 
            AND created_on < DATE_ADD(NOW(), INTERVAL -5 MINUTE)
       )

The : denote place holders for a arguments in a prepared statement. You should not insert strings inside SQL statements like you did, because you are then vulnerable to SQL injection. Read about converting your code to use prepared statements.

The above INSERT statement will not do anything if the name and description value combination has already been registered before, and that happened more than 5 minutes ago.

For that case you could test in PHP that no record was inserted with the num_rows method. If that returns 0, then you would perform a second SQL to perform the update:

UPDATE product
SET    link = :link
WHERE  name = :name 
   AND description = :description 
   AND created_on < DATE_ADD(NOW(), INTERVAL -5 MINUTE)

Again, you should do this with prepared statements.

score 0 · Answer 2 · answered Mar 19 '16 at 07:02

0

If you use description as unique then you cannot insert new record with same description. Unique should use at "id", so yes you should remove unique on description.

And you can use if clause to check if business_id if same and create_at greater than 5 minutes then update or insert new row.

answered Mar 19 '16 at 07:02

Welly Tunggala

50
8

id must be primary, so by definition unique. Name can certainly be unique, but it seems unlikely or unnecessary that description would be - though not inconceivable. There's no need for an if clause here. In fact, it would be counterproductive. – Strawberry Mar 19 '16 at 07:53

Avoid Duplicate data for specific time Interval

2 Answers2