Limit the insertions to MySQL table to N rows

Question

I have created a table in MySQL.

I want this table store only 30 rows and when exceeding 30 rows let us say is 31 rows then the table has to delete the old 30.

Is this possible in MySQL?

I have used the following trigger but it does not work with me.

CREATE TRIGGER my_name
BEFORE INSERT ON try
FOR EACH ROW
BEGIN
    DECLARE cnt INT;

    SELECT count(*) INTO cnt FROM try;

    IF cnt = 30 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You can store only 10 records.';
    END IF;
END;

You can check this issue: [How can I set a maximum number of rows in MySQL table?](https://stackoverflow.com/questions/8048001/how-can-i-set-a-maximum-number-of-rows-in-mysql-table) — R. García, Aug 20 '18 at 09:10
0. After creating a timestamp column.., 1. Insert 30 rows. 2. Disallow insert. 3. And use update instead — Strawberry, Aug 20 '18 at 09:11
OP, please add your queries. No one can ever debug this for you. — Ronnie Oosting, Aug 20 '18 at 09:12
Your code works fine for me maybe you are just not setting delimiters. And what happened to the delete part of the question? — P.Salmon, Aug 20 '18 at 09:21
@ P.Salmon, How it works with you,?? for delete part I have no idea how I can do it. Cloud you help me to make the code insert only 30 row then delete them when exceeding 30 and add new. — laya, Aug 20 '18 at 09:43
There is no easy solution to this in mysql - a way would be to do the inserts and create an event (or cron job) to periodically delete unwanted records. — P.Salmon, Aug 20 '18 at 10:00
Why would you want the error message to say "You can store only 10 records"? In fact, why would you want any error message at all? Surely the system would just silently update the oldest row. — Strawberry, Aug 20 '18 at 10:22
@ Strawberry, I don't want any an error message, I had added it to the code only for testing !!!! — laya, Aug 20 '18 at 10:42

score 0 · Answer 1 · answered Aug 20 '18 at 15:08

0

In the following screen show the result of the SHOW PROCEDURE STATUS

screen1

answered Aug 20 '18 at 15:08

laya

5
7

Tunker · Accepted Answer · 2018-08-20T12:21:58.473

You can use stored procedure instead of regular INSERT statement. The stored procedure will first add the row based on the input parameters and then delete old rows if required. Please see below example. You will probably have to adjust the input parameters for limitedInsert procedure based on your table's columns.

My example table schema

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `value` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Procedure

DELIMITER //
CREATE DEFINER=`root`@`%` PROCEDURE `limitedInsert`(
    IN `parameter_1` INT
)
BEGIN
    DECLARE rowsNumber INT;
    DECLARE idToRemove INT;

    insert into test(value) values(parameter_1);
    select count(*) into rowsNumber from test;

    if rowsNumber > 30 then
        select id into idToRemove from test order by timestamp asc limit 1;
        delete from test where id = idToRemove;
    end if;
END//
DELIMITER ;

Insert data using stored procedure

call limitedInsert(2);

Limit the insertions to MySQL table to N rows

2 Answers2