Databases Reference
In-Depth Information
The general SQL options are:
Add custom comment into header
: We can add our own comments for
this export (for example, 'Monthly backup') which will show in the export
headers (after the PHP version number). If the comment has more than one
line, we must use the special character
\n
to separate each line.
Enclose export in a transaction
: Starting with MySQL 4.0.11, we can use
the
START
TRANSACTION
statement. This command, combined with
SET
AUTOCOMMIT=0
at the beginning and
COMMIT
at the end, asks MySQL to
execute the import (when we will re-import this file) in one transaction,
ensuring that all the changes are done as a whole.
Disable foreign key checks
: In the export file, we can add
DROP
TABLE
statements. However, normally a table cannot be dropped if it is referenced
in a foreign key constraint. This option overrides the verification by adding
SET
FOREIGN_KEY_CHECKS=0
to the export file.
SQL compatibility mode:
This lets us choose the flavor of SQL that we
export. We must know about the system on which we intend to import this
file. Among the choices are
MySQL 3.23
,
MySQL 4.0
,
Oracle,
and
ANSI
.
•
•
•
•
The options in
Structure
section are:
Add DROP TABLE / DROP VIEW
:
Adds a
DROP
TABLE
IF
EXISTS
statement
before each
CREATE
TABLE
statement, for example:
DROP
TABLE
IF
EXISTS
'author'.
This way, we can ensure that the export file can be executed on
a database in which the same table already exists, updating its structure, but
destroying previous table contents. A
DROP
VIEW
is used for views.
Add IF NOT EXISTS
: Adds the
IF
NOT
EXISTS
modifier to
CREATE
TABLE
statements, avoiding an error during import if the table already exists.
Add AUTO_INCREMENT value
: Puts auto-increment information on
the tables into the export, ensuring that the inserted rows in the tables will
receive the correct next auto-increment ID value.
Enclose table and field names with backquotes
: Backquotes are the normal
way of protecting table and field names that may contain special characters.
In most cases it is useful to have them, but not if the target server (where the
export file will be imported) is running a MySQL version older than 3.23.6,
which does not support backquotes.
Add CREATE PROCEDURE/FUNCTION
: This includes in the export all
procedures and functions found in this database.
Add into comments
: This adds information (in the form of SQL comments),
which cannot be directly imported, but which nonetheless is valuable and
human-readable table information. The amount of information here varies
depending on the relational system settings, (see Chapter 11). In fact, with an
activated relational system, we would get the following choices:
•
•
•
•
•
•
Search WWH ::
Custom Search