Sehen, wann eine Datenbankzeile erstellt, oder geändert wurde.

cottton · 6. Juni 2017

Ziel: eine MySql Datenbanktabelle erweitern, um zu sehen, wann eine Zeile erstellt wurde, oder geändert wurde.

Verwendete Techniken: MySql

Schwierigkeitsgrad: für Anfänger geeignet, Grundkenntnisse über die Funktionsweisen von SQL vorausgesetzt

Wichtig: IMMER Daten sichern, bevor man an Produktivdaten rangeht!

Angenommen, Du hast eine Tabelle `user` erstellt, und Du möchest abfragen können, wann ein Benutzer erstellt bzw geändert wurde.

Dann ist der einfachste weg das hinzufügen zweier Felder:

`created_at` und `updated_at`.

Das Ganze wird ein "Einmalaufwand". Die beiden Felder werden hinzugefügt, und Auslöser (Trigger) erledigen den Rest.

SQL

ALTER TABLE `user`
ADD COLUMN `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'datetime row inserted',
ADD COLUMN `updated_at` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT 'datetime row updated';

Wichtig: bei einem INSERT INTO oder UPDATE sollten keine Werte für die Felder `created_at` und `updated_at` mitgegeben werden.

Das sollte komplett dem MySql-Server überlassen werden.

Was passiert hier:

Bei einer neuen Zeile (INSERT INTO) wird der Wert für das Feld `created_at` mit dem momentanen Zeitstempel versehen.

Dieser Zeitstempel wird nie wieder geändert. Somit ist klar, wann genau diese Zeile eingefügt wurde.

Beim ändern einer Zeile (UPDATE) wird der Wert für das Feld `updated_at` mit dem momentanen Zeitstempel versehen.

Das passiert immer nur dann, wenn sich auch wirklich Daten geändert haben.

Am Beispiel: Testtabelle erstellen und Beispieldaten einfügen

(Die Tabelle wird hier `user_test` genannt, um nicht evtl vorhandene Tabellen zu "versauen")

Erstellen der Tabelle

SQL

CREATE TABLE `user_test` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(32) NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP
);

Testdaten einfügen

SQL

INSERT INTO `user_test` SET `name` = 'hans';
INSERT INTO `user_test` SET `name` = 'wurst';

Testausgabe

SQL

SELECT * FROM `user_test`;

+----+-------+---------------------+---------------------+

| id | name | created_at | updated_at |

+----+-------+---------------------+---------------------+

| 1 | hans | 2017-06-06 17:03:22 | 0000-00-00 00:00:00 |

| 2 | wurst | 2017-06-06 17:03:25 | 0000-00-00 00:00:00 |

+----+-------+---------------------+---------------------+

Der MySql-Server hat sich hier also wie gewollt um die Werte der Spalten `created_at` und `updated_at` gekümmert.

Jetzt wollen wir wissen, was passiert, wenn eine Zeile geändert wird:

SQL

UPDATE `user_test` SET `name` = 'peter' where `id` = 2;

Testausgabe

SQL

SELECT * FROM `user_test`;

+----+-------+---------------------+---------------------+

| id | name | created_at | updated_at |

+----+-------+---------------------+---------------------+

| 1 | hans | 2017-06-06 17:03:22 | 0000-00-00 00:00:00 |

| 2 | peter | 2017-06-06 17:03:25 | 2017-06-06 17:09:50 |

+----+-------+---------------------+---------------------+

Wir sehen also, dass sich ser MySql-Server um das Feld `updated_at` gekümmert hat.

Jetzt noch ein Bsp, bei dem wir eigtl gar keine Änderungen verursachen

SQL

UPDATE `user_test` SET `name` = 'peter' where `id` = 2;

Info: hier sollte sich nichts ändern, da wir ja den Namen zuvor auf genau diesen Wert ('peter') gesetzt haben.

Testausgabe

SQL

SELECT * FROM `user_test`;

+----+-------+---------------------+---------------------+

| id | name | created_at | updated_at |

+----+-------+---------------------+---------------------+

| 1 | hans | 2017-06-06 17:03:22 | 0000-00-00 00:00:00 |

| 2 | peter | 2017-06-06 17:03:25 | 2017-06-06 17:09:50 |

+----+-------+---------------------+---------------------+

Keine Änderung. Das Feld `updated_at` behält seinen Wert von '2017-06-06 17:09:50' beim Benutzer mit `id` 2.

Das wars.

Wäre nur noch zu erwähnen, dass man den DEFAULT Wert bei `updated_at` statt '0000-00-00 00:00:00'

auf CURRENT_TIMESTAMP setzen kann.

Warum:

'0000-00-00 00:00:00' ist eigtl kein gültiger Zeitstempel. Manche stört es, manche nicht.

Ändert man den DEFALUT Wert von

`updated_at` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP

auf

`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

dann bekommt man bei einem INSERT INTO z.B.

+----+-------+---------------------+---------------------+

| id | name | created_at | updated_at |

+----+-------+---------------------+---------------------+

| 1 | hans | 2017-06-06 17:19:19 | 2017-06-06 17:19:19 |

| 2 | wurst | 2017-06-06 17:19:22 | 2017-06-06 17:19:22 |

+----+-------+---------------------+---------------------+

Die Felder `created_at` und `updated_at` sind genau gleich.

Möchte man also wissen, ob eine Zeile nie geändert wurde, dann fragt man ab, ob `created_at` und `updated_at` gleich sind:

SELECT * FROM `user_test` WHERE `created_at` = `updated_at`;

Hier noch einmal die CREATE TABLE Variante ohne '0000-00-00 00:00:00':

Code

CREATE TABLE `user_test` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(32) NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Falls eine schon bestehende Tabelle erweitert werden soll:

Code

ALTER TABLE `table_name` -- add your table name
ADD COLUMN `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
ADD COLUMN `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

Bei bereits bestehenden Daten fehlen nun natürlich die richtigen Zeitwerte.

Das könnte man hinnehmen - alle bereits vorhandenen Zeilen bekämen den jetzigen Zeitstempel.

Oder man lässt deren `created_at` und `updated_at` Felder auf den Wert '0000-00-00 00:00:00' setzen.

Dazu benötigen wir mehrere Schritte:

Ausgangs Tabelle

Code

CREATE TABLE `user_test` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(32) NOT NULL
);

Testausgabe

SQL

SELECT * FROM `user_test`;

+----+-------+

| id | name |

+----+-------+

| 1 | hans |

| 2 | wurst |

+----+-------+

Upgrade - dabei setzen wir auf allen vorhandenen Zeilen den (vorübergehenden) DEFAULT Wert auf NULL

Code

ALTER TABLE `user_test`
ADD COLUMN `created_at` DATETIME DEFAULT NULL,
ADD COLUMN `updated_at` DATETIME DEFAULT NULL;

Testausgabe

SQL

SELECT * FROM `user_test`;

+----+-------+------------+------------+

| id | name | created_at | updated_at |

+----+-------+------------+------------+

| 1 | hans | NULL | NULL |

| 2 | wurst | NULL | NULL |

+----+-------+------------+------------+

Jetzt ändern wir die neuen Felder, um die Werte vom MySql-Server befüllen zu lassen

Code

ALTER TABLE `user_test`
MODIFY COLUMN `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
MODIFY COLUMN `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

Testausgabe

SQL

SELECT * FROM `user_test`;

+----+-------+---------------------+---------------------+

| id | name | created_at | updated_at |

+----+-------+---------------------+---------------------+

| 1 | hans | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |

| 2 | wurst | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |

+----+-------+---------------------+---------------------+

Alle zuvor vorhandenen Zeilen bekommen nun also den Wert '0000-00-00 00:00:00'.

Es gitb also keinen `created_at` Wert für diese Einträge (woher auch - da gab es die Erweiterung ja noch nicht).

Aber alle jetzt eingefügten bzw geänderten Zeilen bekommen ihre korrekten Zeitstempel:

SQL

UPDATE `user_test` SET `name` = 'Hans' where `id` = 1; -- 'Hans' instead of 'hans'

Testausgabe

SELECT * FROM `user_test`;

+----+-------+---------------------+---------------------+

| id | name | created_at | updated_at |

+----+-------+---------------------+---------------------+

| 1 | Hans | 0000-00-00 00:00:00 | 2017-06-06 17:46:39 |

| 2 | wurst | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |

+----+-------+---------------------+---------------------+

Möchte man hier wissen, ob eine Zeile geändert wurde, dann fragt man ab,

ob `created_at` und `updated_at` gleich sind und `updated_at` größer als '0000-00-00 00:00:00':

SELECT * FROM `user_test` WHERE `created_at` = `updated_at` and `updated_at` > 0;

Es gibt sicherlich noch andere Möglichkeiten, um bereits vorhandene Daten zu "kennzeichen". Ideen einfach mal posten.

Stef · 11. Juni 2017

Hey,

danke für das Tutorial. Kann gut weiterhelfen.

Eine Frage habe ich dennoch. Wenn der User jetzt z.b. sein Beitrag noch mehrere Male ändert, wird die spalte, updated_at, immer aktualisiert ?

Stef

JR Cologne · 11. Juni 2017

Klar, bei jeder Änderung der entsprechenden Zeile wird automatisch updated_at auf den aktuellen Timestamp gesetzt.

Das funktioniert dank dem Zusatz ON UPDATE.

Sehen, wann eine Datenbankzeile erstellt, oder geändert wurde.

Jetzt mitmachen!

Teilen

Tags