Как "откатить" мой контент до определенной даты, повторить обновление или удалить или вставить

Question

Я поддерживаю здесь базу данных MySQL (примерно 100 таблиц, множество взаимосвязей и т. Д.) (Версия: 5.7). Мне интересно, могу ли я добавить таблицу и т. Д. (Таблицы аудита и / или таблицы истории или добавить столбцы в исходные таблицы), чтобы мне было легче поддерживать эту базу данных в будущем ( например, если пользователь 'joe' вставлял / обновлял и удалял вещи, с которыми он не должен был связываться, тогда я мог бы 'откатить' только его отношения с базой данных). Есть ли для этого хороший образец? Или эта функциональность уже встроена в Enterprise-версию, например, MySQL 5.7 и MySQL 8.x?

Я нашел такие статьи (1) https://kahimyang.com/kauswagan/code-blogs/552/create-history-record-for-every-change-in-mysql-table-audit-trail-mysql- триггер и этот (2) https://blog.programster.org/mysql-history-tables при поиске. Не могли бы вы предложить или указать мне на учебник или технический документ MySQL о том, как это сделать? Или вы можете порекомендовать хорошую книгу на эту тему?

Answer 1

Существует несколько различных подходов к решению этой сложной проблемы. Его часто называют восстановлением на определенный момент времени, а не откатом, потому что ROLLBACK означает в SQL нечто иное.

Первый: добавьте столбцы и в свои таблицы вместе с другими идентифицирующими элементами ( user_idНаверное). Затем, вместо того, чтобы просто выполнить операцию ОБНОВЛЕНИЕ, сначала ОБНОВИТЕ end_dateсуществующей строки в NOW() и ВСТАВИТЬ новое значение с start_dateNOW() и NULL end_date.

При поиске предметов сделайте это, чтобы получить их текущее значение.

       SELECT col, col, col 
   FROM tbl
  WHERE start_date <= NOW()
    AND (end_date > NOW() OR end_date IS NULL)
    AND whateverElseYouNeed

При поиске товаров на определенную дату сделайте следующее:

       SELECT col, col, col 
   FROM tbl
  WHERE start_date <= @date
    AND (end_date > @date OR end_date IS NULL)
    AND whateverElseYouNeed

Затем вы можете написать код для поиска старых значений и обработки их как новых значений. Индексирование этих таблиц, чтобы поиск по-прежнему был эффективен, когда они становятся действительно большими, — это боль в шее xxs.

Второе: создайте таблицу истории, соответствующую каждой таблице, но также включающую change_dateстолбец. Напишите триггеры BEFORE для INSERT, UPDATE и DELETE, чтобы вставлять строку в таблицу истории для каждого изменения в основной таблице. Затем напишите код для просмотра таблицы истории, когда вам нужно что-то восстановить.

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

Третье : MariaDB, начиная с версии 10.3.4, предлагает таблицы с системной версией для обработки истории на сервере базы данных. (Я сам не пробовал эту функцию.)

Профессор Ричард Т. Снодграсс написал книгу «Разработка приложений баз данных, ориентированных на время, на языке SQL» и любезно разместил книгу в формате pdf .