Skipping Datafile Columns
Problem
Your datafile contains columns that should be ignored rather than loaded into the table.
Solution
That’s not a problem if the columns are at the ends of the input lines. Otherwise, you’ll need to preprocess the datafile before loading it.
Discussion
Extra columns that occur 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
(though it may indicate a nonzero warning count).
Skipping columns in the middle of lines is a bit more involved.
Suppose 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 bother loading the password column. A table to hold the information in the other 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. directory CHAR(60), # home directory shell CHAR(60) # command interpreter );
To load the file, we need to specify that the column delimiter is a
colon, which is easily handled with a FIELDS
clause:
FIELDS TERMINATED BY ':'
However, we must also tell LOAD
DATA
to skip the second field that contains the
password. That’s a problem, because
LOAD
DATA
always wants to load successive columns from the datafile. You can tell it which table column each datafile column corresponds to, but you can’t tell it to skip columns ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.