Using null in database

[piano0011]

Hey guys!

I am really enjoying learning about database and php. Recently, I have been learning on how to use null and undefined and found it easier to get my database to work if i set to null for any unknown values. Just like to know if this is a common thing to do in database design?

In hindsight, it was weird that for one of my tables, i couldn't get it to work when assining variables to it... i had around 12 or 15 variables

[requinix]

NULL means a lack of data. It is not a value by itself but used in places where you do not have the correct value.

Something "unknown" should often use an appropriate default value, though if it's really unknown you should consider whether you even store anything in the database at all. But yes, sometimes an unknown value can be left at NULL if you know you are going to fill it in later.

[piano0011]

Thank! Appreciate it 

[Barand]

You need to be aware of the implications of using null values instead of default such as 0 or "";

if we have this data

mysql> select * from user;
+---------+----------+----------+
| user_id | username | user_val |
+---------+----------+----------+
|       1 | Peter    |        2 |
|       2 | Paul     |     NULL |
|       3 | Mary     |       10 |
+---------+----------+----------+

Using functions on null values gives null results

mysql> SELECT username
    ->      , user_val
    ->      , CONCAT(username, user_val) as calc
    -> FROM user;
+----------+----------+--------+
| username | user_val | calc   |
+----------+----------+--------+
| Peter    |        2 | Peter2 |
| Paul     |     NULL | NULL   |
| Mary     |       10 | Mary10 |
+----------+----------+--------+

mysql> SELECT  username
    ->       , user_val
    ->       , user_val * 10 as calc
    -> FROM user;
+----------+----------+------+
| username | user_val | calc |
+----------+----------+------+
| Peter    |        2 |   20 |
| Paul     |     NULL | NULL |
| Mary     |       10 |  100 |
+----------+----------+------+

Nulls are ignored by aggregation functions

mysql> SELECT SUM(user_val) as total FROM user;
+-------+
| total |
+-------+
|    12 |
+-------+

mysql> SELECT COUNT(user_val) as count FROM user;
+-------+
| count |
+-------+
|     2 |
+-------+

But this can sometimes. distort averages. Three records with a total of 12, so you might expect an average of 4.

mysql> SELECT AVG(user_val) as average FROM user;
+---------+
| average |
+---------+
|  6.0000 |
+---------+

To get the expected '4' you would need to treat nulls as zero

mysql> SELECT AVG( IFNULL(user_val, 0) ) as average FROM user;
+---------+
| average |
+---------+
|  4.0000 |
+---------+

 

[piano0011]

If there are variables that i don't need to use now but later, should i use null or undefined? If i have a loginsystem and some of my variables like activate should be defined as 0, can i not use this in the query until needed?

[Barand]

null is undefined

If it's not needed why would you want to include it in the query?

If they are either activated or not, define column "activate" as

activate tinyint not null default 0

When you insert a new record it will be set to 0 by default even though there is no mention of it in the INSERT query. You can start using when you need to.

If it's column that you really don't have value for yet, such as "datecancelled" then

datecancelled date null

Any column not explicitly mentioned in an INSERT will get its default value. If defined as NOT NULL, and there is no default, you will get an error if you don't provide a value..