List:General Discussion« Previous MessageNext Message »
From:Benjamin Stillman Date:August 6 2012 6:13pm
Subject:RE: DECIMAL datatype automatically makes blank become 0
View as plain text  
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