programmera.net -> mysql -> normal     för utskrift      info@programmera.net

NULL i MySQL

1. NULL
2. NULL i uttryck
3. NULL i aggregeringsfunktioner
4. NULL och DEFAULT
5. Att tilldela NULL explicit

1. NULL

På denna sida beskrivs hur värdet NULL fungerar i olika situationer. NULL är ett speciellt värde som inte har någon typ, alla datatyper kan innehålla NULL (om de inte är satta till NOT NULL).

  • NULL kan skrivas med små bokstäver, som null eller Null.
  • NULL skrivs utan fnuttar ("), "NULL" och 'NULL' är alltså vanliga strängar som innehåller text och har inget med NULL att göra.
Det är lätt att man blandar ihop värdet 0 eller tomma strängen med NULL, men NULL skiljer sig från 0 och tomma strängen på flera sätt. 0 och tomma strängen räknas som värden emedan NULL representerar frånvaro av ett värde. NULL ska tolkas som att databasen avsiktligt eller oavsiktligt saknar ett värde för kolumnen, därför ska de flesta vanliga operationer man vill göra på NULL misslyckas, eftersom man omöjligen kan vilja utföra en operation på ett värde som inte existerar.

2. NULL i uttryck

I många fall returnerar ett uttryck NULL

  1. Aritmetiska uttryck returnerar NULL om de innehåller NULL.
  2. De vanliga jämförelseoperatorerna (=, >, < osv.) returnerar NULL då de har NULL på höger eller vänster sida.
  3. Det finns speciella jämförelseoperatorer som hanterar NULL (<=>, ISNULL()), dessa returnerar ej NULL.
  4. Många funktioner returnerar NULL då de får NULL som invärde.
Nu några exempel: 
mysql> SELECT NULL+2, NULL/4, 1/0;
+--------+--------+------+
| NULL+2 | NULL/4 | 1/0  |
+--------+--------+------+
|   NULL |   NULL | NULL |
+--------+--------+------+
1 row in set (0.01 sec)

SELECT NULL=2, NULL=NULL, 3<NULL;
+--------+-----------+--------+
| NULL=2 | NULL=NULL | 3<NULL |
+--------+-----------+--------+
|   NULL |      NULL |   NULL |
+--------+-----------+--------+
1 row in set (0.01 sec)

SELECT NULL<=>NULL, NULL<=>1, 1<=>1;
+-------------+----------+-------+
| NULL<=>NULL | NULL<=>1 | 1<=>1 |
+-------------+----------+-------+
|           1 |        0 |     1 |
+-------------+----------+-------+
1 row in set (0.00 sec)

3. NULL i aggregeringsfunktioner

Aggregering (som beskrivs  här ) utförs med aggregeringsfunktioner som t.ex. COUNT(), SUM(), AVG(). NULL fungerar bra tillsammans med aggregeringsfunktioner eftersom aggregeringsfunktioner hoppar över NULL i sina beräkningar. Alternativet att använda ett defaultvärde är inte så lockande, låt oss se på ett exempel:

  • Vi har en tabell "employee" med anställda (samma tabell som används i exemplen på sidan om  aggregering ). Varje anställd har en lön som listas i kolumnen "salary". Låt oss säga att utvecklaren Olle är nyanställd och inte har någon lön ännu, men finns ändå med i databasen. Om kolumnen "salary" tillåter NULL kan vi sätta lönen till NULL. Om vi nu till exempel vill beräkna medelvärdet på lönerna på de olika avdelningarna så kommer metoden AVG(salary) att hoppa över Olles lön i beräkningen, och medelvärdet blir korrekt. Vi ser nedan att även COUNT(salary) hoppar över Olles lön, och räknar endas 4 löner på utvecklingsavdelningen.

mysql> UPDATE employee SET salary=NULL where name="Olle";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM employee;
+----+-------------+--------+---------------+--------+
| id | department  | name   | title         | salary |
+----+-------------+--------+---------------+--------+
|  1 | Management  | Magnus | President     | 100000 |
|  2 | Management  | Lisa   | Secretary     |  30000 |
|  3 | Sales       | Jan    | Sales Manager |  82000 |
|  4 | Sales       | Thomas | Sales         |  73000 |
|  5 | Sales       | Marcus | Accountant    |  58000 |
|  6 | Development | Jurgen | CTO           |  65000 |
|  7 | Development | Tobbe  | Programmer    |  54000 |
|  8 | Development | Olle   | Programmer    |   NULL |
|  9 | Development | Sven   | Programmer    |  43000 |
| 10 | Development | Jocke  | Programmer    |  47000 |
+----+-------------+--------+---------------+--------+
10 rows in set (0.00 sec)

mysql> SELECT department, COUNT(salary), AVG(salary) FROM employee GROUP BY department;
+-------------+---------------+-------------+
| department  | COUNT(salary) | AVG(salary) |
+-------------+---------------+-------------+
| Development |             4 |  52250.0000 |
| Management  |             2 |  65000.0000 |
| Sales       |             3 |  71000.0000 |
+-------------+---------------+-------------+
3 rows in set (0.03 sec)
Om kolumnen "salary" däremot deklarerad som NOT NULL kommer Olles lön troligtvis att få värdet 0 (som är det automatiska defaultvärdet för typen INT). I exemplet ovan kommer medelvärdet för utvecklingsavdelningen att bli felaktigt, eftersom Olles lön kommer med i beräkningen och förskjuter medelvärdet nedåt.

4. NULL och DEFAULT

Om man inte skriver ett explicit värde för en kolumn i en INSERT-sats lagras ett defaultvärde. De olika datatyperna följer följande regler när det gäller defaultvärden:

  1. En kolumn som har ett explicit defaultvärde lagrar defaultvärdet.
  2. En kolumn som ej har ett explicit defaultvärde och som kan innehålla NULL, lagrar NULL.
  3. En kolumn som ej har ett explicit defaultvärde och som ej kan innehålla NULL, lagrar 0 elle tomma strängen.
Vi testar defaultvärden för olika fall: 
mysql> CREATE TABLE default_table(
	int_col1 INT DEFAULT 17,
  	int_col2 INT,
	int_col3 INT NOT NULL,
	text_col1 VARCHAR(100) DEFAULT 'foo',
	text_col2 VARCHAR(100),
	text_col3 VARCHAR(100) NOT NULL,
	date_col1 DATE DEFAULT "2004-01-01",
	date_col2 DATE,
	date_col3 DATE NOT NULL
);
Query OK, 0 rows affected (0.05 sec)
    
INSERT INTO default_table VALUES ();
Query OK, 1 row affected (0.02 sec)

SELECT * FROM default_table\G
*************************** 1. row ***************************
 int_col1: 17
 int_col2: NULL
 int_col3: 0
text_col1: foo
text_col2: NULL
text_col3:
date_col1: 2004-01-01
date_col2: NULL
date_col3: 0000-00-00
1 row in set (0.00 sec)
Ovan ser vi att:
  • int_col3 tilldelas värdet 0 eftersom kolumnen är en numerisk datatyp.
  • text_col3 tilldelas "tomma strängen" eftersom kolumnen är en texttyp.
  • date_col3 tilldelas värdet 0 eftersom kolumnen är en datumtyp.

5. Att tilldela NULL explicit

Om man explicit försöker lagra NULL i en textkolumn har vi följande fall:

  1. Om en kolumn kan innehålla NULL sätts den till NULL.
  2. Om en NOT NULL-kolumn sätts till NULL i en UPDATE får kolumnen värdet 0, och en varning läggs till på slutet.
  3. Om en NOT NULL-kolumn sätts till NULL i en INSERT genereras ett fel och satsen abryts.
Vi testar att göra INSERT med NULL: 
mysql> CREATE TABLE number_table(
    -> int_col1 INT,
    -> int_col2 INT DEFAULT 17,
    -> int_col3 INT NOT NULL
);
	
INSERT INTO number_table (int_col1, int_col2, int_col3) VALUES(NULL, NULL, NULL);
ERROR 1048: Column 'int_col3' cannot be null
Här ser vi att det är först vid "int_col3" då MySQL protesterar. Nu ska vi se om det går att göra UPDATE med NULL på den kolumnen: 
mysql> UPDATE number_table SET int_col3=NULL;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

SELECT int_col3 FROM number_table;
+----------+
| int_col3 |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
Här kommer vi undan med en varning.