MySQL Cookbook, 2nd Edition

Preprocessing Input Values Before Inserting Them

Problem

You have values in a datafile that are not in a format that is suitable for loading into a table. For example, values are in the wrong units, or two input fields must be combined and inserted into a single column.

Solution

LOADDATAhas the capability of performing limited preprocessing of input values before inserting them. This enables you to map input data onto more appropriate values before loading them into your table.

Discussion

Specifying Input Column Order shows how you can specify a column list for LOADDATA to indicate how input fields correspond to table columns. The column list also can name user-defined variables, such that for each input record, the input fields are assigned to the variables. You can then perform calculations with those variables before inserting the result into the table. These calculations are specified in a SET clause that names one or more col_name=expr assignments, separated by commas.

Suppose that you have a datafile that has the following columns, with the first line providing column labels:

Date        Time        Name        Weight      State
2006-09-01  12:00:00    Bill Wills  200         Nevada
2006-09-02  09:00:00    Jeff Deft   150         Oklahoma
2006-09-04  03:00:00    Bob Hobbs   225         Utah
2006-09-07  08:00:00    Hank Banks  175         Texas

Suppose also that the file must be loaded into a table that has these columns:

CREATE TABLE t
(
  dt         DATETIME,
  last_name  CHAR(10),
  first_name CHAR(10),
  weight_kg  FLOAT,
  st_abbrev  CHAR(2)
);

There are several mismatches between ...