List:General Discussion« Previous MessageNext Message »
From:Fred G Date:August 6 2012 6:20pm
Subject:Re: DECIMAL datatype automatically makes blank become 0
View as plain text  
Thanks! I'll create a temp table to handle it as well.

On Mon, Aug 6, 2012 at 2:13 PM, Stillman, Benjamin <
BStillman@stripped> wrote:

> Meant to add:
>
> My workaround was to set the column as varchar then convert it to decimal
> after the import. When adding to the table, I use a temp table to import to
> as varchar, convert the column, the just select from temp table into prod
> table.
>
>
>
> -----Original Message-----
> From: Stillman, Benjamin [mailto:BStillman@stripped]
> Sent: Monday, August 06, 2012 2:05 PM
> To: 'Fred G'
> Cc: mysql@stripped
> Subject: RE: DECIMAL datatype automatically makes blank become 0
>
> I don't believe this is limited to Workbench. I saw the same behavior
> using LOAD INFILE and a pipe-delimited file. There's a bug report that's
> been open since November of 2006 about this:
> http://bugs.mysql.com/bug.php?id=23212
>
>
> -----Original Message-----
> From: Fred G [mailto:bayespokerguy@stripped]
> Sent: Monday, August 06, 2012 9:48 AM
> To: Hassan Schroeder
> Cc: mysql@stripped
> Subject: Re: DECIMAL datatype automatically makes blank become 0
>
> Thanks for the response.  I'm not sure that is exactly what is happening
> for me.
>
> I tried an example where I created a dummy database and a dummy table. If
> I create a column with decimal datatype and insert 2 different rows, one
> blank and one null, they both are treated as nulls.  This is what I would
> like, but it does not work this way when I try to import a csv.
>
> When I try to import a csv file with either null or blank values for a
> decimal datatype, they both get treated as 0.00 (if we are using
> DECIMAL(12,2)).
>
> I think the issue has to do with the way MySQL 5.2 Workbench imports csv
> values.  I experimented with a few different tests, but I haven't found a
> way to successfully treat null decimal values being imported from a csv as
> nulls in the database without just making the whole column a VARCHAR(255)
> datatype, which does seem to allow for nulls, but just seems like the wrong
> way to solve the problem.
>
> On Sun, Aug 5, 2012 at 9:53 AM, Hassan Schroeder <
> hassan.schroeder@stripped
> > wrote:
>
> > On Sat, Aug 4, 2012 at 8:14 PM, Fred G <bayespokerguy@stripped> wrote:
> >
> > > But I'm sure that I must be missing something here. Is there a way
> > > to
> > use a
> > > DECIMAL-like operator that treats blanks as blanks?
> >
> > A DECIMAL column is either going to contain a decimal number or NULL;
> > 'blank' isn't a term that even makes sense in this context.
> >
> > If you want NULL rather than 0 for a non-specified value, insert it
> > that way.
> >
> > --
> > Hassan Schroeder ------------------------ hassan.schroeder@stripped
> > http://about.me/hassanschroeder
> > twitter: @hassan
> >
>
> ________________________________
>
> Notice: This communication may contain privileged and/or confidential
> information. If you are not the intended recipient, please notify the
> sender by email, and immediately delete the message and any attachments
> without copying or disclosing them. LBI may, for any reason, intercept,
> access, use, and disclose any information that is communicated by or
> through, or which is stored on, its networks, applications, services, and
> devices.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql
>
>

Thread
DECIMAL datatype automatically makes blank become 0Fred G5 Aug
  • Re: DECIMAL datatype automatically makes blank become 0hsv7 Aug
Re: DECIMAL datatype automatically makes blank become 0Fred G6 Aug
  • RE: DECIMAL datatype automatically makes blank become 0Benjamin Stillman6 Aug
    • RE: DECIMAL datatype automatically makes blank become 0Benjamin Stillman6 Aug
      • Re: DECIMAL datatype automatically makes blank become 0Fred G6 Aug