Database Reference
In-Depth Information
By default, LOAD DATA expects the file to be on the same machine as the MySQL server,
within the directory that contains the information about the current database, in our case,
mysqlfast
. If we were running the client and server on separate machines, and wanted the
file to be on the client machine, we would need to insert the word LOCAL before INFILE to
specify that it is on the local machine. We do not need to do this in our example.
If we look at the text file again, we can see that the first line is a list of the column names
in the order of the file. This is useful information to us but not to the LOAD DATA com-
mand. We could delete that first row but that would mean changing the contents of our log
file which may be needed by another program, so we need to add the line:
IGNORE 1 LINES
at the end of our query to get the command to bypass the first line.
You will notice that our file has commas separating the columns. This is one standard of
representing data in text files, called a “csv” or Comma Separated Variable file. By default,
though, the LOAD DATA command looks for a tab to separate columns. We therefore have
to tell LOAD DATA to view the separators as commas. This is done by adding the following
to the query:
FIELDS TERMINATED BY ','
As you may expect, as there is a FIELDS TERMINATED command there is also a
RECORDS TERMINATED command for setting the way that the record's end is repre-
sented. As LOAD DATA's default setting for a record's end is the newline, the same as our
text file, we don't have to specify this.
If you remember when we created the Log table in the previous chapter, we used the fol-
lowing command:
CREATE TABLE Log ( ID MEDIUMINT
NOT NULL
AUTO_INCREMENT
PRIMARY KEY,
CookieID MEDIUMINT,
WebpageID MEDIUMINT,
Browser TEXT,
DateCreated DATETIME,
IPNumber TEXT,
ReferringPage TEXT )
By default, LOAD DATA expects the columns in the text file to be in the same order as
when the table was created. If you compare this with the text file you will see that the col-
umn order is different. LOAD DATA allows you to specify the order of columns to import by
just giving it a list of the rows.
We now have enough information to insert our log file into the Log table. The command
that we will use to do this is as follows:
Search WWH ::
Custom Search