6 messages in com.mysql.lists.mysqlImporting NULL values
FromSent OnAttachments
gary...@pomo.cwa4.epa.gov11 Mar 1999 16:45 
Christian Mack12 Mar 1999 06:22 
Chris12 Mar 1999 08:46 
Thimble Smith12 Mar 1999 08:55 
Micheal Mc Evoy12 Mar 1999 09:08 
Michael Widenius14 Mar 1999 04:34 
Subject:Importing NULL values
From:gary...@pomo.cwa4.epa.gov (gary@pomo.cwa4.epa.gov)
Date:03/11/1999 04:45:08 PM
List:com.mysql.lists.mysql

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 gar@pomo.cwa4.epa.gov 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