I belive that '\N' in the data file will be interpreted as a NULL value...
--Chris
On Thu, 11 Mar 1999 garyl.cwa4.epa.gov@stripped wrote:
> >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
>
> ---------------------------------------------------------------------
> To request this thread, e-mail mysql-thread73@stripped
>
> To unsubscribe, e-mail the address in the List-Unsubscribe header.
> For additional commands, e-mail: mysql-help@stripped
>
