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:bstill...@limitedbrands.com] Sent: Monday, August 06, 2012 2:05 PM To: 'Fred G' Cc: mysql@lists.mysql.com 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:bayespoker...@gmail.com] Sent: Monday, August 06, 2012 9:48 AM To: Hassan Schroeder Cc: mysql@lists.mysql.com 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.schroe...@gmail.com > wrote: > On Sat, Aug 4, 2012 at 8:14 PM, Fred G <bayespoker...@gmail.com> 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.schroe...@gmail.com > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
