Jump to content
piano0011

Using null in database

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 |
+---------+

 

  • Like 1

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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..

Share this post


Link to post
Share on other sites

If you update or insert your database field than you have to need set some value of a particular field. If you don't want to change your particular field to update or add then you must be remove it from your query.

you can use this field than you  need to assign a default value for ex $variable_name  = '';

If you don't assign any value then you must need to define field must be a NULL on a database table.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.