Many apologies Mikel for the double reply. I didn't include slug in the response
last time.
An extract from the MySQL manual under the LOAD DATA INFILE section:
-- SNIP --
An empty field value is interpreted differently than if the field value is missing:
* For string types, the column is set to the empty string.
* For numeric types, the column is set to 0.
* For date and time types, the column is set to the appropriate ``zero'' value
for the type. See section 6.2.2 Date and Time Types.
Note that these are the same values that result if you assign an empty string
explicitly to a string, numeric, or date or time type explicitly in an INSERT or
UPDATE statement.
-- SNIP --
A script to parse the data file sounds like the way to go.
Fil
Quoting James Gregory <[EMAIL PROTECTED]>:
> On Mon, 2003-01-13 at 17:24, Michael Lake wrote:
> > Hi all,
> >
> > This is a bit OT but I am trying to fix up some MySQL data on a Linux
> > system and having just one problem
> > Numeric types that have no entry end up as 0.00000 rather than NULL when
> > imported into the tables.
>
> this is kinda a guess since I distance myself from mysql these days.
> But, isn't it correct behaviour for mysql to convert the empty string to
> something that isn't null? You're specifying a value there, not telling
> it that there is no value. I would assume that it's giving you something
> that is non-null because you're specifying a value for that column. The
> default will only apply when that column isn't specified. I think the
> solution is to run a script beforehand that replaces "" with "\N" or
> whatever null is in mysql. That or use some zany conditional when you're
> inserting.
>
> HTH
>
> James.
>
> --
> SLUG - Sydney Linux User's Group - http://slug.org.au/
> More Info: http://lists.slug.org.au/listinfo/slug
>
-------------
Phil Scarratt
It Consultant
0403 531 271
--
SLUG - Sydney Linux User's Group - http://slug.org.au/
More Info: http://lists.slug.org.au/listinfo/slug
