Oracle 5.0, Reference Manual

Page: 1100 / 3232

LOAD DATA INFILE
Syntax
1080
If an input line has too few fields, the table columns for which input fields are missing are set to their
default values. Default value assignment is described in
Section 11.1.7, “Data Type Default Values” .
An empty field value is interpreted different from a missing field:
• For string types, the column is set to the empty string.
• For numeric types, the column is set to
0
.
• For date and time types, the column is set to the appropriate “zero” value for the type. See
Section 11.1.5, “Date and Time Types” .
These are the same values that result if you assign an empty string explicitly to a string, numeric, or
date or time type explicitly in an
INSERT
or
UPDATE
statement.
Treatment of empty or incorrect field values differs from that just described if the SQL mode is set to a
restrictive value. For example, if
sql_mode='TRADITIONAL
[495] , conversion of an empty value or a
value such as
'x'
for a numeric column results in an error, not conversion to 0. (With
LOCAL
, warnings
occur rather than errors, even with a restrictive
sql_mode
[495] value, because the server has no way
to stop transmission of the file in the middle of the operation.)
TIMESTAMP
columns are set to the current date and time only if there is a
NULL
value for the column
(that is,
\N
) and the column is not declared to permit
NULL
values, or if the
TIMESTAMP
column's
default value is the current timestamp and it is omitted from the field list when a field list is specified.
LOAD DATA INFILE
regards all input as strings, so you cannot use numeric values for
ENUM
or
SET
columns the way you can with
INSERT
statements. All
ENUM
and
SET
values must be specified as
strings.
BIT
values cannot be loaded using binary notation (for example,
b'011010'
). To work around this,
specify the values as regular integers and use the
SET
clause to convert them so that MySQL performs
a numeric type conversion and loads them into the
BIT
column properly:
shell> cat /tmp/bit_test.txt
2
127
shell> mysql test
mysql> LOAD DATA INFILE '/tmp/bit_test.txt'
-> INTO TABLE bit_test (@var1) SET b = CAST(@var1 AS UNSIGNED);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT BIN(b+0) FROM bit_test;
+----------+
| bin(b+0) |
+----------+
| 10 |
| 1111111 |
+----------+
2 rows in set (0.00 sec)
On Unix, if you need
LOAD DATA
to read from a pipe, you can use the following technique (the
example loads a listing of the
/
directory into the table
db1.t1
):
mkfifo /mysql/data/db1/ls.dat
chmod 666 /mysql/data/db1/ls.dat
find / -ls > /mysql/data/db1/ls.dat &
mysql -e "LOAD DATA INFILE 'ls.dat' INTO TABLE t1" db1
Note that you must run the command that generates the data to be loaded and the
mysql
commands
either on separate terminals, or run the data generation process in the background (as shown in the
preceding example). If you do not do this, the pipe will block until data is read by the
mysql
process.
When the
LOAD DATA INFILE
statement finishes, it returns an information string in the following
format: