not null usage in mysql

Question

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

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.