Handling Null in MySQL - MySQL

We will enter a null value to make things more clear. The query for that would be as follows:

// before inserting a value alter the column to accept null values

ALTER TABLE `example` CHANGE `name` `name` VARCHAR( 20 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL

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

mysql> select * from example;

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

| id | name |

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

| 2 | |

| 3 | |

| 4 | NULL |

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

3 rows in set (0.00 sec)

Now, suppose, you want to select all of the tuples which have null values. The query for this would be as follows:

Select * from example where name is null ;

This will return id = 4

mysql> Select * from example where name is null ;

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

| id | name |

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

| 4 | NULL |

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

1 row in set (0.03 sec)

Select * from example where name = '' ;

This will return id = 1,2

mysql> Select * from example where name = '' ;

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

| id | name |

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

| 2 | |

| 3 | |

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

2 rows in set (0.00 sec)

In any practical case, you would want to get all three values because they are all empty. For this you will have to use the following query:

Select * from example where name is null OR name = '';

mysql> Select * from example where name is null OR name = '';

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

| id | name |

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

| 2 | |

| 3 | |

| 4 | NULL |

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

3 rows in set (0.00 sec)

And this code becomes a safe way for SQL to handle results when you are not sure what you are handling, null or empty string.

Before I finish, there's a "gotcha" I would like to mention. Let's insert one more row with id as NULL:

mysql> INSERT INTO `example` (`id`, `name` ) values ('', 'finalrow');

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from example where id IS NULL;

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

| id | name |

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

| 5 | finalrow |

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

1 row in set (0.00 sec)

Now, this looks weird but there's an explanation for it. For the benefit of some ODBC applications (at least Delphi and Access), the following query can be used to find a newly inserted row:

SELECT * FROM tbl_name WHERE auto IS NULL;

--http://dev.mysql.com/doc/mysql/en/ODBC_and_last_insert_id.html

All further executions of the same statement provide the expected result:

mysql> select * from example where id IS NULL;

Empty set (0.00 sec)

I hope you now understand the difference between null and an empty string, and how to safely handle them in PHP and MySQL. Good luck!