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

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

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