MySQL - checking max rows during insert

Question

We have a table where we limit the number of rows per user to a certain number. Before insert, we check if user has exceeded the storage capacity (number of rows) and then insert as appropriate. For example,

select count(id) from items where user=123;

say, if count < 10,

insert into items set user=123, a=xyx;

However, this approach requires two queries. Is there a way in which is can be done in a single query.

Thanks

You can use an insert trigger to check that and automaitcally cancel the insert if necessary. But that are in reality also 2 queries — juergen d, May 02 '16 at 10:53

score 0 · Answer 1 · answered May 02 '16 at 10:56

Pretty much any approach you take will require two queries. Whether you do this in a trigger or in application code is a matter of choice.

If you use your method, then add an index on items(user). This will make it cheap to count the number of rows.

An alternative is to increment a value in the user table for each item. So, when a user inserts an item, then increment users.itemcount. Remember, though, to reduce the count when you delete items.

score 0 · Answer 2 · edited May 23 '17 at 12:31

0

If you just want to have 1 query in a code you can try to use conditional insert with approach described here MySQL Conditional Insert

edited May 23 '17 at 12:31

Community

1
1

answered May 02 '16 at 10:58

Stan

1,410
10
14

MySQL - checking max rows during insert

2 Answers2