Home arrow MySQL arrow Page 4 - Null and Empty Strings

Handling Null in MySQL - MySQL

Anyone who has programmed for any length of time has encountered the concepts of null and empty strings. They are not the same, and confusing the two can cause some serious problems. This article deals with these concepts in the context of PHP and MySQL.

TABLE OF CONTENTS:
  1. Null and Empty Strings
  2. Making Null Safe in PHP
  3. Null and MySQL
  4. Handling Null in MySQL
By: Shikhar Kumar
Rating: starstarstarstarstar / 13
December 02, 2008

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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!



 
 
>>> More MySQL Articles          >>> More By Shikhar Kumar
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: