MySQL и автоматическое обновление таймстампов

Несмотря на то, что сейчас такие вещи, как последнее обновление записи, модно делать с помощью ORM, — в том случае, если вы можете быть платформенно-зависимы (всю жизнь с MySQL, или готовы переписать триггеры в случае перехода на другую БД вроде PostgreSQL), возможно, есть смысл воспользоваться триггерами.

MySQL позволяет сделать один автообновляемый таймстамп, например таким образом:

CREATE TABLE games (name VARCHAR(64), created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);

Либо на обновление:

CREATE TABLE games (name VARCHAR(64), updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

А вот совместить их нельзя. Зато можно сделать так:

CREATE TABLE games (name VARCHAR(64), created TIMESTAMP NOT NULL DEFAULT ‘0000-00-00’, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

Благодаря полубаге/полуфиче, можно теперь сделать так:

INSERT INTO games(name, created) VALUES (‘123’, NULL);

Таким образом в таблицу будет записано текущее значение. Подробности — в документации MySQL.

Вместе с тем, мне больше по душе другой вариант, реализация через триггеры. Поле created — используем как приведено раньше, а вот полю updated устанавливаем дефолтное значение в 0 или ‘0000-00-00’.

CREATE TABLE games (name VARCHAR(64), 
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
updated TIMESTAMP NOT NULL DEFAULT 0);
DELIMITER ;;
DROP TRIGGER IF EXISTS games_update;;
DROP TRIGGER IF EXISTS games_create;;
CREATE TRIGGER games_update BEFORE UPDATE ON games FOR EACH ROW 
BEGIN 
  SET NEW.updated = NOW(); 
END 
;;
CREATE TRIGGER games_create BEFORE INSERT ON games FOR EACH ROW 
BEGIN 
  IF NEW.updated = 0 THEN
    SET NEW.updated = CURRENT_TIMESTAMP;
  END IF;
END 
;;
DELIMITER ;

Такой вариант я использовал, такой вариант работает. Если массово добавляются записи с другим created, то этот вариант не прокатит. И тогда я переписал его полностью на триггеры:

CREATE TABLE games (name VARCHAR(64), 
created TIMESTAMP NOT NULL DEFAULT 0, 
updated TIMESTAMP NOT NULL DEFAULT 0);
DELIMITER ;;
DROP TRIGGER IF EXISTS games_update;;
DROP TRIGGER IF EXISTS games_create;;
CREATE TRIGGER games_update BEFORE UPDATE ON games FOR EACH ROW 
BEGIN 
  SET NEW.updated = NOW(); 
END 
;;
CREATE TRIGGER games_create BEFORE INSERT ON games FOR EACH ROW 
BEGIN
  IF NEW.created = 0 THEN
    SET NEW.created = CURRENT_TIMESTAMP;
  END IF;
  IF NEW.updated = 0 THEN
    SET NEW.updated = NEW.created;
  END IF;
END 
;;
DELIMITER ;

Теперь NEW.created задаётся раньше, чем NEW.updated, и его можно использовать.

Например:

INSERT INTO games(name) VALUES ('123');
INSERT INTO games(name, created) VALUES ('222', '2011-01-01');
SELECT * FROM games;

+------+---------------------+---------------------+
| name | created             | updated             |
+------+---------------------+---------------------+
| 123  | 2011-09-20 23:43:11 | 2011-09-20 23:43:11 |
| 222  | 2011-01-01 00:00:00 | 2011-01-01 00:00:00 |
+------+---------------------+---------------------+

Всё работает, как задумано.