Null and MySQL - MySQL

Now let's look at null in the context of MySQL. The principles are the same here; only the application differs.

Here, NULL means "a missing unknown value" and is treated somewhat differently from other values. To test for NULL, you cannot use the arithmetic comparison operators such as =, <, or <>. So, for handling null you are left with only is null and is not null operators.

mysql> select '' IS NULL, '' IS NOT NULL;

+------------+----------------+

| '' IS NULL | '' IS NOT NULL |

+------------+----------------+

| 0 | 1 |

+------------+----------------+

So by definition NULL is just NULL , and anything which you might enter into the column is not NULL, including the empty string ( "").

I discuss the possible cases where you might run into problems next. We'll start with an example table for the discussion. 

Table structure for example :

CREATE TABLE `example` (

`id` int(2) NOT NULL auto_increment,

`name` varchar(20) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

The way this code is written, it's very possible to enter an empty string into the table rather than null. In the above example table, if you execute following query:

$query = "INSERT INTO `example` ( `id` , `name` )
VALUES (

'', '$var') ";

If $name is empty, you have just entered an empty string into the table and not a null value. The updated table looks like this:

mysql> select * from example;

+----+------+

| id | name |

+----+------+

| 2 | |

+----+------+

1 row in set (0.00 sec)

Again, taking the above table for our example, suppose you execute the following:

$query = "INSERT INTO `example`(`id`) values('') " ;

Here what goes into the "name" column depends on the SQL mode in effect.

• If a strict SQL mode is not enabled, MySQL sets the column to the implicit default value for the column data type.

• If a strict mode is enabled, an error occurs for transactional tables and the statement is rolled back. For non-transactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.

Implicit defaults for a non-strict SQL mode for MySQL datatypes are as follows:

• For numeric types, the default is 0, with the exception that for integer or floating-point types declared with the AUTO_INCREMENT attribute, the default is the next value in the sequence.

• For date and time types other than TIMESTAMP, the default is the appropriate "zero" value for the type. For the first TIMESTAMP column in a table, the default value is the current date and time.

• For string types other than ENUM, the default value is the empty string. For ENUM, the default is the first enumeration value.

• BLOB and TEXT columns cannot be assigned a default value.
  

Most probably your SQL mode will not be strict, as by default the strict mode is not on. So, in this case you again entered an empty string and not a null value!

The following are the updated values:

mysql> select * from example;

+----+------+

| id | name |

+----+------+

| 2 | |

| 3 | |

+----+------+

2 rows in set (0.00 sec)

In both of the cases above we have entered empty strings, which may be confused with null. To make matters worse, we may actually enter null values in the column later.