Как установить максимальное количество строк в таблице?

Question

[yonshou]

Как установить максимальное количество строк по такому правилу: при достижении 1000 строк, удалить последнюю строку (по дате) что бы записать свежую.

Comments

[Danny Arty]

Нужно копать в сторону триггеров. Вот например статья на Хабре

[Дмитрий]

Удалять по крону лишнее раз в сутки или во сколько надо

[Akina]

Вариант 1 - замена INSERT на UPDATE самой старой записи.
Вариант 2 - хранимая процедура вместо запроса.

Второй метод предпочтителен, но становятся проблемой пакетные вставки.

Danny Arty, какие триггеры? он хочет удалять из таблицы, в которую вставляет - в MySQL в триггере это недопустимо.

[yonshou]

Дмитрий, при достижении 1000-ой строки

[Дмитрий]

yonshou, ну вы в запросах выборки ставите limit 1000 тем самым гарантировав что больше не выберите. А по крону раз в какое то время удаляете лишнее. В результате будет работать ровно как вы хотите.

[Danny Arty]

Akina, да, плохо вчитался в вопрос. Триггер же не сможет изменять записи таблицы, к которой он сам привязан.

[Danny Arty]

Дмитрий, Можно и без крона. С 5.1 есть свой планировщик заданий

[Дмитрий]

Danny Arty, крон общее название для планировщиков - где конкретно вопрос второй.

[ThunderCat]

Чисто ради интереса: нафига, а главное - зачем?

Answer 1

[meusov11]

Триггер использовать надо. Пример:

CREATE TRIGGER limit_rows_trigger
AFTER INSERT ON your_table
FOR EACH ROW
BEGIN
    DECLARE row_count INT;
    
    SELECT COUNT(*) INTO row_count FROM your_table;
    
    IF row_count > 500 THEN
        DELETE FROM your_table
        ORDER BY created_at
        LIMIT 1;
    END IF;
END//

Comments

[Akina]

Ты на тег посмотри! MySQL. Грохнется твой триггер, с ошибкой 1442.

Answer 2

[mayton2019]

По постановке это похоже на буфер очереди сообщений. Вам нет смысла синхронно удалять хвостовые строки.с
Пускай это делает отдельный джоб раз в сутки например. Вам - же не принципиально если пол дня табличка будет в 1001 строку?

А консистентный снимок top 1000 строк вы всегда можете получить запросом с сортировкой и лимитом. И это будет решение в духе баз данных а не Java-буферов с блокировками.

Comments

[Akina]

А консистентный снимок top 1000 строк вы всегда можете получить запросом с сортировкой и лимитом.

Угу. Можно даже вьюшку навернуть, чтобы не думалось.

Answer 3

[Akina]

Демонстрация метода замены вставки на обновление: fiddle.