List:General Discussion« Previous MessageNext Message »
From:garyl.cwa4.epa.gov Date:March 12 1999 12:45am
Subject:Importing NULL values
View as plain text  
>Description:

	I am trying to do a LOAD DATA INFILE where the data file has NULL
values.  For example, using the table:

     +--------+-------------+------+-----+---------+-------+
     | Field  | Type        | Null | Key | Default | Extra |
     +--------+-------------+------+-----+---------+-------+
     | fname  | varchar(10) | YES  |     | NULL    |       |
     | lname  | varchar(10) | YES  |     | NULL    |       |
     | age    | int(4)      | YES  |     | NULL    |       |
     | height | float(5,2)  | YES  |     | NULL    |       |
     +--------+-------------+------+-----+---------+-------+
     4 rows in set (0.16 sec)

and data file:

     "sally","rogers",,
     "joe","blo",32,5.5
     "test","",,
     
And submit the floowing query:

     LOAD DATA INFILE '/h/garyl/test.dat'
     INTO TABLE test
     FIELDS TERMINATED BY ','
     OPTIONALLY ENCLOSED BY '"';

I get:

     mysql> select * from test;
     +-------+--------+------+--------+
     | fname | lname  | age  | height |
     +-------+--------+------+--------+
     | sally | rogers |    0 |   0.00 |
     | joe   | blo    |   32 |   5.50 |
     | test  |        |    0 |   0.00 |
     +-------+--------+------+--------+
     3 rows in set (0.18 sec)

Instead, I should have gotten:

     mysql> select * from test;
     +-------+--------+------+--------+
     | fname | lname  | age  | height |
     +-------+--------+------+--------+
     | sally | rogers | NULL |   NULL |
     | joe   | blo    |   32 |   5.50 |
     | test  | NULL   | NULL |   NULL |
     +-------+--------+------+--------+
     3 rows in set (0.02 sec)

Anyone know how to import data files with NULL values that show up as 
NULL values?

-Gary

>Submitter-Id:	<submitter ID>
>Originator:	Gary G. Lear
>Organization:
  -----------------------------------
  Gary Lear
  garyl@stripped
  OAR/Acid Rain Division
  401 M Street SW (6204J)
  Washington DC 20460
  202 564-9179
>
>MySQL support: none
>Synopsis:	Null data vales not loading as NULL
>Severity:	non-critical
>Priority:	medium
>Category:	mysql
>Class:		sw-bug
>Release:	mysql-3.22.15-gamma (TCX binary)
>Server: /usr/local/bin/mysqladmin  Ver 7.8 Distrib 3.22.15-gamma, for sun-solaris2.6
> on sparc
TCX Datakonsult AB, by Monty

Server version		3.22.15-gamma-log
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/tmp/mysql.sock
Uptime:			2 days 19 hours 1 min 12 sec

Threads: 4  Questions: 72231  Slow queries: 96  Opens: 167  Flush tables: 1  Open tables:
28
>Environment:
	
System: SunOS pomo 5.6 Generic_105181-06 sun4m sparc SUNW,SPARCstation-20
Architecture: sun4

Some paths:  /usr/local/bin/perl /usr/local/bin/make /usr/local/bin/gcc /usr/ucb/cc
GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.6/2.8.0/specs
gcc version 2.8.0
Compilation info: CC='gcc'  CFLAGS='-O3 -fomit-frame-pointer'  CXX='gcc'  CXXFLAGS='-O3
-fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti'  LDFLAGS=''
Configure command: ./configure  --prefix=/usr/local/mysql '--with-comment=TCX binary'
--with-low-memory --disable-shared
Perl: This is perl, version 5.004_04 built for sun4-solaris
Thread
Importing NULL valuesgaryl.cwa4.epa.gov12 Mar
  • Re: Importing NULL valuesChris12 Mar
  • Re: Importing NULL valuesThimble Smith12 Mar
  • Re: Importing NULL valuesMicheal Mc Evoy12 Mar
  • Re: Importing NULL valuesChristian Mack12 Mar
  • Importing NULL valuesMichael Widenius15 Mar