From: Benjamin Stillman Date: August 6 2012 6:13pm Subject: RE: DECIMAL datatype automatically makes blank become 0 List-Archive: http://lists.mysql.com/mysql/227951 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Meant to add: My workaround was to set the column as varchar then convert it to decimal a= fter 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 t= able.=20 -----Original Message----- From: Stillman, Benjamin [mailto:BStillman@stripped]=20 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 op= en since November of 2006 about this: http://bugs.mysql.com/bug.php?id=3D23= 212 -----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 fo= r 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 bla= nk 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 deci= mal 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 va= lues. 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 nul= ls in the database without just making the whole column a VARCHAR(255) data= type, 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 wrote: > On Sat, Aug 4, 2012 at 8:14 PM, Fred G wrote: > > > But I'm sure that I must be missing something here. Is there a way=20 > > to > use a > > DECIMAL-like operator that treats blanks as blanks? > > A DECIMAL column is either going to contain a decimal number or NULL;=20 > '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=20 > that way. > > -- > Hassan Schroeder ------------------------ hassan.schroeder@stripped=20 > http://about.me/hassanschroeder > twitter: @hassan > ________________________________ Notice: This communication may contain privileged and/or confidential infor= mation. If you are not the intended recipient, please notify the sender by = email, and immediately delete the message and any attachments without copyi= ng or disclosing them. LBI may, for any reason, intercept, access, use, and= disclose any information that is communicated by or through, or which is s= tored 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