Non null usage in mysql

Question

I am using syntax like name varchar(20) NOT NULL in mysql .. I have a lot of confusion here.

Does this usually mean that this field is required?

but when I store a space in this field, it takes it. It is right. its like during insertion, I say '".$_POST['name']."' . even if the name has no query value.

Can anyone clarify me on this?

is null and empty?

Answer 1

I will no longer explain NULL, others have done it.

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

Example

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

This suggests that 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 a prepared SQL statement, you will have either a NULL string value or a name string.

Answer 2

NULL , and an empty string is not the same. You can save an empty row in a column that is defined as NOT NULL .

From the manual :

A common mistake when working with NULL is to assume that it is not possible to insert a null or empty row in a column defined as NOT NULL, but it is not. These are actually values, while NULL means "irrelevant." You can verify this fairly easily 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 possible to insert a null or empty row into NOT NULL columns, since they are actually NOT NULL. See Section B.5.5.3, Problems with NULL Values .

Answer 3

NOT NULL means that the database requires some value. You can define a default value in MySQL that will be inserted instead if you try to insert NULL.

Keep in mind that a space or empty string is different from NULL. NULL means that no value is specified at all, not even empty.

Answer 4

Yes, this means that the value must be specified when inserting. However, MySQL handles empty strings and NULL differently . (And also good, IMO.) From this document:

A common mistake when working with NULL is to assume that it is not possible to insert a null or empty row in a column defined as NOT NULL, but this is not the case. They are in fact values, while NULL means "no meaning." You can verify this fairly easily using IS [NOT] NULL as shown below:

 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 possible to insert a null or empty row into NOT NULL columns, since they are actually NOT NULL.

Answer 5

NULL is actually " NULL ", not a space. You should do something like name=NULL to set the null field.

Answer 6

NULL - this lack of value - is unknown. Space or even an empty string is still something - a string.