not null usage in mysql

Question

Hi, I use syntax like name varchar(20) NOT NULL in mysql..i have a big confusion over here.

typically does it mean that this field is mandatory?

but when i store a space in this field it accepts it.is it correct. its like while insert i say '".$_POST['name']."'. even if the name does not have any value query is executed.

can any1 just clarify me on this ?

is NULL and blank space same ?

Answer 1

NULL and an empty string are not the same. You can store an empty string in a column that is defined as NOT NULL.

From the manual:

A common error when working with NULL is to assume that it is not possible to insert a zero or an empty string into a column defined as NOT NULL, but this is not the case. These are in fact values, whereas NULL means “not having a value.” You can test this easily enough by using IS [NOT] NULL as shown:

mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
+-----------+---------------+------------+----------------+
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
+-----------+---------------+------------+----------------+
|         0 |             1 |          0 |              1 |
+-----------+---------------+------------+----------------+

Thus it is entirely possible to insert a zero or empty string into a NOT NULL column, as these are in fact NOT NULL. See Section B.5.5.3, Problems with NULL Values.

Answer 2

NOT NULL means that the database requires some value to be entered. It's possible to define a default value in MySQL that will be inserted instead if you try to insert a NULL.

Keep in mind that a space or a blank string is different than NULL. NULL means that no value at all has been specified, not even blank.

Answer 3

NULL is actually "NULL" and not a blank space. You have to do something like name=NULL in order to set a field to null.

Answer 4

NULL is the absence of value - an unknown. A space, or even an empty string, is still something - a string.

Answer 5

Yes, it means that the value must be provided on insertion. However, MySQL treats empty strings and NULL differently. (And a good thing too, IMO.) From that document:

A common error when working with NULL is to assume that it is not possible to insert a zero or an empty string into a column defined as NOT NULL, but this is not the case. These are in fact values, whereas NULL means “not having a value.” You can test this easily enough by using IS [NOT] NULL as shown:

mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
+-----------+---------------+------------+----------------+
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
+-----------+---------------+------------+----------------+
|         0 |             1 |          0 |              1 |
+-----------+---------------+------------+----------------+

Thus it is entirely possible to insert a zero or empty string into a NOT NULL column, as these are in fact NOT NULL.

Answer 6

I'm not going to explain NULL to you again, the others have done that.

When doing SQL with PHP, please, always try to use PDO.

Example

$name = (!isset($_POST['name']) || empty($_POST['name']) ? NULL : $_POST['name'];

This says, if your $_POST is either not set, or empty, set the value of $name to NULL. Otherwise use $_POST['name'].

Now, when you bind $name in your prepared SQL statement, you will either have a strict NULL value or the string of the name.