Specifying Input Column Order
Problem
The columns in your datafile aren’t in the same order as the columns in the table into which you’re loading the file.
Solution
Tell LOAD
DATA
how to match up
the table and the file by indicating which table columns correspond
to the datafile columns.
Discussion
LOAD
DATA
assumes the columns
in the datafile have the same order as the columns in the table. If
that’s not true, you can specify a list to indicate
which table columns the datafile columns should be loaded into.
Suppose your table has columns a
,
b
, and c
, but successive
columns in the datafile correspond to columns b
,
c
, and a
. You can load the file
like this:
mysql> LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl (b, c, a);
The equivalent
mysqlimport statement uses the
--columns
option to specify the column list:
% mysqlimport --local --columns=b,c,a cookbook mytbl.txt
The --columns
option for
mysqlimport was introduced in MySQL 3.23.17. If
you have an older version, you must either use
LOAD
DATA
directly or
preprocess your datafile to rearrange the file’s
columns into the order in which they occur in the table. (See Recipe 10.20 for a utility that can do this.)
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.