Блог

Я использую MySQL 5.1.36, и у меня есть база данных, используемая для веб-службы поддержки. В базе данных есть три таблицы, для которых я хотел бы отслеживать изменения:

 CREATE TABLE IF NOT EXISTS `tickets` (
  `TicketNum` int(11) unsigned NOT NULL,
  `SubmittedFromIP` tinyblob,
  `SubmittedFromDevice` varchar(255) DEFAULT NULL,
  `EntryDate` datetime DEFAULT NULL,
  `ClosedDate` datetime DEFAULT NULL,
  `LastName` varchar(50) DEFAULT NULL,
  `FirstName` varchar(50) DEFAULT NULL,
  `Email` varchar(50) DEFAULT NULL,
  `Location` varchar(4) DEFAULT NULL,
  `InventoryNumber` varchar(50) DEFAULT NULL,
  `DeviceName` varchar(50) DEFAULT NULL,
  `Description` text,
  `Notes` text,
  `Agent_ID` smallint(5) unsigned NOT NULL DEFAULT '1',
  `TotalHoursSpent` float NOT NULL DEFAULT '0',
  `Status` smallint(5) unsigned NOT NULL DEFAULT '1',
  `Priority` tinyint(4) NOT NULL DEFAULT '0',
  `LastUpdatedByAgent_ID` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`TicketNum`),
  KEY `ClosedDate` (`ClosedDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ticketsolutions` (
  `Entry_ID` int(10) unsigned NOT NULL,
  `TicketNum` mediumint(8) unsigned DEFAULT NULL,
  `EntryDateTime` datetime DEFAULT NULL,
  `HoursSpent` float DEFAULT NULL,
  `Agent_ID` smallint(5) unsigned DEFAULT NULL,
  `EntryText` text,
  `LastUpdatedByAgent_ID` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`Entry_ID`),
  KEY `TicketNum` (`TicketNum`),
  KEY `EntryDateTime` (`EntryDateTime`),
  KEY `HoursSpent` (`HoursSpent`),
  KEY `Rating` (`Rating`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `tickettagsmap` (
  `TicketNum` int(11) unsigned NOT NULL,
  `Tag_ID` int(10) unsigned NOT NULL,
  `AddedByAgent_ID` smallint(5) unsigned NOT NULL,
  `DateTimeAdded` datetime NOT NULL,
  PRIMARY KEY (`TicketNum`,`Tag_ID`),
  KEY `Tag_ID` (`Tag_ID`),
  KEY `fk_AgentID` (`AddedByAgent_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  

Исходя из того, что я прочитал, лучший способ справиться с этим — создать повторяющиеся таблицы, только с двумя дополнительными полями в таблице:

 ModifiedDateTime
Action
  

Действительно ли это лучший способ? Каждый раз, когда в запись вносится даже малейшее изменение, вся запись вставляется в соответствующую таблицу истории. Это кажется огромной тратой места. Есть ли лучший способ сделать это?

Один простой способ сделать это

1. Включите ведение журнала MySQL.
2. Проверьте наличие обновлений в журналах.

Включить ведение журнала в MySQL:

Введите следующее в серверной консоли mysql.

 SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';
  

Проверяйте наличие обновлений с помощью запросов: (вы можете настроить это в соответствии с вашими потребностями)

 select argument from mysql.general_log where argument REGEXP '*INSERT*';
  

Это может быть или не быть пустой тратой места, зависит от типичных операций с таблицами. Для INSERT и DELETE единственный способ отслеживать — это сохранять значения всех столбцов. Только для UPDATE того, чтобы вы могли сэкономить немного места. Вы можете создать 2 таблицы, например,

 update_history_main(id int not null auto_increment primary key,
modify_date datetime not null,
table_involved varchar(50) not null);

update_history_details (id int not null auto_increment primary key,
update_history_main_id int not null,
field_name varchar(100),
old_value varchar(100),
new_value varchar(100),
FOREIGN KEY (update_history_main_id) REFERENCES update_history_main(id)
ON UPDATE CASCADE ON DELETE CASCADE);
  

и добавляйте записи в эти таблицы после каждого обновления. Проблема здесь в том, что столбцы old_value и new_value должны быть достаточно большими, чтобы сохранить значение любого столбца из ваших исходных таблиц. Поэтому вам, вероятно, нужно создать другую, update_history_details_text_blobs которая отслеживает только изменения в текстовых столбцах / больших двоичных объектах.

Обновить. Таким образом, тело вашего триггера after update для tickets таблицы может выглядеть следующим образом

 DELIMITER $$$
 CREATE TRIGGER afterTicketUpdate AFTER UPDATE ON tickets
 FOR EACH ROW
 BEGIN
     DECLARE main_id int;
     INSERT INTO update_history_main(modify_date, table_involved) 
      VALUES(NOW(),'tickets';
     SELECT LAST_INSERT_ID() INTO main_id;

     IF (new.SubmittedFromDevice != old.SubmittedFromDevice) THEN
      INSERT INTO update_history_details(update_history_main_id, field_name, 
      old_value,new_value) 
     VALUES (main_id, 'SubmittedFromDevice',old.SubmittedFromDevice,
        new.SubmittedFromDevice);
     END IF; // ... check all other fields. 
 END
$$$
  

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

 TicketNum          
ModifiedDateTime   
Action
  

и обновляется триггером каждый раз по мере необходимости.