MySQL NULL and NOT NULL?

[AthlonInside]

I use PHPMyAdmin and I have a field define as 'not null'. But how come when I leave that field bank when inserting data, MySQL didn't complaint? It just let it empty?

So what is the actual use for NULL and NOT NULL?

[senior mcinvale]

a blank value is not null, so that behavior is expected.

null is non-existant, a blank value is something.

[Fischerlaender]

If your "not null" field is a text type (TEXT, VARCHAR, ..) then it's not null, but empty. So MySQL doesn't complain, because it cannot distinguish between empty and null.

If you define an integer (or any other number type) field "not null", then MySQL would complain, if you leave it without any value. (If set a default value for your "not null" field, then MySQL wouldn't complain either.)

[AthlonInside]

Good, I think I understand now. Thanks.

[senior mcinvale]

"because it cannot distinguish between empty and null."

SELECT * FROM table WHERE field IS NULL;

that will only select NULL fields and leave blank fields, mySQL can distinguish between empty & null just fine.

[Fischerlaender]

senior_mcinvale,
of course you are right. I was misled by some surprising behaviour of MySQL.

I did the following:

mysql> CREATE TABLE test ( 
id int(11) NOT NULL default '0', 
text varchar(255) NOT NULL 
) TYPE=MyISAM; 
Query OK, 0 rows affected (0.00 sec) 
mysql> INSERT INTO test SET id=1; 
Query OK, 1 row affected (0.00 sec) 
mysql> SELECT * FROM test WHERE text IS NULL; 
Empty set (0.00 sec)

So, the field 'text' should be NULL, because there was no insert. Obviously, MySQL inserts an empty string, even without a default value for this field, when the the field is defined as NOT NULL.