Recipe 10.10. 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 the table and the file by indicating which table columns correspond to the datafile columns.

Discussion

LOAD DATA assumes that the columns in the datafile have the same order as the columns in the table. If that's not true, specify a list to indicate which table columns the datafile columns should be loaded into. Suppose that 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             




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net