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

  • 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
    1. ALTER TABLE `user`
    2. ADD COLUMN `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'datetime row inserted',
    3. 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
    1. CREATE TABLE `user_test` (
    2. `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    3. `name` VARCHAR(32) NOT NULL,
    4. `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    5. `updated_at` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP
    6. );

    Testdaten einfügen

    SQL
    1. INSERT INTO `user_test` SET `name` = 'hans';
    2. INSERT INTO `user_test` SET `name` = 'wurst';

    Testausgabe

    SQL
    1. 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
    1. UPDATE `user_test` SET `name` = 'peter' where `id` = 2;

    Testausgabe

    SQL
    1. 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
    1. 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
    1. 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
    1. CREATE TABLE `user_test` (
    2. `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    3. `name` VARCHAR(32) NOT NULL,
    4. `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    5. `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    6. );


    Falls eine schon bestehende Tabelle erweitert werden soll:

    Code
    1. ALTER TABLE `table_name` -- add your table name
    2. ADD COLUMN `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    3. 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
    1. CREATE TABLE `user_test` (
    2. `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    3. `name` VARCHAR(32) NOT NULL
    4. );

    Testausgabe

    SQL
    1. 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
    1. ALTER TABLE `user_test`
    2. ADD COLUMN `created_at` DATETIME DEFAULT NULL,
    3. ADD COLUMN `updated_at` DATETIME DEFAULT NULL;

    Testausgabe

    SQL
    1. 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
    1. ALTER TABLE `user_test`
    2. MODIFY COLUMN `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    3. MODIFY COLUMN `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

    Testausgabe

    SQL
    1. 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
    1. 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.