Database Reference
In-Depth Information
Ignoring datafile columns
Extra columns at the end of input lines are easy to handle. If a line contains more
columns than are in the table,
LOAD
DATA
just ignores them (although it might produce
a nonzero warning count).
Skipping columns in the middle of lines is a bit more involved. To handle this, use a
column list with
LOAD
DATA
that assigns the columns to be ignored to a dummy user-
defined variable. Suppose that you want to load information from a Unix password
file
/etc/passwd
, which contains lines in the following format:
account:password:UID:GID:GECOS:directory:shell
Suppose also that you don't want to load the password and directory columns. A table
to hold the information in the remaining columns looks like this:
CREATE
TABLE
passwd
(
account
CHAR
(
8
),
#
login
name
uid
INT
,
#
user
ID
gid
INT
,
#
group
ID
gecos
CHAR
(
60
),
#
name
,
phone
,
office
,
etc
.
shell
CHAR
(
60
)
#
command
interpreter
);
To load the file, specify that the column delimiter is a colon. Also, tell
LOAD
DATA
to skip
the second and sixth fields that contain the password and directory. To do this, add a
column list in the statement. The list should include the name of each column to load
into the table, and a dummy user-defined variable for columns to be ignored (you can
use the same variable for all of them). The resulting statement looks like this:
mysql>
LOAD DATA LOCAL INFILE '/etc/passwd' INTO TABLE passwd
->
FIELDS TERMINATED BY ':'
->
(account,@dummy,uid,gid,gecos,@dummy,shell);
The corresponding
mysqlimport
command includes a
--columns
option:
%
mysqlimport --local \
--columns="account,@dummy,uid,gid,gecos,@dummy,shell" \
--fields-terminated-by=":" cookbook /etc/passwd
See Also
Another approach to ignoring columns is to preprocess the input file to remove col‐
umns.
Recipe 11.7
discusses a
yank_col.pl
utility that can extract and display datafile
columns in any order.