MySQL in a Nutshell [21]
+---------+-----------------------------------+---------------+
| book_id | title | author |
+---------+-----------------------------------+---------------+
| 1400 | If on a winter's night a traveler | Italo Calvino |
+---------+-----------------------------------+---------------+
With the LIKE operator, we use the percent sign wildcard twice to indicate that we’re searching for all rows in which the title column’s data contains the string traveler with zero or more characters before it (the preceding percent sign), and zero or more characters after it (the terminating percent sign). Put another way, the word traveler must be contained somewhere in the column’s data to have a pattern match. The next part of the clause indicates that winter must also be found in the same column. Incidentally, the LIKE keyword is an operator like the equals sign.
If another customer asks us to search the database for a Graham Greene book with either the word Stamboul or the word Orient in the title, we could use OR within an expression like this:
SELECT book_id, title
FROM books
WHERE author_id = 1
AND title LIKE '%Stamboul%'
OR author_id = 1
AND title LIKE '%Orient%';
Since we already know the author’s identification number, this statement is more succinct and includes only one table. Notice that we have to specify the author_id in each expression; otherwise we might get results by other authors that match the words for which we’re searching. For more information on operators, see Appendix B. You can find more examples and possibilities for searching data in Chapter 6.
Importing Data in Bulk
Often, when setting up a new database, you will need to migrate data from an old database to MySQL. In the case of our bookstore, let’s suppose that a vendor has sent us a disk with a list of all of their books in a simple text file. Each record for each book is on a separate line, and each field of each record is separated by a vertical bar. Here’s what the fictitious vendor’s data text file looks like:
ISBN|TITLE|AUTHOR LAST|AUTHOR FIRST|COPYRIGHT DATE|
067973452X|Notes from Underground|Dostoevsky|Fyodor|August 1994|
...
Obviously, an actual vendor file would contain more fields and records than are shown here, but this is enough for our example. The first line contains descriptions of the fields in the records that follow. We don’t need to extract the first line; it’s just instructions for us. So, we’ll tell MySQL to ignore it when we enter our SQL statement.
As for the data, we must consider a few problems. First, the fields are not in the order that they are found in our tables. We’ll have to tell MySQL the order in which the data will be coming so that it can make adjustments. The other problem is that this text table contains data for both our books table and our authors table. This is going to be a bit tricky, but we can deal with it. What we’ll do is extract the author information only in one SQL statement, then we’ll run a separate SQL statement to import the book information. Before starting, we’ve copied the vendor’s file called books.txt to a temporary directory (e.g., /tmp). Here we run a LOAD DATA INFILE statement from the mysql client:
LOAD DATA INFILE '/tmp/books.txt' REPLACE INTO TABLE authors
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n'
TEXT_FIELDS(col1, col2, col3, col4, col5)
SET author_last = col3, author_first = col4
IGNORE col1, col2, col5, 1 LINES;
First, I should point out that the TEXT_FIELDS and the IGNORE clause for columns are not available before version 4.1 of MySQL. The IGNORE n LINES clause has been around for a while, though. With IGNORE 1 LINES, the first line of the text file containing the column headings will be ignored. Going back to the first line in this SQL statement, we’ve named the file to load and the table in which to load the data. The REPLACE flag has the effect of the REPLACE statement mentioned earlier. Of course, since the name fields aren’t set to unique, there won’t be any duplicates as far as MySQL is concerned. In a real-life situation, you would have to alter