MySQL NULL-Wertbehandlung

Wir wissen bereits, dass MySQL denSELECT-Befehl und dieWHERE-Klausel verwendet, um Daten in der Datentabelle zu lesen, aber wenn das angegebene Abfragebedingungsfeld NULL ist, funktioniert der Befehl möglicherweise nicht ordnungsgemäß.

In MySQL wird NULL verwendet, um fehlende oder unbekannte Daten darzustellen. Der Umgang mit NULL-Werten erfordert besondere Sorgfalt, da dies in der Datenbank zu Ergebnissen führen kann, die von Ihren Erwartungen abweichen.

Um diese Situation zu behandeln, bietet MySQL drei Hauptoperatoren:

  • IS NULL:Wenn der Spaltenwert NULL ist, gibt dieser Operator true zurück.
  • IS NOT NULL:Wenn der Spaltenwert nicht NULL ist, gibt der Operator true zurück.
  • <=>:Vergleichsoperator (im Gegensatz zum =-Operator), der true zurückgibt, wenn die beiden verglichenen Werte gleich sind oder wenn beide NULL sind.

Die bedingte Vergleichsoperation für NULL ist speziell. Sie können = NULL oder != NULL nicht verwenden, um NULL-Werte in einer Spalte zu finden.

In MySQL gibt der Vergleich von NULL mit einem anderen Wert (sogar NULL) immer NULL zurück, das heißt, NULL = NULL gibt NULL zurück.

In MySQL verwenden Sie die Operatoren IS NULL und IS NOT NULL, um NULL zu behandeln.

Hinweis:

select * , columnName1+ifnull(columnName2,0) from tableName;

columnName1 und columnName2 sind vom Typ int. Wenn columnName2 einen NULL-Wert hat, ist columnName1 + columnName2 = NULL. ifnull(columnName2,0) konvertiert den NULL-Wert in columnName2 in 0.

Häufige Überlegungen und Tipps zum Umgang mit NULL-Werten in MySQL

1. Prüfen Sie, ob es NULL ist:

Um zu prüfen, ob eine Spalte NULL ist, können Sie die Bedingungen IS NULL oder IS NOT NULL verwenden.

SELECT * FROM employees WHERE department_id IS NULL;
SELECT * FROM employees WHERE department_id IS NOT NULL;

2. Verwenden Sie die COALESCE-Funktion, um NULL zu behandeln:

Die COALESCE-Funktion kann verwendet werden, um NULL-Werte zu ersetzen. Sie akzeptiert mehrere Argumente und gibt den ersten Nicht-NULL-Wert in der Argumentliste zurück:

SELECT product_name, COALESCE(stock_quantity, 0) AS actual_quantity
FROM products;

In der obigen SQL-Anweisung gibt COALESCE 0 zurück, wenn die Spalte stock_quantity NULL ist.

3. Verwenden Sie die IFNULL-Funktion, um NULL zu behandeln:

Die IFNULL-Funktion ist die MySQL-spezifische Version von COALESCE. Sie akzeptiert zwei Argumente; wenn das erste Argument NULL ist, gibt sie das zweite Argument zurück.

SELECT product_name, IFNULL(stock_quantity, 0) AS actual_quantity
FROM products;

4. NULL-Sortierung:

Beim Sortieren mit der ORDER BY-Klausel werden NULL-Werte standardmäßig ans Ende der Sortierung gesetzt. Wenn Sie NULL-Werte zuerst platzieren möchten, verwenden Sie ORDER BY column_name ASC NULLS FIRST; umgekehrt verwenden Sie ORDER BY column_name DESC NULLS LAST.

SELECT product_name, price
FROM products
ORDER BY price ASC NULLS FIRST;

5. Verwenden Sie den <=>-Operator für NULL-Vergleiche:

Der <=>-Operator ist ein spezieller Operator in MySQL, der verwendet wird, um zu vergleichen, ob zwei Ausdrücke gleich sind. Er gibt auch TRUE für Vergleiche mit NULL-Werten zurück. Er kann für Gleichheitsvergleiche mit NULL-Werten verwendet werden.

SELECT * FROM employees WHERE commission <=> NULL;

6. Beachten Sie, wie Aggregatfunktionen mit NULL umgehen:

Bei der Verwendung von Aggregatfunktionen (wie COUNT, SUM, AVG) ignorieren diese NULL-Werte, was zu unerwarteten Ergebnissen führen kann. Wenn Sie NULL als 0 behandeln möchten, können Sie COALESCE oder IFNULL verwenden.

SELECT AVG(COALESCE(salary, 0)) AS avg_salary FROM employees;

Auf diese Weise wird salary, selbst wenn es NULL ist, von der Aggregatfunktion als 0 behandelt.

Bei der Behandlung von NULL-Werten ist besonders darauf zu achten, dass die Semantik von Abfragen und Operationen den Erwartungen entspricht. Bei der Gestaltung von Tabellenstrukturen müssen Sie außerdem die Verwendungsszenarien und die Angemessenheit von NULL-Werten berücksichtigen.


Verwendung von NULL-Werten in der Eingabeaufforderung

Im folgenden Beispiel wird angenommen, dass die Tabelle example_test_tbl in der Datenbank EXAMPLE zwei Spalten hat, example_author und example_count, und dass NULL-Werte in example_count eingefügt werden.

Beispiel

Probieren Sie das folgende Beispiel aus:

Datentabelle example_test_tbl erstellen

root@Host# mysql -u root -p password; Geben Sie Passwort ein:******* mysql> use EXAMPLE; Datenbank geändert mysql> create table example_test_tbl -> ( -> example_author varchar(40) NOT NULL, -> example_count INT -> ); Abfrage OK, 0 Zeilen betroffen (0.05 sec) mysql> INSERT INTO example_test_tbl (example_author, example_count) values ('BEISPIEL', 20); mysql> INSERT INTO example_test_tbl (example_author, example_count) values ('Beispiel-Tutorial', NULL); mysql> INSERT INTO example_test_tbl (example_author, example_count) values ('Google', NULL); mysql> INSERT INTO example_test_tbl (example_author, example_count) values ('FK', 20); mysql> SELECT * from example_test_tbl; +---------------+--------------+ | example_author | example_count | +---------------+--------------+ | EXAMPLE | 20| | Beispiel-Tutorial |NULL | | Google | NULL | | FK | 20 | +---------------+--------------+ 4 Zeilen in Satz (0.01 sec)

Im folgenden Beispiel sehen Sie, dass die Operatoren = und != nicht wirksam sind:

mysql> SELECT * FROM example_test_tbl WHERE example_count = NULL; Leere Menge (0.00 sec) mysql> SELECT * FROM example_test_tbl WHERE example_count != NULL; Leere Menge (0.01 sec)

Um festzustellen, ob die Spalte example_test_tbl in der Datentabelle NULL ist, müssen SieIS NULLundIS NOT NULL, wie im folgenden Beispiel:

mysql> SELECT * FROM example_test_tbl WHERE example_count IS NULL; +---------------+--------------+ | example_author | example_count| +---------------+--------------+ | Beispiel-Tutorial |NULL | | Google | NULL | +---------------+--------------+ 2 Zeilen in Satz (0.01 sec) mysql> SELECT * from example_test_tbl WHERE example_count IS NOT NULL; +---------------+--------------+ | example_author | example_count | +---------------+--------------+ | EXAMPLE | 20 | | FK | 20 | +---------------+--------------+ 2 Zeilen in Satz (0.01 sec)

Verwendung von PHP-Skripten zur Behandlung von NULL-Werten

In PHP-Skripten können Sie if...else-Anweisungen verwenden, um festzustellen, ob eine Variable leer ist, und entsprechende bedingte Anweisungen zu erzeugen.

Im folgenden Beispiel setzt PHP die Variable $example_count und verwendet diese Variable dann zum Vergleich mit dem Feld example_count in der Datentabelle:

MySQL ORDER BY-Test:

<?php $dbhost = 'localhost'; //Hostadresse des MySQL-Servers $dbuser = 'root'; //MySQL-Benutzername $dbpass = '123456'; //Passwort für den MySQL-Benutzernamen $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('Verbindung fehlgeschlagen:' . mysqli_error($conn)); } //Kodierung festlegen, um chinesischen Zeichensalat zu vermeiden mysqli_query($conn , "set names utf8"); if( isset($example_count )) { $sql = "SELECT example_author, example_count FROM example_test_tbl WHERE example_count = $example_count"; } else { $sql = "SELECT example_author, example_count FROM example_test_tbl WHERE example_count IS NULL"; } mysqli_select_db( $conn, 'EXAMPLE' ); $retval = mysqli_query( $conn, $sql ); if(! $retval ) { die('Daten können nicht gelesen werden:' . mysqli_error($conn)); } echo '<h2>Beispiel-Tutorial IS NULL-Test<h2>'; echo '<table border="1"><tr><td>Autor</td><td>Anmeldeanzahl</td></tr>'; while($row = mysqli_fetch_array($retval, MYSQL_ASSOC)) { echo "<tr>". "<td>{$row['example_author']} </td> ". "<td>{$row['example_count']} </td> ". "</tr>"; } echo '</table>'; mysqli_close($conn); ?>

Das Ausgabeergebnis wird in der folgenden Abbildung gezeigt:

Weitere Erweiterungen