MySQL a NULL
Czym jest NULL? Co to za wartość? Jakie ma cechy? Jak zachowuje się w zapytaniach? Czy wartość NULL ma jakąkolwiek wartość? Czym NULL różni się od zera? Czy różni się w ogóle? Wbrew pozorom, te pytania nie są rzadkością – odpowiedź na nie potrafi być problemem także dla administratorów i programistów z dłuższym stażem obcowania z MySQL. Jak to więc z NULL jest?
Logicznie rzecz biorąc, NULL to nic. Nic to z kolei zero. Niestety, to by było na tyle logiki, bo MySQL wcale nie planuje się do niej stosować (podobnie zresztą jak i inne systemy RDBMS). W przypadku MySQL NULL oznacza nieznaną wartość. W efekcie nie da się porównać tej wartości z czymkolwiek innym (no bo jej nie znamy). Dlatego też każdy wynik porównania NULL z czymkolwiek zwróci wartość NULL – to akurat jest logiczne: efekt porównania czegoś z nieznanym będzie nieznany.
mysql> SELECT NULL < 1, NULL > 0, NULL = 10, NULL <> 53; +----------+----------+-----------+------------+ | NULL < 1 | NULL > 0 | NULL = 10 | NULL <> 53 | +----------+----------+-----------+------------+ | NULL | NULL | NULL | NULL | +----------+----------+-----------+------------+ 1 row in set (0.00 sec)
Do sprawdzania czy dana wartość jest NULL służą dwa operatory: IS NULL i IS NOT NULL.
mysql> SELECT 5 IS NULL, 3 IS NOT NULL, NULL IS NULL, NULL IS NOT NULL; +-----------+---------------+--------------+------------------+ | 5 IS NULL | 3 IS NOT NULL | NULL IS NULL | NULL IS NOT NULL | +-----------+---------------+--------------+------------------+ | 0 | 1 | 1 | 0 | +-----------+---------------+--------------+------------------+ 1 row in set (0.00 sec)
Jak widać, 5 nie jest nieznana, wartość 3 jest także. NULL to oczywiście NULL.
Ok, jak ma się NULL do wartości pustych?
mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL; +-----------+---------------+------------+----------------+ | 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL | +-----------+---------------+------------+----------------+ | 0 | 1 | 0 | 1 | +-----------+---------------+------------+----------------+ 1 row in set (0,00 sec)
Jak widać, zarówno wartość zero jak i pusty string nie są NULL. W końcu znamy ich wartość – zero i nic. To oczywiście przekłada się na obostrzenia NOT NULL dla danej kolumny. Zakładamy taką tabelę:
mysql> SHOW CREATE TABLE tab_not_null\G *************************** 1. row *************************** Table: tab_not_null Create Table: CREATE TABLE `tab_not_null` ( `a` int(10) NOT NULL, `b` varchar(10) COLLATE utf8_polish_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci 1 row in set (0.00 sec)
Próbujemy wykonać INSERTy:
mysql> INSERT INTO tab_not_null VALUES (0,''); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO tab_not_null (a) VALUES (0); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +---------+------+----------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------+ | Warning | 1364 | Field 'b' doesn't have a default value | +---------+------+----------------------------------------+ 1 row in set (0.00 sec)
Pierwszy oczywiście wykonuje się poprawnie, w końcu 0 i ‘’ nie są NULL. Drugi INSERT dodaje rekord z wartością zdefiniowaną tylko dla kolumny ‘a’. MySQL zgłasza to w warningu, ale wartość dla kolumny b ustawia na wartość pustą i INSERT jest wykonany. W efekcie zawartość tabeli wygląda następująco:
mysql> SELECT * FROM tab_not_null; +---+---+ | a | b | +---+---+ | 0 | | | 0 | | +---+---+ 2 rows in set (0.00 sec)
NULL może być ustawione także jako wartość domyślna dla kolumny. Załóżmy taką tabelę:
mysql> SHOW CREATE TABLE tab_null\G *************************** 1. row *************************** Table: tab_null Create Table: CREATE TABLE `tab_null` ( `a` int(10) DEFAULT NULL, `b` varchar(10) COLLATE utf8_polish_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci 1 row in set (0.00 sec)
W tej sytuacji wynik tych samych INSERTów co poprzednio wyglądać będzie następująco:
mysql> INSERT INTO tab_null VALUES (0,''); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO tab_null (a) VALUES (0); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM tab_null; +------+------+ | a | b | +------+------+ | 0 | | | 0 | NULL | +------+------+ 2 rows in set (0.00 sec)
Wartość domyślna jest ustawiona, więc ostrzeżenie się nie pojawia – za to kolumna ‘b’ przyjmuje wartość domyślną NULL. Oczywiście, możemy także samodzielnie dodać rekord z wartościami NULL:
mysql> INSERT INTO tab_null VALUES (NULL, NULL); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM tab_null; +------+------+ | a | b | +------+------+ | 0 | | | 0 | NULL | | NULL | NULL | +------+------+ 3 rows in set (0.00 sec)
Jak sobie zobrazować różnicę pomiędzy NULL a pustą wartością? Dajmy na to adres email. Jeśli wartość kolumny ‘email’ w danym rekordzie jest pusta, oznacza to tyle, że np. dany użytkownik nie posiada adresu email. Jeśli wartość kolumny ‘email’ to NULL, oznaczałoby to że nie wiemy czy dany użytkownik posiada adres email.
W SQL wyrażenia, które zawierają w sobie NULL zawsze zwracają nieznany wynik. Przykładem mogą być takie operacje:
mysql> SELECT NULL, 5+NULL, CONCAT('tekst',NULL), FROM_UNIXTIME(NULL);
+------+--------+----------------------+---------------------+
| NULL | 5+NULL | CONCAT('tekst',NULL) | FROM_UNIXTIME(NULL) |
+------+--------+----------------------+---------------------+
| NULL | NULL | NULL | NULL |
+------+--------+----------------------+---------------------+
1 row in set (0.00 sec)
To samo będzie w przypadku użycia wyrażenia z NULL w warunku WHERE:
mysql> SELECT * FROM tab_null; +------+------+ | a | b | +------+------+ | 0 | | | 0 | NULL | | NULL | NULL | +------+------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM tab_null WHERE b=NULL; Empty set (0.00 sec)
Aby sprawdzić, które rekordy zawierają NULL w kolumnie ‘b’ konieczne jest zastosowanie operatora IS NULL. Analogicznie, aby sprawdzić które nie zawierają NULL wykorzystujemy IS NOT NULL:
mysql> SELECT * FROM tab_null WHERE b IS NULL; +------+------+ | a | b | +------+------+ | 0 | NULL | | NULL | NULL | +------+------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM tab_null WHERE b IS NOT NULL; +------+------+ | a | b | +------+------+ | 0 | | +------+------+ 1 row in set (0.00 sec)
Jeśli chcemy wyciągnąć informację o rekordach z pustą kolumną ‘b’ wystarczy oczywiście standardowe zapytanie:
mysql> SELECT * FROM tab_null WHERE b=''; +------+------+ | a | b | +------+------+ | 0 | | +------+------+ 1 row in set (0.00 sec)
Jeśli stosujemy funkcje agregujące kolumny typu COUNT(), MAX(), SUM(), MIN() itp. ignorują one rekordy z wartością NULL. Odstępstwem jest COUNT(*) – tak wywołana zlicza wszystkie rekordy a nie wartości występujące w kolumnie. Przykładowo:
mysql> SELECT * FROM tab_null; +------+------+ | a | b | +------+------+ | 0 | | | 0 | NULL | | NULL | NULL | +------+------+ 3 rows in set (0.00 sec) mysql> SELECT COUNT(a), COUNT(b), COUNT(*) FROM tab_null; +----------+----------+----------+ | COUNT(a) | COUNT(b) | COUNT(*) | +----------+----------+----------+ | 2 | 1 | 3 | +----------+----------+----------+ 1 row in set (0.00 sec)
W przypadku zastosowania sortowania NULL traktowana jest jako minus nieskończoność – będzie na początku gdy sortujemy rosnąco i na końcu, gdy sortujemy malejąco.
mysql> SELECT * FROM tab_null ORDER BY b ASC; +------+------+ | a | b | +------+------+ | 0 | NULL | | NULL | NULL | | 0 | | +------+------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM tab_null ORDER BY b DESC; +------+------+ | a | b | +------+------+ | 0 | | | 0 | NULL | | NULL | NULL | +------+------+ 3 rows in set (0.00 sec)
Innymi ciekawymi cechami NULL jest np. to, że jeśli do kolumny o typie TIMESTAMP wrzucimy wartość NULL, to w efekcie dodaną do tabeli wartością tej kolumny będzie aktualna data i czas.
Powyższe jest prawdziwe tylko dla tabel tworzonych w sposób następujący:
CREATE TABLE tab (a INT, b TIMESTAMP); W takiej sytuacji MySQL domyślnie dodaje DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP do definicji kolumny b: mysql> SHOW CREATE TABLE tab\G *************************** 1. row *************************** Table: tab Create Table: CREATE TABLE `tab` ( `a` int(11) DEFAULT NULL, `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin2 1 row in set (0,00 sec)
Nie można zakładać że tak będzie zawsze, bo można także jako wartość domyślną dla kolumny TIMESTAMP ustawić NULL. Wtedy tego typu zastąpienie NULL konkretną aktualnym czasem nie ma miejsca:
mysql> CREATE TABLE tab (a INT, b TIMESTAMP NULL); Query OK, 0 rows affected (0,02 sec) mysql> INSERT INTO tab VALUES (1, NULL); Query OK, 1 row affected (0,00 sec) mysql> drop table tab; Query OK, 0 rows affected (0,00 sec) mysql> CREATE TABLE tab (a INT, b TIMESTAMP NULL); Query OK, 0 rows affected (0,04 sec) mysql> SHOW CREATE TABLE tab\G *************************** 1. row *************************** Table: tab Create Table: CREATE TABLE `tab` ( `a` int(11) DEFAULT NULL, `b` timestamp NULL DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin2 1 row in set (0,00 sec) mysql> INSERT INTO tab VALUES (1, NULL); Query OK, 1 row affected (0,00 sec) mysql> SELECT * FROM tab; +------+------+ | a | b | +------+------+ | 1 | NULL | +------+------+ 1 row in set (0,00 sec)
Dziękuję Marcie za zwrócenie uwagi na to zachowanie.
Jeśli dodamy NULL do kolumny, która jest autoinkrementowana, wstawiana jest kolejna z rzędu wartość. W przypadku GROUP BY, ORDER BY czy DISTINCT wszystkie wartości NULL traktowane są jako identyczne.
Mam nadzieję, że tą serią postów trochę przybliżyłem specyficzne zjawisko, jakim jest w SQL wartość NULL. Te dwa posty tu dopiero początek na drodze ku poznaniu. Ciekawe i nie koniecznie logicznie przewidywalne rzeczy zaczynają się dziać w momencie gdy NULL pojawia się w JOINach. Może w przyszłości trafi to na ten blog, na razie zainteresowanych zapraszam do dokumentacji MySQL i do podręczników opisujących SQL.
Źródło
Aby dodawać komentarze musisz być zalogowany!