CODEWITHSUNDEEP
mysql
Michał Jurczuk
Michał Jurczuk

Reputation: 3828

Limitting data in tables MySql

I have a lot of entries for some devices in one table, and I have to limit size for every device:

ENTIRES
-ID
-DEVICE_ID
-TIMESTAMP
-VALUE

I want to limit to e.g. 100 entries for each device. In mongo I would use capped collections. How can I do it in MySql?

I can periodically run count and delete queries, but maybe are there some better solutions?

Upvotes: 0

Views: 62

Answers (2)

Daniel Pradeep
Daniel Pradeep

Reputation: 31

You can use a before save trigger to remove entries from the table (if the count is > 100) before inserting the new row

Upvotes: 1

SkyDream
SkyDream

Reputation: 392

Count the number of entries with the DEVICE_ID

SELECT count(*) from ENTIRES where DEVICE_ID = <device_id>

Then each time you want to add an entry in the first table, check the corresponding count and if less than 100 do your insert

I read you can do conditional insert but i'm not sure it can be applied to your case

Upvotes: 1

Related Questions