Variablen in MySQL deklarieren und verwenden

MySQL hat drei verschiedene Variablenmechanismen. Verwenden Sie eine benutzerdefinierte Sitzungsvariable wie @limit, wenn mehrere Anweisungen in einer Verbindung einen Wert gemeinsam nutzen. Verwenden Sie eine lokale Variable ohne @ in einer gespeicherten Prozedur, Funktion, einem Trigger oder Event. Verwenden Sie eine Systemvariable wie @@SESSION.sql_mode, wenn Sie den Server oder eine einzelne Verbindung untersuchen oder konfigurieren.

Der wichtigste Unterschied: DECLARE ist keine allgemeine Anweisung für ein beliebiges SQL-Fenster. Sie ist nur in einem BEGIN ... END-Block erlaubt und muss vor den übrigen Anweisungen dieses Blocks stehen. Für ein kurzes Skript beginnen Sie mit SET @name = value;. Die Schlüsselwörter DECLARE, SET, SELECT ... INTO, BEGIN ... END, SESSION und GLOBAL bleiben in den SQL-Beispielen unverändert.

Die richtige MySQL-Variable auswählen

Diese Formen sind nicht austauschbar. Insbesondere ist DECLARE @name INT nicht die Syntax für eine lokale Variable, und DECLARE name INT schlägt als eigenständige Anweisung außerhalb eines gespeicherten Programms fehl.

Eine benutzerdefinierte Variable in einer SQL-Sitzung verwenden

Benutzerdefinierte Variablen beginnen mit @. Sie müssen sie vor der Verwendung nicht deklarieren. Weisen Sie mit SET einen Wert oder Ausdruck zu und verwenden Sie die Variable in einer späteren Anweisung:

SET @low = 2, @high = 4;
SELECT n FROM demo_numbers WHERE n BETWEEN @low AND @high ORDER BY n;

Die Variablen gehören zur aktuellen Verbindung. Eine andere Verbindung kann sie nicht verwenden, und beim Ende der Verbindung verschwinden sie. Eine nicht initialisierte Benutzervariable ergibt NULL; initialisieren Sie sie daher am Anfang eines wiederverwendbaren Skripts.

SET ist die klarste Zuweisungsform. MySQL akzeptiert in einer SET-Anweisung auch :=:

SET @total = 10;
SET @total := @total + 5;
SELECT @total;

Benutzervariablen können Zahlen, Dezimalwerte, Zeichenketten, Binärzeichenketten oder NULL enthalten. Ihr Typ wird durch den zugewiesenen Wert bestimmt, nicht durch eine Datentypdeklaration. Verwenden Sie kompatible Werte und explizite Casts, wenn ein Wert den Typ wechselt.

Ein Abfrageergebnis mit SELECT ... INTO laden

Verwenden Sie SELECT ... INTO, wenn der Wert aus einer Abfrage kommt. Die Zahl der Zielvariablen muss der Zahl der ausgewählten Ausdrücke entsprechen:

SELECT COUNT(*) INTO @number_of_rows
FROM demo_numbers;

SELECT @number_of_rows;

Die Abfrage sollte genau eine Zeile liefern. COUNT(*) tut dies natürlich; auch eine Suche nach einem eindeutigen Schlüssel ist geeignet. Wenn mehrere Zeilen möglich sind, wählen Sie mit einer deterministischen ORDER BY-Klausel und LIMIT 1 gezielt eine Zeile aus.

Eine Benutzervariable ist kein Tabellen- oder Spaltenname. SET @table_name = 'demo_numbers'; SELECT * FROM @table_name; ersetzt keinen Tabellennamen. Für dynamisches SQL müssen Sie eine für PREPARE bestimmte Anweisung erstellen und Bezeichner der Anwendung prüfen.

MySQL 9.7 unterstützt Zuweisungen außerhalb von SET noch aus Kompatibilitätsgründen, markiert dieses Verhalten aber zur späteren Entfernung. Bevorzugen Sie SET und SELECT ... INTO statt Ausdrücken wie SELECT @n := n.

Eine lokale Variable in einem gespeicherten Programm deklarieren

Lokale Variablen verwenden DECLARE ohne @ und benötigen einen MySQL-Datentyp. Mit DEFAULT ist ein Anfangswert möglich; sonst ist der Anfangswert NULL. Deklarieren Sie Variablen am Anfang des BEGIN ... END-Blocks, vor ausführbaren Anweisungen. Sie gelten in diesem und verschachtelten Blöcken, aber nicht danach.

Die folgende Prozedur verwendet einen Eingabeparameter, eine lokale Variable und einen Ausgabeparameter. Die DELIMITER-Befehle gehören zum mysql-Client und sorgen dafür, dass der Client den Prozedurkörper als eine CREATE PROCEDURE-Anweisung sendet.

DROP PROCEDURE IF EXISTS count_numbers;
DELIMITER //
CREATE PROCEDURE count_numbers(IN p_min INT, OUT p_count INT)
BEGIN
  DECLARE local_count INT DEFAULT 0;
  SELECT COUNT(*) INTO local_count
  FROM demo_numbers
  WHERE n >= p_min;
  SET p_count = local_count;
END//
DELIMITER ;
CALL count_numbers(3, @result);
SELECT @result;

local_count existiert nur während der Prozedurausführung. Der OUT-Parameter wird über die bei CALL übergebene Sitzungsvariable zurückgegeben. Wenn eine Abfrage mehrere Spalten liefert, benötigen Sie ein Ziel pro Ausdruck und müssen den Fall ohne Treffer in der Routine behandeln.

Sie können mehrere Variablen desselben Typs in einer Anweisung deklarieren; getrennte Deklarationen sind oft leichter zu warten:

DECLARE quantity, retries INT DEFAULT 0;
DECLARE label VARCHAR(100) DEFAULT 'pending';

DECLARE definiert auch Bedingungen, Cursor und Handler. Ihre Reihenfolge ist festgelegt: Variablen und Bedingungen kommen vor Cursor, Cursor vor Handlern. Deshalb erzeugt eine Deklaration nach einer SELECT- oder SET-Anweisung einen Syntaxfehler.

Die Zeilengrenze von SELECT ... INTO verstehen

Ein skalares Ziel kann einen Wert speichern, aber keine beliebige Ergebnismenge. Die folgende Abfrage liefert absichtlich drei Zeilen und schlägt daher mit Fehler 1172 fehl:

SET @selected = 99;
SELECT n INTO @selected FROM demo_numbers ORDER BY n;

ERROR 1172 (42000) at line 6: Result consisted of more than one row

Begrenzen Sie die Abfrage auf eine bekannte Zeile, aggregieren Sie sie oder lassen Sie das Ergebnis als Zeilenmenge zurückgeben. LIMIT 1 ist nur mit einer absichtlichen Sortierung passend:

SELECT n INTO @selected
FROM demo_numbers
ORDER BY n DESC
LIMIT 1;

Dieselbe Ein-Zeilen-Regel gilt für lokale Variablen. Wenn eine Routine legitimerweise keine Zeile findet, initialisieren Sie die Variable bewusst und verwenden Sie bei Bedarf einen NOT FOUND-Handler oder eine Existenzprüfung.

Eine MySQL-Systemvariable untersuchen oder setzen

Systemvariablen konfigurieren den Server oder eine Clientverbindung. Untersuchen Sie sie mit SHOW VARIABLES oder @@:

SHOW SESSION VARIABLES LIKE 'sql_mode';
SELECT @@SESSION.sql_mode, @@GLOBAL.sql_mode;

Ohne Bereichsangabe verwendet SHOW VARIABLES die aktuelle Sitzung. Ein Sitzungswert betrifft nur diese Verbindung. Ein globaler Wert wird vom Server verwendet und initialisiert normalerweise neue Verbindungen. Für eine globale Änderung sind geeignete Berechtigungen und eine veränderbare Variable nötig.

SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
-- A privileged server administrator may use an appropriate GLOBAL setting.
-- SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES';

Die auskommentierte globale Anweisung ist absichtlich nicht Teil der Fixture: ihr Erfolg hängt von Berechtigungen sowie Gültigkeitsbereich und Änderbarkeit der Variable ab.

Eine nicht funktionierende Variable diagnostizieren

• Wenn DECLARE abgelehnt wird, prüfen Sie BEGIN ... END und die Position vor jeder ausführbaren Anweisung. Für ein gewöhnliches SQL-Skript verwenden Sie SET @name = value.
• Wenn unerwartet NULL erscheint, initialisieren Sie die Variable und prüfen Sie den Quellausdruck oder die Suche.
• Bei Fehler 1172 muss SELECT ... INTO genau eine Zeile liefern; andernfalls lassen Sie das Ergebnis als Zeilenmenge bestehen.
• Dass eine andere Verbindung @name nicht sieht, ist erwartetes Sitzungsverhalten.
• Wenn @@GLOBAL.name nicht geändert werden kann, prüfen Sie Änderbarkeit und Berechtigungen.

Die Erfolg-Fixture weist Sitzungsvariablen zu, verwendet sie in einem Filter, deklariert eine lokale Variable in einer Prozedur und gibt deren Ausgabeparameter zurück. Die Grenzfall-Fixture prüft den Mehrzeilenfehler von SELECT ... INTO. Beide verwenden einen temporären MySQL-Server.

Weitere Einzelheiten finden Sie in der MySQL-Referenz zu benutzerdefinierten Variablen, zur lokalen DECLARE-Anweisung und zur SET-Variablenzuweisung.