Variablen in MySQL

• Variablen dienen zur temporären Speicherung von Werten innerhalb von SQL-Sitzungen, Stored Procedures oder Funktionen.

Typen

Lokale Variablen

• Nur innerhalb eines BEGIN...END-Blocks gültig (z.B. in Stored Procedures / Functions)
• Müssen mit DECLARE deklariert werden
• Existieren nur während der Ausführung der Routine

DECLARE variablen_name INT DEFAULT 0;

SET variablen_name = 42;

Session Variablen

• Gültig für die gesamte aktuelle Datenbankverbindung (Session)
• Beginnen immer mit @
• Keine Deklaration notwendig
• Werden automatisch gelöscht, wenn die Session endet

SET @meine_variable = 100;

SELECT @meine_variable;

Globale System-Variablen

• Gelten für den gesamten MySQL-Server (alle Sessions)
• Beginnen mit @@
• Können nur mit ausreichenden Berechtigungen geändert werden
• Änderungen gelten sofort, aber nicht dauerhaft (nach Neustart zurückgesetzt)

SET GLOBAL event_scheduler = ON;

SELECT @@autocommit;

Verwendungsbeispiel

DELIMITER //

CREATE PROCEDURE var_beispiel()

BEGIN

-- Lokale Variable

DECLARE lokал INT DEFAULT 0;

SET lokal = 10;

-- Session-Variable setzen

SET @session_var = lokal * 2;

END //

DELIMITER ;

CALL var_beispiel();

SELECT @session_var; -- Ergebnis: 20

Stored Procedures

• Gespeicherte Programme auf dem Server, die mehrere SQL-Befehle ausführen Gespeicherte Programme auf dem Server (Datenbank)
• Führen mehrere SQL-Befehle als eine einzige Einheit aus
• Können Parameter akzeptieren und Werte zurückgeben
• Werden zur Kapselung von Geschäftslogik und zur Verbesserung der Leistung/Sicherheit verwendet

Erstellen

DELIMITER //

CREATE PROCEDURE proc_name(in param1 int, out param2 int)

BEGIN

SELECT Count(*)

INTO param2

FROM users

WHERE age > param1;

END //

delimiter ;

Aufrufen

CALL proc_name(18, @result);

SELECT @result;

Parameter

Diese Modi legen die Richtung des Datenflusses fest.

1. Input (IN)

• Datenfluss zur aufgerufenen Einheit (Lesen).
• Übergabe eines Anfangswertes.
• Der Wert in der aufrufenden Einheit bleibt unverändert.

2. Output (OUT)

• Datenfluss von der aufgerufenen Einheit zurück an die aufrufende Einheit.
• Rückgabe eines oder mehrerer Ergebnisse.
• Der zugewiesene Wert ersetzt den ursprünglichen Wert beim Aufrufer.

3. Input/Output (INOUT)

• Datenfluss zur und von der aufgerufenen Einheit (Lesen und Schreiben).
• Dient zur Modifikation eines existierenden Wertes.
• Der modifizierte Wert ersetzt den ursprünglichen Wert beim Aufrufer.

Löschen

DROP PROCEDURE proc_name;

Stored Functions

• Definition: Gespeicherte SQL-Routinen in der DB. Führen als Einheit aus und geben immer genau einen Wert zurück. Benötigen RETURNS-Typ. Optional DETERMINISTIC.
• Einsatz: Kapselung/Wiederverwendung von Logik/Berechnungen. Vereinfachen komplexe SELECT-Abfragen. Sichern Datenkonsistenz/Integrität. Performance-Verbesserung (vorkompiliert).

Erstellen

DELIMITER //

CREATE FUNCTION get_user_count(min_age INT)

RETURNS INT

DETERMINISTIC

BEGIN

DECLARE total INT;

SELECT COUNT(*) INTO total FROM users WHERE age >= min_age;

RETURN total;

END //

DELIMITER ;

Aufruf

SELECT get_user_count(18);

Löschen

DROP FUNCTION get_user_count;

Transaktionen

Eine Transaktion bündelt mehrere SQL-Befehle zu einer sicheren, logischen Einheit und erfüllt die ACID-Prinzipien.

Sie wird immer dann verwendet, wenn:

1. Mehrere Aktionen zusammengehören und nur als Ganzes gültig sind (z.B. die Überweisung von Geld, bei der sowohl das Abbuchen als auch das Gutschreiben erfolgreich sein muss).
2. Die Konsistenz der Datenbank zu jedem Zeitpunkt garantiert werden muss, auch wenn mehrere Benutzer gleichzeitig Änderungen vornehmen.
3. Änderungen bei Fehlern oder bewusstem Abbruch rückgängig gemacht werden müssen.

Beispiel

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

Rollback

ROLLBACK;

Autocommit

SELECT @@autocommit;

SET autocommit = 0;

Multi-Session

Wenn mehrere Benutzer (Sessions) gleichzeitig versuchen, dieselben Daten zu ändern, können Probleme der Datenkonsistenz und Datenintegrität entstehen.

• Problem: Ohne Mechanismen zur Steuerung des gleichzeitigen Zugriffs könnten sich Transaktionen gegenseitig beeinflussen und zu falschen Ergebnissen führen (z.B. Lost Update, Dirty Read).
• Lösung: Datenbanken verwenden Locking und Isolation Levels (siehe unten), um konkurrierenden Zugriff zu regeln. Locks stellen sicher, dass eine Ressource (z.B. eine Zeile in einer Tabelle) während der Bearbeitung durch eine Transaktion für andere Transaktionen gesperrt ist.

Levels

Die Isolationsebene (Isolation Level) bestimmt, wie stark sich gleichzeitige Transaktionen gegenseitig beeinflussen dürfen. Sie ist ein Kompromiss zwischen Datenkonsistenz und Performance.

MySQL unterstützt (standardmäßig) vier Isolation Levels, die unterschiedliche Probleme verhindern:

• READ UNCOMMITTED
  • Beschreibung: Niedrigste Isolation. Eine Transaktion sieht unbestätigte (ungecommittete) Änderungen anderer Transaktionen.
  • Probleme: Erlaubt Dirty Read, Non-Repeatable Read und Phantom Read.
• READ COMMITTED
  • Beschreibung: Eine Transaktion sieht nur Änderungen, die committed wurden.
  • Probleme: Verhindert Dirty Read, erlaubt aber Non-Repeatable Read und Phantom Read.
• REPEATABLE READ
  • Beschreibung: MySQL-Standard. Eine Transaktion erhält bei wiederholtem Lesen dieselben Daten, solange die Transaktion läuft.
  • Probleme: Verhindert Dirty Read und Non-Repeatable Read, erlaubt aber Phantom Read.
• SERIALIZABLE
  • Beschreibung: Höchste Isolation. Die Transaktionen werden seriell ausgeführt, um größtmögliche Konsistenz zu gewährleisten.
  • Probleme: Verhindert Dirty Read, Non-Repeatable Read und Phantom Read.

Ändern des Isolation Levels:

Eine Transaktion: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Gesamte Session: SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

ACID

• Atomicity: Eine Transaktion ist eine unteilbare Einheit, die entweder komplett ausgeführt wird (commit) oder komplett rückgängig gemacht wird (abort/rollback). Es gibt keinen "halbfertigen" Zustand.
• Consistency: Eine Transaktion überführt die Datenbank von einem gültigen Zustand in einen anderen gültigen Zustand und hält dabei alle definierten Regeln und Integritätsbedingungen ein.
• Isolation: Gleichzeitig ablaufende Transaktionen beeinflussen sich nicht gegenseitig. Jede Transaktion sieht es so, als wäre sie die einzige, die auf der Datenbank arbeitet.
• Durability: Sobald eine Transaktion erfolgreich abgeschlossen (committed) wurde, sind ihre Änderungen dauerhaft gespeichert und überleben Systemausfälle wie Stromausfälle oder Abstürze.

Events

• Reaktion auf Aktionen: Events ermöglichen es, dass verschiedene Teile einer Anwendung auf bestimmte Vorkommnisse (z.B. Mausklick, Datenänderung) reagieren, ohne direkt miteinander gekoppelt zu sein.
• Entkopplung und Modularität: Sie fördern lose Kopplung, da der Auslöser eines Events (Publisher) nicht wissen muss, wer darauf reagiert (Subscriber). Das verbessert die Wartbarkeit und Erweiterbarkeit.
• Asynchrone Kommunikation: Events können für die asynchrone Kommunikation verwendet werden, um zeitintensive Aufgaben in den Hintergrund zu verlagern und die Hauptanwendung nicht zu blockieren.

Aktivieren

SET GLOBAL event_scheduler = ON;

Einmaliges Event

CREATE EVENT one_time_event

ON SCHEDULE AT '2026-01-01 00:00:00'

DO

INSERT INTO test VALUES ('Happy New Year');

Wiederkehrendes Event

CREATE EVENT my_event

ON SCHEDULE EVERY 1 DAY

DO

DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;

Löschen

DROP EVENT my_event;

Triggers

• Automatisierung: Automatische Ausführung von Aktionen bei Datenänderungen (INSERT, UPDATE, DELETE).
• Datenintegrität/Validierung: Sicherstellen komplexer Geschäftsregeln und Datenkonsistenz.
• Protokollierung/Audit: Nachverfolgen und Aufzeichnen von Änderungen an Daten.

Erstellen

DELIMITER //

CREATE TRIGGER before_user_insert

BEFORE INSERT ON users

FOR EACH ROW

BEGIN

SET NEW.created_at = NOW();

END //

DELIMITER ;

Typen

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELETE

Zugriff

• NEW.column
• OLD.column

Beispiel

DELIMITER //

CREATE TRIGGER after_delete_user

AFTER DELETE ON users

FOR EACH ROW

BEGIN

INSERT INTO deleted_users_log(user_id, msg)

VALUES (OLD.id,

CONCAT(“User gelöscht: “, OLD.name));

END //

DELIMITER ;

Löschen

DROP TRIGGER trigger_name;

Beispiele aus dem Unterricht

Procedure

DELIMITER @@

CREATE PROCEDURE proc_OrtEinfuegen(

IN id INT,

IN ort VARCHAR(255),

IN schulname VARCHAR(255)

)

BEGIN

INSERT INTO kurse.ort (ortnr, ort, schule)

VALUES (id, ort, schulname);

END @@

DELIMITER ;

CALL proc_OrtEinfuegen(71, 'Erlangen', 'Baumschule');

Functions

DELIMITER $$

CREATE FUNCTION fakultaet(n INT)

RETURNS INT

DETERMINISTIC

BEGIN

DECLARE ergebnis INT DEFAULT 1;

DECLARE i INT DEFAULT 1;

IF n < 0 THEN

RETURN NULL; -- negative Fakultät existiert nicht

END IF;

WHILE i <= n DO

SET ergebnis = ergebnis * i;

SET i = i + 1;

END WHILE;

RETURN ergebnis;

END$$

DELIMITER ;

SELECT fakultaet(5) AS result;

Events

use bank;

create event test_event_03ALTER

on schedule every 1 minute ends '2026-02-03 20:31:00'

do

insert into messages (message, created_at)

values ('test', NOW();

Trigger

DELIMITER //

CREATE TRIGGER trg_auth_before_insert

BEFORE INSERT ON auth

FOR EACH ROW

BEGIN

-- Entferne alle Leerzeichen aus dem neuen Usernamen

SET NEW.user = REPLACE(NEW.user, ' ', '');

-- Trage das Ereignis ins Log ein

INSERT INTO log (wann, wer, was)

VALUES (

NOW(),

USER(),

CONCAT('Neuer User angelegt: ', NEW.user)

);

END //

DELIMITER ;

Transactions

set autocommit = OFF;

select * from kunde;

begin;

update kunde set saldo = saldo -500 where id = 1;

update kunde set saldo = saldo +500 where id = 2;

commit; # erst mit dem commit werden die neuen saldos gespeichert

mit Rollback kann die Änderung also vor dem commit rückgängig gemacht werden

set autocommit = ON;

begin;

update kunde set saldo = saldo -500 where id = 1;

update kunde set saldo = saldo +500 where id = 2;

commit; # durch autocommit on hat diese Zeile keinen Einfluss

Rollback hat keinen Einfluss

START TRANSACTION;

INSERT INTO tab2 VALUES (NULL, 'Hello', 1);

INSERT INTO tab2 VALUES (NULL, 'World', 2);

INSERT INTO tab2 VALUES (NULL, 'in SQL', 3);

COMMIT;