Database Reference
In-Depth Information
FIELDS
clause to specify that fields are enclosed with double quotes. In addition, we've
included the
OPTIONALLY
option to that subclause to indicate that some fields may not
be enclosed within double quotes. That tells MySQL that if it encounters a double quote,
to look for a second one and to treat everything inside of the pair of double quotes as data.
So if it finds a comma within double quotes, it will not consider it a marker indicating the
termination of a field.
It may seem strange that this works, considering there is text outside of the double quotes
and more than one pair of double quotes in some fields, but it does work.
TIP
When loading data into a table, it's generally locked and other users are prevented from accessing the
table. However, you can include the
LOW_PRIORITY
option tolet other clients read from the table
while you are loading it:
LOAD DATA LOW_PRIORITY INFILE
. The execution of the SQL state-
ment will be delayed until no other clients are reading the table. It works only with tables that use stor-
age engines with table-level locking (e.g., MyISAM), not with row-level locking tables (e.g., InnoDB).
There was another addition to the
LOAD DATA INFILE
statementwe used here. We ad-
ded the
IGNORE
clause to the end. This tells MySQL to ignore the number of lines speci-
fied, starting from the beginning of the data text file. By specifying that the statement ig-
nore one line, we skip over the first line, which is the line containing the field names that
we don't need. If the data text file has more than one line for the header, you can tell it to
ignore more than one.
Execute the earlier
SELECT
statement again:
SELECT id, change_type,
scientific_name, english_name,
`order`, family
FROM rookery.clements_list_import
WHERE change_type = 'new species' LIMIT 2 \G
*************************** 1. row ***************************
id: 4073
change_type: new species
scientific_name: Prosobonia ellisi
english_name: Moorea Sandpiper
order: Charadriiformes
family: Scolopacidae (Sandpipers and Allies)
*************************** 2. row ***************************
id: 6707
change_type: new species
