>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
