How to leave no more than N last records of a certain type in a table?

Question

MySQL

Please tell me how, with little bloodshed, when inserting a row into a table, leave no more than N records of the same "type" (field value) in it.
For example,
ID | type | time | other
1 | 1 | 2020-01-01 | Bla-bla-bla
2 | 1 | 2020-01-02 | Bla-bla-bla
3 | 2 | 2020-01-02 | Bla-bla-bla
4 | 1 | 2020-01-03 | Bla-bla-bla

With N = 3 and insertion of record
5 | 1 | 2020-01-06 | Bla-bla-bla
entry
1 | 1 | 2020-01-01 | Bla-bla-bla
must go.

When inserting record
6 | 2 | 2020-01-07 | Bla-bla-bla
nothing is deleted, because records with type = 2 only 2.

When inserting record
7 | 1 | 2020-01-07 | Bla-bla-bla
record
2 | 1 | 2020-01-02 | Bla-bla-bla
must leave, etc.

For obvious reasons, it is impossible to make a trigger. It is advised to do a Mysql event, but with a large "traffic" of inserts, there will be times when there are more than N records. I don't want to bring this check into the application (that is, after insert, do select and then delete, all this in a transaction), but I feel that have to.

And a side question, MySQL can't delete from ... where ..order by .... limit OFFSET ? How to delete with one request?

Answer 1

And a side question, MySQL doesn't know how to delete from ... where ..order by .... limit OFFSET ? How to delete with one request?

and what hinders to make a subquery?

delete from ...
where id in (select id from ... where ... order by ... limit ...)

and already in brackets - any complexity of the condition