Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from MySQL Cookbook

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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata