In MySQL is it possible to have a NULL and a DEFAULT value inserted?

I'm trying to set up a table row 开发者_如何学编程that does not need a value (no error message if empty) but will insert a default value if empty, as in:

ALTER TABLE tablename ALTER rowname TINYINT UNSIGNED NOT NULL;

ALTER TABLE tablename ALTER rowname SET DEFAULT 0;

I set it up this way, but the value inserted is still NULL.

Is this even possible?

Thanks!

The NOT NULL clearly says the value for this column cannot be NULL. So there is no NULL value used for the column. But it can hold the integer value 0 as it is a valid integer value.

It is a bit strange. Given this table definition:

create table def ( i int not null default 0 );

This statement works:

insert into def () values ();
select * from def;

+---+
| i |
+---+
| 0 |
+---+

But this statement generates an error:

insert into def values( null );
ERROR 1048 (23000): Column 'i' cannot be null

So it seems that "not null default 0" will supply the default of 0 when the column is not explicitly assigned a value (which would normally be assigned a NULL), but does not allow NULL to be explicitly assigned because of the NOT NULL attribute.

Are you sure that the value being inserted into the database is actually NULL, and that your programming language isn't just disregarding type and treating a 0 in the row as equivalent to NULL? If your column is defined as NOT NULL, MySQL shouldn't even be allowing NULL at all, let alone use it as the default.