创建一个触发器,在日志表中写入上次更新的时间等.
CREATE TABLE LOG (
id integer PRIMARY KEY AUTOINCREMENT,
tablename varchar NOT NULL,
action ENUM('insert','delete','update') NOT NULL,
action_time DATETIME NOT NULL)
DELIMITER $$
CREATE TRIGGER ai_table1 AFTER INSERT ON table1
BEGIN
INSERT INTO log (id, tablename, action, action_time)
VALUES (null, 'table1', 'insert', NOW())
END$$
CREATE TRIGGER ad_table1 AFTER DELETE ON table1
BEGIN
INSERT INTO log (id, tablename, action, action_time)
VALUES (null, 'table1', 'delete', NOW())
END$$
CREATE TRIGGER au_table1 AFTER UPDATE ON table1
BEGIN
INSERT INTO log (id, tablename, action, action_time)
VALUES (null, 'table1', 'update', NOW())
END$$
DELIMITER ;
请注意,触发器不会触发FOR EACH ROW,因为您只想要发生任何更改的日期,这会使触发器在执行多次更新/删除/插入时稍微加快(并且日志稍微缩短).