Vertiefendes Verständnis von Null in SQL

NULL steht für Unbekanntheit und Unsicherheit in der Computer- und Programmierwelt. Obwohl die chinesische Übersetzung „leer“ ist, ist diese Leere (null) nicht so leer (leer). Null stellt einen unbekannten Zustand dar, einen zukünftigen Zustand. Ich weiß zum Beispiel nicht, wie viel Geld Xiao Ming in seiner Tasche hat, aber ich kann nicht sicher sein, dass es 0 ist. Derzeit wird Null in Computern zur Darstellung verwendet unbekannt und unsicher.

Obwohl Leute, die sich mit SQL auskennen, keine Zweifel an Null haben werden, ist es immer noch schwierig, einen Artikel zu finden, der es umfassend zusammenfasst. Ich habe eine englische Version gesehen und es fühlte sich gut an.

Tony Hoare erfand die Nullreferenz im Jahr 1965 und betrachtete sie als seinen „Milliarden-Dollar-Fehler“. Auch heute, 50 Jahre später, sind Nullwerte in SQL immer noch für viele häufige Fehler verantwortlich.

Werfen wir einen Blick auf einige der schockierendsten Situationen.

Null unterstützt keine Größen-/Gleichheitsbeurteilung

Bei den folgenden beiden Abfragen sind die zurückgegebenen Datensätze unabhängig von der Anzahl der Datensätze in der Benutzertabelle 0 Zeilen:

select * from users where deleted_at = null;

– result: 0 rows

select * from users where deleted_at != null;

– result: 0 rows

Wie könnte das sein? Alles nur, weil null einen „unbekannten“ Typ darstellt. Das heißt, es macht keinen Sinn, normale bedingte Operatoren zu verwenden, um Null mit anderen Werten zu vergleichen. Null ist nicht gleich Null (ungefähres Verständnis: Ein unbekannter Wert kann nicht gleich einem unbekannten Wert sein, und die Beziehung zwischen den beiden ist ebenfalls unbekannt, sonst werden Mathematik und Logik durcheinander gebracht).

– Hinweis: Das folgende SQL ist für MySQL geeignet, Sie müssen … von dual;

select null > 0;

– result: null

select null < 0;

– result: null

select null = 0;

– result: null

select null = null;

– result: null

select null != null;

– result: null

hinzufügen Der richtige Weg, einen Wert mit Null zu vergleichen, besteht darin, das Schlüsselwort is und den Operator is not zu verwenden:

select * from users

where deleted_at is null;

– result: 所有被标记为删除的 users

select * from users

where deleted_at is not null;

– result: 所有被标记为删除的 users

Wenn Sie feststellen möchten, ob die Werte zweier Spalten unterschiedlich sind, können Sie is different from:

select * from users

where has_address is distinct from has_photo

– result: 地址(address)或照片(photo)两者只有其一的用户

verwenden nicht mit Null einverstanden

Unterabfragen (Subselect) sind eine sehr praktische Möglichkeit, Daten zu filtern. Wenn Sie beispielsweise Benutzer abfragen möchten, die keine Pakete haben, können Sie eine Abfrage wie die folgende schreiben:

select * from users 

where id not in (select user_id from packages)

Aber zu diesem Zeitpunkt, wenn die user_id einer Zeile in der Pakettabelle null ist, entsteht ein Problem: Das Rückgabeergebnis ist leer! Um zu verstehen, warum diese seltsame Sache passiert, müssen wir verstehen, was der SQL-Compiler getan hat Beispiel:

select * from users 

where id not in (1, 2, null)

Diese SQL-Anweisung wird konvertiert in:

select * from users 

where id != 1 and id != 2 and id != null

Wir wissen, dass das Ergebnis von id != null ein unbekannter Wert ist, null. Und das Ergebnis der UND-Operation zwischen einem beliebigen Wert und null ist, sodass es keiner anderen Bedingung entspricht Der Wert null ist nicht wahr.

Wenn die Bedingungen umgekehrt sind, treten keine Probleme mit den Abfrageergebnissen auf. Jetzt fragen wir Benutzer mit Paketen ab.

select * from users 

where id in (select user_id from packages)

​Ähnlich können wir ein einfaches Beispiel verwenden:

select * from users

where id in (1, 2, null)

Dieses SQL wird konvertiert in:

select * from users 

where id = 1 or id = 2 or id = null

Da es sich bei der where-Klausel um eine Reihe von ODER-Bedingungen handelt, spielt es keine Rolle, ob eine davon null ergibt. Unwahre Werte haben keinen Einfluss auf die Berechnungsergebnisse anderer Teile der Klausel und sind gleichbedeutend mit Ignorieren.

​Null und Sortieren

Beim Sortieren wird der Nullwert als der größte betrachtet. Dies kann beim Sortieren in absteigender Reihenfolge (absteigend) zu Problemen führen, da der Nullwert an erster Stelle steht.

Die folgende Abfrage dient dazu, Benutzerrankings basierend auf Bewertungen anzuzeigen, aber Benutzer ohne Bewertungen werden an die Spitze gesetzt

select name, points

from users

order by 2 desc;

– points 为 null 的记录排在所有记录之前!

Es gibt zwei Denkweisen zur Lösung dieser Art von Problem. Die einfachste Möglichkeit ist die Verwendung von „coalesce“, um den Effekt von null zu beseitigen:

– 在输出时将 null 转换为 0 :

select name, coalesce(points, 0)

from users

order by 2 desc;

– 输出时保留 null, 但排序时转换为 0 :

select name, points

from users

order by coalesce(points, 0) desc;

Es gibt eine andere Möglichkeit, die Datenbankunterstützung erfordert, nämlich die Angabe, ob der Nullwert beim Sortieren an erster oder letzter Stelle stehen soll:

select name, coalesce(points, 0)

from users

order by 2 desc nulls last;

Natürlich kann Null auch verwendet werden, um das Auftreten von Fehlern zu verhindern, beispielsweise um mathematische Operationsfehler zu behandeln, wenn der Divisor Null ist.

Geteilt durch 0

Durch Null zu dividieren ist ein sehr schmerzhafter Fehler. SQL, das gestern einwandfrei lief, ging plötzlich schief, als es durch 0 geteilt wurde. Eine übliche Lösung besteht darin, zunächst mithilfe einer case-Anweisung zu bestimmen, ob der Nenner 0 ist, und dann die Divisionsoperation durchzuführen.

select case when num_users = 0 then 0 

else total_sales/num_users end;

Die Art und Weise der ASE-Anweisung ist tatsächlich hässlich und der Nenner wird wiederverwendet. Es ist in Ordnung, wenn es sich um eine einfache Situation handelt, aber wenn der Nenner ein sehr komplexer Ausdruck ist, wird es eine Tragödie geben: Es ist schwer zu lesen, schwer zu warten und zu ändern und es wird viele Fehler geben, wenn Sie nicht aufpassen.

Zu diesem Zeitpunkt können wir uns die Vorteile von null ansehen. Verwenden Sie nullif, um den Nenner zu null zu machen, wenn er 0 ist. Dies wird keinen Fehler mehr melden. Wenn num_users = 0 ist, wird das Rückgabeergebnis null.

select total_sales/nullif(num_users, 0);

nullif 是将其他值转为 null, 而Oracle的 nvl 是将 null 转换为其他值。

Wenn Sie nicht Null möchten, sondern in 0 oder andere Zahlen konvertieren möchten, können Sie die auf dem vorherigen SQL basierende Koaleszenzfunktion verwenden:

select coalesce(total_sales/nullif(num_users, 0), 0);

null 再转换回0

Schlussfolgerung

Tony Hoare mag seinen Fehler bereuen, aber zumindest kann das Problem der Null leicht gelöst werden. Üben Sie also Ihren neuen ultimativen Zug und halten Sie sich von der ungültigen Grube (Nullifizierung) fern, die von Null gegraben wurde!

Das obige ist der detaillierte Inhalt vonVertiefendes Verständnis von Null in SQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!