Re: How to assign NULL instead of 0 for numeric INSERTs?

Dizzledorf wrote:
> Hi,
>
>
> My PHP application does a mass INSERT of about 3 dozen fields, many of
> them numeric. After I run the INSERT, however, MySQL places a bunch
> of "0"s (zeroes) in every numeric field, even if a null value was
> passed.
>
> I don't want to convert these fields to VARCHARs... so can I:
> - force a NULL insert
> - change default NULL insert behavior (i.e. don't put a 0 in!)
> or
> - easily run a post-INSERT one-line SQL statement to change all "0"
> field values to null?
>
> (MySQL 4.1.7)
>
>
> Thanks,
> DIZZLE

I just tried entering NULL into an INT field a DECIMAL field and a FLOAT
field and the contents were always NULL.
You will most likely find that the field is set up with a default value of 0
and it may also be defined as not null.
You can change these settings with an ALTER TABLE command

> > My PHP application does a mass INSERT of about 3 dozen fields, many of
> > them numeric. After I run the INSERT, however, MySQL places a bunch
> > of "0"s (zeroes) in every numeric field, even if a null value was
> > passed.
> >
> > I don't want to convert these fields to VARCHARs... so can I:
> > - force a NULL insert
> > - change default NULL insert behavior (i.e. don't put a 0 in!)
> > or
> > - easily run a post-INSERT one-line SQL statement to change all "0"
> > field values to null?
> >
> > (MySQL 4.1.7)
> >
> >
> > Thanks,
> > DIZZLE
>
> I just tried entering NULL into an INT field a DECIMAL field and a FLOAT
> field and the contents were always NULL.
> You will most likely find that the field is set up with a default value of
0

A "default" only applies if no value is supplied. NULL is considered valid.

> and it may also be defined as not null.

In MySQL, this might be the thing and it will change your data silently.
Oh, the horror.

> You can change these settings with an ALTER TABLE command


--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com