Home arrow MySQL arrow Page 3 - Null and Empty Strings

Null and 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

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.



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