Database Reference
In-Depth Information
Better Loading
Although we have done well at loading a rather large data text file, we could do better. This
section covers a few ways we can improve the method of loading data with the
LOAD
DATA INFILE
statement.
Mapping Fields
When we loadedthe data from the Cornell CSV data text file, we included many fields
containing data in which we had no interest. We dealt with this by creating pointless char-
acter columns with no width to store data. That generated many warnings, which we ig-
nored.
There's a better way to addressunwanted fields. At the end of the
LOAD DATA INFILE
statement, you can add a comma-separated list of columns in the table that map to fields in
the original input. This list can also include user variables in place of columns. There must
be a column or a variable for each field and the columns must match the order of the fields,
but the order of columns in the
LOAD DATA INFILE
can be different from the order the
are in the table. So you can import fields into a table in any order you want. Additionally,
you can import fields you don't want into a temporary variable multiple times and their
data will be discarded; the variable itself disappears when the client session is terminated.
Let's drop the
clements_list_import
table and re-create it without the generic
columns that we don't need. Let's also put the columns in a different order. Enter the fol-
lowing two SQL statements on your server:
DROP TABLE
rookery
.
clements_list_import
;
CREATE TABLE
rookery
.
clements_list_import
(
id
INT
,
scientific_name
VARCHAR
(
255
),
english_name
VARCHAR
(
255
),
family
VARCHAR
(
255
),
bird_order
VARCHAR
(
255
),
change_type
VARCHAR
(
255
));
Now we have only the columns we want in this import table. We have the
family
before
the
bird_order
, and we put the
change_type
last.
Now let's load the data again. This time we'll provide a list of columns and variables to
map the fields where we want. We'll direct data from unwanted fields to a temporary vari-
able,
@niente
. Any name is fine.
Niente
means nothing in Italian. Execute this SQL
statement on your server:
LOAD DATA INFILE '
/tmp/Clements-Checklist-6.9-final.csv
'
INTO TABLE rookery.clements_list_import
