Databases Reference
In-Depth Information
This method of inserting data is faster than using multiple
INSERT
s
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
state-
ment. 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
and
ISAM
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 binary fields:
: A field with the
BINARY
attribute may
or may not have binary contents. This option makes phpMyAdmin encode
the contents of these fields in
0x
format. Uncheck this option if the fields are
marked
BINARY
but are nevertheless in plain text like the
mysql.user
table.
Export type
: The choices are
INSERT
,
UPDATE
, and
REPLACE
. The most
well-known of these types is the default
INSERT
- using
INSERT
statements
to import back our data. At import time, however, we could be in a situation
where a table already exists and contains valuable data, and we just want
to update the fields that are in the current table we are exporting.
UPDATE
generates statements like
UPDATE
'authors'
SET
'author_id'
=
1,
'author_name'
=
'John
Smith',
'phone'
=
'111-1111'
WHERE
'author_id'
=
'1';
updating a row when the same primary or unique key
is found. The third possibility,
REPLACE
, produces statements like
REPLACE
INTO
'authors'
VALUES
(1,
'John
Smith',
'111-1111');
which act like an
INSERT
statement for new rows and updates existing rows, based on primary
or unique keys.
•
•
•
•
•
The Save as file Sub-Panel
In the previous examples, the results of the export operation were displayed
on-screen, and of course, no compression was made on the data. We can choose to
transmit the export file via HTTP by checking the
Save as file
checkbox. This triggers
a
Save
dialog into the browser, which ultimately saves the file on our local station:
Search WWH ::
Custom Search