Bulk Importing Data - Learning MySQL and MariaDB - page 398

Bulk Exporting Data
Thus far in this chapter wehave looked at how to bulk import data into MySQL and Mari-
aDB from data text files. However, you may be asked to do the opposite, and bulk export
data to provide someone with a text file containing data from your MySQL databases. This
can be done more easily than importing, so long as you get to decide the layout of the data
text file.
The easiest way to bulk export data to a textfile is to use the SELECT statement with the
INTO OUTFILE clause. This works similarly to the LOAD DATA INFILE statement,
with the same subclauses — except that it exports instead of imports data. Let's look at an
example.
Suppose we want to give someone a list of birds from the rookery database. We want
specifically to give them a test file containing a list of birds in theCharadriiformes— an
order of birds that includes Sea Gulls and Plovers. We want to export the scientific and
common name of each bird, and the family name.
We'll do this in stages. First, let's construct a SELECT statement to make sure we're ex-
porting the correct data. Execute this from your server:
SELECT birds . scientific_name ,
IFNULL ( common_name , '' ),
bird_families . scientific_name
FROM rookery . birds
JOIN rookery . bird_families USING ( family_id )
JOIN rookery . bird_orders USING ( order_id )
WHERE bird_orders . scientific_name = 'Charadriiformes'
ORDER BY common_name ;
This SELECT statement includesa JOIN (covered extensively in Chapter9 ). We're joining
together the main three tables in the rookery database to get the bird names and the fam-
ily names for the family order that we want. We're ordering the list based on com-
mon_name . The SELECT...INTO OUTFILE statement will generally convert NULL
values to the letter N. So we'reusing IFNULL() to change any null values for the com-
mon_name to a blank space. That SELECT statement works fine. If you tried it on your
server, it should have returned about 718 rows.
To keep anyone receiving the data text from being confused as to what each field repres-
ents, let's include a first row containing field names. The easiest way to do this is to just ex-
ecute SELECT with a set of strings like this:
SELECT 'scientific name' , 'common name' , 'family name' ;