This post comes from the first version of this blog.
Please send me an email if anything needs an update. Thanks!

Oprogramowanie komputera zawiera w sobie wiele pułapek, o których trzeba wiedzieć, żeby w nie nie wpaść. Szczęśliwym trafem jeszcze nie miałem okazji cierpieć przez opisywany dzisiaj problem - przeczytałem o nim podczas poszukiwania informacji na temat innego, który już opisałem na niniejszym blogu. Serwer SQL, a dokładnie MySQL, to jedno z najpopularniejszych rozwiązań tej klasy, a co za tym idzie pewnego rodzaju standard. W dzisiejszym wpisie chciałbym pokazać jeden z problemów, na jaki możemy się natknąć podczas pracy z bazą danych opartą właśnie o niego.

Fotografia: Kevin Severud, CC-BY-SA.

(My)SQL: Aktualizowanie pola typu TIMESTAMP.

1
2
3
4
5
6
7

CREATE TABLE IF NOT EXISTS `tmp_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `category` int(11) NOT NULL,
  `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB ;

Wstawmy do niej następujący rekord:

1
2

INSERT INTO `tmp_table` (`id`, `name`, `category`, `added`)
VALUES (1, 'first post', 3, '2011-06-30 23:43:07');

Wszystko działa, także możemy spokojnie cieszyć się naszym rozwiązaniem:

Jakkolwiek baza danych z jedną tabelą świata nie zawojuje, tak widzimy, że MySQL nas nie oszukuje i działa jak trzeba, zgodnie z naszymi oczekiwaniami. Problem pojawia się wtedy, kiedy zechcemy np. zmienić jedną z wartości tego rekordu:

1
2
3

UPDATE `temp`.`tmp_table`
SET `name` = 'second post'
WHERE `tmp_table`.`id` =1;

Podglądamy w phpMyAdminie dane, jakie zostały zapisane w bazie i co widzimy? Oto zrzut ekranu:

Na pierwszy rzut oka wszystko dalej działa jak trzeba. Po dłuższych oględzinach zauważymy jednak, że pole ‘added’ także zostało zmienione, chociaż zapytanie UPDATE nie zawierało takich instrukcji. Okazuje się, że pola typu TIMESTAMP są aktualizowane automatycznie podczas każdej zmiany rekordu. Jest to bardzo istotna informacja, ponieważ ignorując ten fakt można zupełnie stracić informację na temat danych dotyczących daty i czasu, a jest to bardzo istotne dla niektórych skryptów - np. daty przelewów i tym podobne. Jak sobie z tym poradzić?

Jest oczywiście kilka możliwości zablokowania automatycznej aktualizacji pól tego typu, zarówno na stałe, jak też tylko w przypadku danego zapytania SQL.

Zacznijmy od modyfikacji zapytania SQL, aby nie aktualizować tego pola w danym przypadku. Aby osiągnąć ten cel, wystarczy dopisać do zapytania w klauzuli SET instrukcję aktualizującą pole aktualną wartością, a więc:

1

added = added

I całe zapytanie:

1
2
3

UPDATE `temp`.`tmp_table`
SET `name` = 'second post', `added` = `added`
WHERE `tmp_table`.`id` =1;

Zapisana wartość pozostanie niezmieniona.

Innym sposobem jest zmiana deklaracji typu pól typu TIMESTAMP w danej tabeli. Jak widać w deklaracji na samej górze wpisu, pole ‘added’ jest zadeklarowane następująco:

1

`added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

Niestety nie wystarczy usunąć fragmentu:

1

DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

ponieważ skrócony zapis:

1

`added` timestamp NOT NULL

jest równoważny temu długiemu. Manual MySQL na ten temat:

In a CREATE TABLE statement, the first TIMESTAMP column can be declared in any of the following ways:

With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, the column has the current timestamp for its default value, and is automatically updated.

With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

With a DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.

With no DEFAULT clause and with an ON UPDATE CURRENT_TIMESTAMP clause, the column has a default of 0 and is automatically updated.

With a constant DEFAULT value, the column has the given default and is not automatically initialized to the current timestamp. If the column also has an ON UPDATE CURRENT_TIMESTAMP clause, it is automatically updated; otherwise, it has a constant default and is not automatically updated.

1
2
3
4
5
6
7

CREATE TABLE IF NOT EXISTS `tmp_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `category` int(11) NOT NULL,
  `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB ;

A my, wykonując po raz kolejny zapytanie:

1
2
3

UPDATE `temp`.`tmp_table`
SET `name` = 'third post'
WHERE `tmp_table`.`id` =1;

cieszymy się znacznikiem czasu pozostającym w niezmienionej formie.

Pytania? Zapraszam do formularza komentarzy poniżej. ;]