Zusammenfassung der Wissenspunkte im Zusammenhang mit Null (IFNULL, COALESCE und NULLIF) in MySQL

Dieser Artikel erläutert anhand von Beispielen die mit Null verbundenen Wissenspunkte (IFNULL, COALESCE und NULLIF) in MySQL. Teilen Sie uns die Einzelheiten zu Ihrer Information mit:

In MySQL stellt der NULL-Wert einen unbekannten Wert dar, der sich von 0 oder der leeren Zeichenfolge „“ unterscheidet und nicht gleich sich selbst ist.

Wenn wir einen NULL-Wert mit einem anderen NULL-Wert oder einem beliebigen anderen Wert vergleichen, ist das Ergebnis NULL, da ein unbekannter Wert (NULL-Wert) mit einem anderen unbekannten Wert (NULL-Wert) verglichen wird und dessen Wert natürlich auch ein unbekannter Wert (NULL-Wert) ist.

Normalerweise verwenden wir NULL-Werte, um anzuzeigen, dass Daten fehlen, unbekannt oder nicht anwendbar sind. Beispielsweise könnte die Telefonnummer eines potenziellen Kunden NULL sein und kann später hinzugefügt werden. Wenn wir also eine Tabelle erstellen, können wir mithilfe der NOT NULL-Einschränkung angeben, ob die Spalte NULL-Werte akzeptiert. Als nächstes erstellen wir eine Leads-Tabelle und nutzen diese als Grundlage für ein detailliertes Verständnis:

CREATE TABLE führt (
  ID INT AUTO_INCREMENT PRIMARY KEY,
  Vorname VARCHAR(50) NOT NULL,
  Nachname VARCHAR (50) NICHT NULL,
  Quelle VARCHAR (255) NICHT NULL,
  E-Mail VARCHAR(100),
  Telefon VARCHAR(25)
);

Wir können sehen, dass „id“ die Primärschlüsselspalte ist, die keine NULL-Werte akzeptiert, und dass die Spalten „first_name“, „last_name“ und „source“ die Einschränkung NOT NULL verwenden, sodass in diese Spalten keine NULL-Werte eingefügt werden können, während die Spalten „email“ und „phone“ NULL-Werte akzeptieren können.

Daher können wir in der Einfügeanweisung den NULL-Wert verwenden, um fehlende Daten anzugeben. Die folgende Anweisung fügt beispielsweise eine Zeile in die Leads-Tabelle ein. Da die Telefonnummer fehlt, wird ein NULL-Wert verwendet:

INSERT INTO Leads (Vorname, Nachname, Quelle, E-Mail, Telefon)
WERT('John', 'Doe', 'Websuche', '[email protected]', NULL);

Da der Standardwert der E-Mail-Spalte NULL ist, können Sie die E-Mail in der INSERT-Anweisung wie folgt weglassen:

INSERT INTO leads(Vorname,Nachname,Quelle,Telefon)
WERTE('Lily','Bush','Kaltakquise','(408)-555-1234'),
('David','William','Websuche','(408)-888-6789');

Wenn wir den Wert einer Spalte auf NULL setzen möchten, können wir den Zuweisungsoperator (=) verwenden. Um beispielsweise die Telefonnummer von David William auf NULL zu aktualisieren, verwenden Sie die folgende UPDATE-Anweisung:

UPDATE führt 
SATZ 
  Telefon = NULL
WO
  Ich würde = 3;

Wenn Sie die Ergebnismenge jedoch mithilfe der Order By-Klausel in aufsteigender Reihenfolge sortieren, betrachtet MySQL NULL-Werte als niedriger als andere Werte und zeigt daher zuerst NULL-Werte an. Die folgende Abfrage sortiert die Telefonnummern in aufsteigender Reihenfolge:

WÄHLEN 
  *
AUS
  führt
BESTELLUNG PER Telefon;

Führen Sie die obige Abfrageanweisung aus. Die Ergebnisse sind wie folgt:

+----+------------+--------------+-----------+---------------------+----------------+
| ID | Vorname | Nachname | Quelle | E-Mail | Telefon |
+----+------------+--------------+-----------+---------------------+----------------+
| 1 | John | Doe | Websuche | [email protected] | NULL |
| 3 | David | William | Websuche | NULL | NULL |
| 2 | Lily | Bush | Kaltakquise | NULL | (408)-555-1234 |
+----+------------+--------------+-----------+---------------------+----------------+

Wenn Sie ORDER BY DESC verwenden, erscheinen NULL-Werte am Ende des Ergebnissatzes:

WÄHLEN 
  *
AUS
  führt
BESTELLUNG PER TELEFON DESC;

Führen Sie die obige Abfrageanweisung aus. Die Ergebnisse sind wie folgt:

+----+------------+--------------+-----------+---------------------+----------------+
| ID | Vorname | Nachname | Quelle | E-Mail | Telefon |
+----+------------+--------------+-----------+---------------------+----------------+
| 2 | Lily | Bush | Kaltakquise | NULL | (408)-555-1234 |
| 1 | John | Doe | Websuche | [email protected] | NULL |
| 3 | David | William | Websuche | NULL | NULL |
+----+------------+--------------+-----------+---------------------+----------------+
3 Reihen im Set

Wenn wir in einer Abfrage auf NULL testen möchten, können wir in der Where-Klausel den Operator IS NULL oder IS NOT NULL verwenden. Um beispielsweise die Leads abzurufen, die keine Telefonnummer angegeben haben, verwenden Sie den Operator IS NULL wie folgt:

WÄHLEN 
  *
AUS
  führt
WO
  Telefon IST NULL;

Führen Sie die obige Abfrageanweisung aus. Die Ergebnisse sind wie folgt:

+----+------------+--------------+---------+---------------------+----------+
| ID | Vorname | Nachname | Quelle | E-Mail | Telefon |
+----+------------+--------------+---------+---------------------+----------+
| 1 | John | Doe | Websuche | [email protected] | NULL |
| 3 | David | William | Websuche | NULL | NULL |
+----+------------+--------------+---------+---------------------+----------+
2 Reihen im Set

Wir können auch den Operator „IS NOT“ verwenden, um alle Leads abzurufen, die eine E-Mail-Adresse angegeben haben:

WÄHLEN 
  *
AUS
  führt
WO
  E-Mail ist nicht NULL;

Führen Sie die obige Abfrageanweisung aus. Die Ergebnisse sind wie folgt:

+----+------------+--------------+---------+---------------------+----------+
| ID | Vorname | Nachname | Quelle | E-Mail | Telefon |
+----+------------+--------------+---------+---------------------+----------+
| 1 | John | Doe | Websuche | [email protected] | NULL |
+----+------------+--------------+---------+---------------------+----------+
1 Reihe im Set

Aber auch wenn NULL ungleich NULL ist, werden in der GROUP BY-Klausel zwei NULL-Werte als gleich angesehen. Schauen wir uns das SQL-Beispiel an:

WÄHLEN 
  E-Mail, Anzahl(*)
AUS
  führt
GRUPPE NACH E-Mail;

Diese Abfrage gibt nur zwei Zeilen zurück, da die Zeilen, deren E-Mail-Spalte NULL ist, in einer Zeile zusammengefasst sind. Das Ergebnis ist wie folgt:

+---------------------+----------+
| E-Mail | Anzahl(*) |
+---------------------+----------+
| NULL | 2 |
| [email protected] | 1 |
+---------------------+----------+
2 Reihen im Set

Wir müssen wissen, dass Sie, wenn Sie eine eindeutige Einschränkung oder einen eindeutigen Index für eine Spalte verwenden, mehrere NULL-Werte in diese Spalte einfügen können. In diesem Fall betrachtet MySQL die NULL-Werte als unterschiedlich. Als Nächstes überprüfen wir dies, indem wir einen EINDEUTIGEN Index für die Telefonspalte erstellen:

ERSTELLEN SIE EINEN EINDEUTIGEN INDEX idx_phone FÜR Leads (Telefon);

Hier sollten wir beachten, dass MySQL bei Verwendung der BDB-Speicher-Engine NULL-Werte als gleich betrachtet, sodass wir nicht mehrere NULL-Werte mit einer eindeutigen Einschränkung in eine Spalte einfügen können.

Nachdem wir nun die Vor- und Nachteile von Null kennen, schauen wir uns an, wie damit in MySQL umgegangen wird. MySQL bietet insgesamt drei Funktionen, nämlich IFNULL, COALESCE und NULLIF.

Sehen wir uns jeden einzeln an. Zunächst akzeptiert die IFNULL-Funktion zwei Parameter. Die Funktion IFNULL gibt das erste Argument zurück, wenn es nicht NULL ist, andernfalls gibt sie das zweite Argument zurück. Beispielsweise gibt die folgende Anweisung die Telefonnummer (phone) zurück, wenn sie nicht NULL ist, andernfalls gibt sie N/A und nicht NULL zurück. Schauen wir uns ein Beispiel an:

WÄHLEN 
  id, vorname, nachname, IFNULL(telefon, 'N/A') telefon
AUS
  führt;

Führen Sie die obige Abfrageanweisung aus und erhalten Sie die folgenden Ergebnisse:

+----+------------+--------------+----------------+
| ID | Vorname | Nachname | Telefon |
+----+------------+--------------+----------------+
| 1 | John | Mustermann | Nicht zutreffend |
| 2 | Lily | Busch | (408)-555-1234 |
| 3 | David | William | Nicht zutreffend |
+----+------------+--------------+----------------+
3 Reihen im Set

Die Funktion COALESCE nimmt eine Liste von Argumenten und gibt das erste Argument zurück, das nicht NULL ist. Mit der Funktion COALESCE können Sie beispielsweise die Kontaktinformationen eines Leads basierend auf der Priorität der Informationen in der folgenden Reihenfolge anzeigen: Telefon, E-Mail und N/A. Hier sind einige Beispiele:

WÄHLEN 
  Ausweis,
  Vorname,
  Nachname,
  COALESCE (Telefon, E-Mail, „N/A“) Kontakt
AUS
  führt;

Führen Sie die obige Abfrageanweisung aus und erhalten Sie den folgenden Code:

+----+------------+--------------+---------------------+
| ID | Vorname | Nachname | Kontakt |
+----+------------+--------------+---------------------+
| 1 | John | Doe | [email protected] |
| 2 | Lily | Busch | (408)-555-1234 |
| 3 | David | William | Nicht zutreffend |
+----+------------+--------------+---------------------+
3 Reihen im Set

Schließlich gibt es die NULLIF-Funktion, die zwei Parameter akzeptiert. Wenn die beiden Argumente gleich sind, gibt die NULLIF-Funktion NULL zurück. Andernfalls wird das erste Argument zurückgegeben. Die NULLIF-Funktion ist nützlich, wenn eine Spalte sowohl NULL- als auch leere Zeichenfolgenwerte enthält. Beispielsweise haben wir versehentlich die folgende Zeile in die Leads-Tabelle eingefügt:

INSERT INTO Leads (Vorname, Nachname, Quelle, E-Mail, Telefon)
WERT('Thierry','Henry','Websuche','[email protected]','');

Weil „Telefon“ eine leere Zeichenfolge ist: „“, nicht NULL. Wenn wir also die Kontaktinformationen eines potenziellen Kunden abrufen möchten, erhalten wir anstelle einer E-Mail-Adresse eine leere Telefonnummer, etwa so:

WÄHLEN 
  Ausweis,
  Vorname,
  Nachname,
  COALESCE (Telefon, E-Mail, „N/A“) Kontakt
AUS
  führt;

Führen Sie die obige Abfrageanweisung aus und erhalten Sie den folgenden Code:

+----+------------+--------------+---------------------+
| ID | Vorname | Nachname | Kontakt |
+----+------------+--------------+---------------------+
| 1 | John | Doe | [email protected] |
| 2 | Lily | Busch | (408)-555-1234 |
| 3 | David | William | Nicht zutreffend |
| 4 | Thierry | Henry | |
+----+------------+--------------+---------------------+

Um dieses Problem zu lösen, müssen wir die Funktion NULLIF verwenden, um die Telefonnummer mit einem leeren String ('') zu vergleichen. Wenn sie gleich sind, wird NULL zurückgegeben, andernfalls die Telefonnummer:

WÄHLEN 
  Ausweis,
  Vorname,
  Nachname,
  COALESCE(NULLIF(Telefon, ''), E-Mail, 'N/A') Kontakt
AUS
  führt;

Führen Sie die obige Abfrageanweisung aus und erhalten Sie den folgenden Code:

+----+------------+--------------+---------------------------+
| ID | Vorname | Nachname | Kontakt |
+----+------------+--------------+---------------------------+
| 1 | John | Doe | [email protected] |
| 2 | Lily | Busch | (408)-555-1234 |
| 3 | David | William | Nicht zutreffend |
| 4 | Thierry | Henry | [email protected] |
+----+------------+--------------+---------------------------+
4 Reihen im Set

Okay, das ist alles zu diesem Eintrag.

Leser, die an weiteren MySQL-bezogenen Inhalten interessiert sind, können sich die folgenden Themen auf dieser Site ansehen: „MySQL-Abfragekenntnisse“, „MySQL-Transaktionsoperationskenntnisse“, „MySQL-gespeicherte Prozedurkenntnisse“, „Zusammenfassung der Kenntnisse zu MySQL-Datenbanksperren“ und „Zusammenfassung der allgemeinen MySQL-Funktionen“.

Ich hoffe, dass dieser Artikel für jedermann beim Entwurf einer MySQL-Datenbank hilfreich ist.