Databases Reference
In-Depth Information
The options available in the
Data
section are:
•
Complete inserts
: Generates the following export for the
author
table:
INSERT INTO 'author' ('id', 'name', 'phone')
VALUES (1, 'John Smith', '+01 445 789-1234');
INSERT INTO 'author' ('id', 'name', 'phone')
VALUES (2, 'Maria Sunshine', '+01 455 444-5683');
Notice that every column name is present in every statement. The resulting
file is bigger, but will prove more portable on various SQL systems, with the
added benefit of being better documented.
Extended inserts
: Packs the whole table data into a single
INSERT
statement:
INSERT INTO 'author' VALUES (1, 'John Smith',
'+01 445 789-1234'), (2, 'Maria Sunshine', '+01 455 444-5683');
•
This method of inserting data is faster than using multiple
INSERT
statements, but is less convenient because it makes reading the resultant file
harder.
Extended inserts
also produces a smaller file, but each line of this
file is not executable in itself because each line does not have an
INSERT
statement. If you cannot import the complete file in one operation, you
cannot split the file with a text editor and import it chunk by chunk.
Maximal length of created query
: The single
INSERT
statement generated
for
Extended inserts
might become too big and could cause problems, this
is why we can set a limit here—in number of characters—for the length of
this statement.
Use delayed inserts
: Adds the
DELAYED
modifier to
INSERT
statements. This
accelerates the
INSERT
operation because it is queued to the server, which
will execute it when the table is not in use. Please note that this is a MySQL
non-standard extension, and it's only available for
MyISAM,
ISAM,
MEMORY
and
ARCHIVE
tables.
Use ignore inserts
: Normally, at import time, we cannot insert duplicate
values for unique keys—this would abort the insert operation. This option
adds the
IGNORE
modifier to
INSERT
and
UPDATE
statements, thus skipping
the rows which generate duplicate key errors.
Use hexadecimal for BLOB
: This option makes phpMyAdmin encode
the contents of BLOB fields in
0x
format. Such a format is useful because,
depending on the software that will be used to manipulate the export file
(for example a text editor or mail program), handling a file containing 8-bit
data can be problematic. However, using this option will produce an export
of the
BLOB
that can be twice the size or even more.
•
•
•
•
Search WWH ::
Custom Search