Удалить строки старше 14 дней в MySQL

Сводка

Мне нужно очистить историю таблицы от строк старше 14 дней.

Не будучи экспертом по MySQL, мои поиски привели меня к следующему:

delete
    from SYS_VERROUS_POLICE
    where idModificationPolice not in (
                select distinct idModificationPolice
                    from SYS_VERROUS_POLICE
                    where date(dateHeureModification) 
                            between curdate() and curdate() - interval 14 day
            );

Сгенерировано исключение

Но затем я застрял с этим сообщением об ошибке:

Код ошибки: 1093. Вы не можете указать целевую таблицу "SYS_VERROUS_POLICE" для обновления в предложении FROM.

Что за...

Контекст

MySQL, похоже, работает в безопасном режиме, поэтому я просто не смогу выполнить DELETE при совпадении дат.

В безопасном режиме, если я попытаюсь удалить, используя только поле даты, это не соответствует требованиям.

delete  
  from SYS_VERROUS_POLICE     
 where date(dateHeureModification) < curdate() - interval 14 day    

Error Code: 1175. You are using safe update mode and you tried to update a table without a 
WHERE that uses a KEY column
To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. 
0,00071 sec

Я что-то упускаю?


mysql delete-row safe-mode
person Will Marcouiller    schedule 13.11.2017    source источник
comment
не понимаю, почему вам нужно использовать NOT IN. вы должны просто выбрать правильные записи старше 14 дней с помощью ..SELECT * FROM SYS_VERROUS_POLICE WHERE DATE(dateHeureModification) < CURDATE() - INTERVAL 14 DAY   -  person Raymond Nijland    schedule 13.11.2017
comment
@RaymondNijland Да, я могу выбрать. Но на самом деле я не могу удалить записи, которые возвращает выбор.   -  person Will Marcouiller    schedule 13.11.2017

Ответы (3)


arrow_upward
3
arrow_downward

Я понимаю вашу точку зрения об использовании безопасного режима. Если вы попытаетесь использовать UPDATE или DELETE для неиндексированного выражения, оно выдаст ошибку, потому что не может оценить, не удалите ли вы случайно всю таблицу.

Использование выражения для DATE(dateHeureModification) > ... естественно не индексируется. MySQL не может выполнять поиск индекса по результату функции.

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

DELETE
    FROM SYS_VERROUS_POLICE
    WHERE DATE(dateHeureModification) < (curdate() - INTERVAL 14 DAY)
    LIMIT 1000;

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

Просто продолжайте выполнять DELETE в цикле, удаляя пакеты по 1000 строк за раз и проверяя затронутые строки после каждого пакета. Остановите цикл, когда затронутые строки достигнут 0.

Еще одна идея: я не думаю, что вам действительно нужна функция DATE() в предложении WHERE. Таким образом, вы можете выполнить DELETE, как показано ниже, и он сможет использовать индекс. Кроме того, запрос должен быстрее проверять любые строки, если у вас есть индекс для dateHeureModification.

DELETE
    FROM SYS_VERROUS_POLICE
    WHERE dateHeureModification < (curdate() - INTERVAL 14 DAY)
    LIMIT 1000;
person Bill Karwin    schedule 13.11.2017
comment
а как отключить настройки? SET SQL_SAFE_UPDATES = 0; ?? - person Juan Carlos Oropeza; 13.11.2017
comment
Это зависит от вас. Если у вас есть контроль над глобальными настройками, вы можете делать все, что хотите — тем самым вы принимаете на себя риск случайного удаления всех ваших данных. Но по-прежнему рекомендуется выполнять удаление пакетами по причинам, описанным выше. И все же неплохо сделать поиск индексируемым, чтобы повысить производительность. - person Bill Karwin; 13.11.2017
comment
Эта таблица предназначена только для помощи в устранении неполадок, связанных с изменениями в основных объектах. Это таблица истории, которую требуется очищать каждый день от записей старше 14 дней. Таким образом, случайное удаление маловероятно, и если это произойдет, мы проведем расследование. Это не должно иметь большого значения. - person Will Marcouiller; 14.11.2017

arrow_upward
3
arrow_downward

Я не понимаю, почему вы усложняете это:

delete
    from SYS_VERROUS_POLICE
    where date(dateHeureModification)
                           <(curdate() - interval 14 day);
person Daniel E.    schedule 13.11.2017
comment
Потому что MySQL находится в безопасном режиме, и я не могу удалить только с неключевым полем. - person Will Marcouiller; 13.11.2017

arrow_upward
0
arrow_downward

@Жан Доус - правильный ответ. Но просто объяснить, в чем проблема в вашем запросе. Вы пытаетесь проверить условие для той же таблицы, которую вы обновляете, и это похоже на создание циклической ссылки.

Вместо этого вы материализуете таблицу как подзапрос, чтобы вы могли ее использовать.

delete
from SYS_VERROUS_POLICE
where idModificationPolice not in (
              select distinct T.idModificationPolice
              from (SELECT *
                    FROM SYS_VERROUS_POLICE) as T
              where date(T.dateHeureModification) 
                    between curdate() and curdate() - interval 14 day
         );
person Juan Carlos Oropeza    schedule 13.11.2017
comment
Я столкнулся с той же проблемой, сказав, что в безопасном режиме я не могу удалить, используя неключевой столбец. :\ - person Will Marcouiller; 13.11.2017
comment
Но это совершенно другая ошибка, чем та, которую вы публикуете в вопросе. Возможно, вы могли бы проверить здесь stackoverflow.com/questions/11448068/ - person Juan Carlos Oropeza; 13.11.2017