7

Здравствуйте.

Подскажите, пожалуйста, как реализовать такой запрос. Структура таблицы:

USERNAME    |  DATETIME  |   IP_ADDRESS
user0
user0
user0
user1
user1
user1
user2
user2
user2
...
userN
userN
userN

CREATE TABLE log (
    USERNAME VARCHAR(50) NOT NULL,
    DATETIME DATETIME NOT NULL,
    IP_ADDRESS VARCHAR(15) NOT NULL
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

Мне нужно, чтобы для каждого пользователя в таблице было не больше 100 записей. Соответственно, если их больше, более ранние нужно удалить.

Вот такой запрос как-бы работает 

delete from `log` 
    where `USERNAME` = ? and `DATETIME` not in (select `DATETIME` from (select * from `log` order by `DATETIME` desc limit 100) s )

но я не могу повторять его для каждого USERNAME, т.к их несколько тысяч

Deleted
  • 371
mmp0sa
  • 171
  • Попробуйте так: 
    create temporary table log1 (USERNAME varchar(50), `DATETIME` DATETIME, number int);
insert into log1 (USERNAME, `DATETIME`, number) select USERNAME, `DATETIME`,
  if(l.USERNAME = @un, @rn:= @rn + 1, @rn := 1+least(0, @un := l.USERNAME)) as number
  from log l, (select @rn := 0, @un = '_') zz order by USERNAME, `DATETIME` desc;
delete log from log join log1 on log.USERNAME = log1.USERNAME and log.DATETIME = log1.DATETIME

    where log1.number > 100;

    Конечно, всё можно затолкать в один запрос, но выглядеть будет ужасно.

     – alexlz Dec 25 '13 at 17:21
  • Таблица на 350.000 записей. Через 3 часа запрос на удаление еще не завершился, сбросил. – mmp0sa Dec 26 '13 at 08:45
  • 4
    Пересмотрите структуры хранения- ведите для пользователя счетчик записей, если он равен 100, то добавляя новую запись удаляйте самую старую. – ReinRaus Dec 26 '13 at 09:36
  • А если индексы добавить? И, кстати, который запрос оказался таким длинным? – alexlz Dec 26 '13 at 10:05
  • 350 тыс не так уж и много, чтобы 3 часа выполняться. Скорее всего действительно не хватает индексов. В случае, если это надо сделать разово или с некоторой периодичностью (не в режиме реального времени), можно создать таблицу с аналогичной структурой, вставить в нее те данные, которые удалять не нужно, добавьте необходимые индексы, после чего дропните исходную таблицу и переименуйте новую в имя исходной. – vanchester Apr 03 '15 at 01:00

2 Answers2

2

Судя по вопросу автору необходимо производить такую чистку регулярно, поэтому посоветую создать хранимую процедуру для удаления логов. Внутри её делать удаление поэтапным, с минимумом удаляемых записей за запрос, в моем примере 100 записей.

CREATE PROCEDURE logclean()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE users VARCHAR(50);
  DECLARE count_rows, delete_rows INT;

  DECLARE del CURSOR FOR
  SELECT `USERNAME`, Count(*) - 100 FROM `log`
  GROUP BY `USERNAME`
  HAVING Count(*) > 100;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN del;

  read_loop: LOOP
    FETCH del INTO users, count_rows;

    IF done THEN
      LEAVE read_loop;
    END IF;

    SET delete_rows = 100;

    WHILE (count_rows > 0)
      DO
        IF delete_rows > count_rows THEN SET delete_rows = count_rows;
        END IF;

        DELETE FROM `log`
        WHERE `USERNAME` = users
        ORDER BY `DATETIME`
        LIMIT delete_rows;

        SET count_rows = count_rows - ROW_COUNT();
      END WHILE;

  END LOOP;

  CLOSE del;
END;

Вот пример на SQL Fiddle.

PS. Запрос приведенный в вопросе неправильный, так как во вложенном запросе не учитывается пользователь указанный в условии.

vikolyada
  • 1,409
0

Попробуйте это http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/.

Статья называется "How to select the first/least/max row per group in SQL"

Rjazhenka
  • 1,264
  • Включайте основные моменты из ссылки непосредственно в ответ. Ссылка может протухнуть, а выдержка останется. – andreycha Apr 07 '15 at 11:02