MySQL in a Nutshell [22]
In the second line, we specify that fields are terminated by a vertical bar and that lines are terminated by a carriage return (\r) and a newline (\n). This is the format for an MS-DOS text file. Unix files have only a newline to terminate the line.
In the third line of the SQL statement, we create aliases for each column. In the fourth line, we name the table columns to receive data and set their values based on the aliases given in the previous line. In the final line, we tell MySQL to ignore the columns that we don’t want, as well as the top line, because it doesn’t contain data.
If you’re using an older version of MySQL that isn’t able to ignore unwanted columns, you will have to perform a couple of extra steps. There are a few different ways of doing this. One simple way, if the table into which we’re loading data isn’t too large, is to add three extra, temporary columns to authors that will take in the unwanted fields of data from the text file and drop them later. This would look like the following:
ALTER TABLE authors
ADD COLUMN col1 VARCHAR(50),
ADD COLUMN col2 VARCHAR(50),
ADD COLUMN col5 VARCHAR(50);
LOAD DATA INFILE '/tmp/books.txt' REPLACE INTO TABLE authors
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(col1, col2, author_last, author_first, col5);
ALTER TABLE authors
DROP COLUMN col1,
DROP COLUMN col2,
DROP COLUMN col5;
These statements will work, but they’re not as graceful as the more straightforward statement shown earlier. In the second SQL statement here, notice that the IGNORE clause specifies one line to be ignored. The last line of the same statement lists the columns in the authors table that are to receive the data and the sequence in which they will be imported. In the third SQL statement, having finished importing the data from the vendor’s text file, we now delete the temporary columns with their unnecessary data by using a DROP statement. There’s usually no recourse from DROP, no undo. So take care in using it.
Once we manage to copy the list of authors into the authors table from the text file, we need to load the data for the books and find the correct author_id for each book. We do this through the following:
LOAD DATA INFILE '/tmp/books.txt' IGNORE INTO TABLE books
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n'
TEXT_FIELDS(col1, col2, col3, col4, col5)
SET isbn = col1, title = col2,
pub_year = RIGHT(col5, 4),
author_id =
(SELECT author_id
WHERE author_last = col3
AND author_first = col4)
IGNORE col3, col4, 1 LINES;
In this SQL statement, we’ve added a couple of twists to get what we need. On the fifth line, to extract the year from the copyright field—which contains both the month and the year—we use the string function RIGHT( ). It captures the last four characters of col5 as specified in the second argument.
The sixth line starts a subquery that determines the author_id based on data from the authors table, where the author’s last and first names match what is found in the respective aliases. The results of the column selected within the parentheses will be written to the author_id column.
Finally, we’re having MySQL ignore col3 and col4, as well as the column heading line. The IGNORE flag on the first line instructs MySQL to ignore error messages, not to replace any duplicate rows, and to continue executing the SQL statement. Doing this maneuver with earlier versions of MySQL will require temporary columns or a temporary table along the lines of the previous example. Actually, using a temporary table is still a prudent method for staging data. After you’ve verified it, you can execute an INSERT...SELECT statement (see Chapter 6).
Command-Line Interface
It’s not necessary to open the mysql interface to enter SQL statements into the MySQL server. In fact, sometimes you may have only a quick query to make in MySQL, and you’d rather just do it from the shell or command line. For instance, suppose we have a table called vendors in our database, and we want to get