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.
Search WWH ::




Custom Search