Hispanic 100 Hispanic 100 - 3 months ago 10
MySQL Question

Is it possible to have an empty MySQL numeric field?

I want to have an empty field on a MySQL Numeric field. If I define the field to allow a NULL value it defaults to 0.00. Sometimes for this row item I prefer no value. I could probably create a different table to track these few items, but at this point I prefer a one table solution.

Answer

Because you did this with DEFAULT. Don't do that:

create table t1
(   id int auto_increment primary key,
    thing varchar(100) not null,
    anInt NUMERIC(5,2) NULL DEFAULT 0
);

insert t1(thing) values ('fish');
select * from t1;
+----+-------+-------+
| id | thing | anInt |
+----+-------+-------+
|  1 | fish  |  0.00 |
+----+-------+-------+