Database Reference
In-Depth Information
The first three lines present a header comment so that when you review the dump file, you
will know that this is the start of the section related to the
rookery
database. The first
SQL statement, reasonably enough,is a
CREATE DATABASE
statement. It can look a bit
confusing because it contains a couple of conditional components, which are related to the
version of MySQL or MariaDB on which the statement will later be executed. Let's look
at one of those components.
In this SQL statement,
IF NOT EXISTS
willbe executed if the server is running at least
version 3.23.12 of MySQL. That's quite an old version of MySQL, but this option was in-
troduced in that version and release of MySQL and hasn't changed since. It's unlikely that
a server anywhere in the world is still using such an early version, but this is the nature of
mysqldump
, to be ready for any conflict. More important is the option itself. If the
rook-
ery
database already exists, it won't be created with this
CREATE DATABASE
statement
and it won't be overwritten. Incidentally, if you want to create a dump file without
CREATE DATABASE
and without
CREATE TABLE
statements,you can addthe
--no-
create-info
option when running
mysqldump
.
The last SQL statement in the previous snippet switches the default database to use to
rookery
. You may wonder why the utility uses the
USE
statement instead of just includ-
ing the database name in the subsequent SQL statements (e.g., it doesn't have statements
like,
INSERT INTO `rookery`.`bird_families`...
). That would seem to me
more dependable of a method, but the method used has an advantage. When executing a
dump table, if you want to create a new database on the same server, but with all of the
tables and data the same, you can simplyedit the
USE
statement in the dump file and
change the database name (e.g., change
rookery
to
rookery_backup
) in one place.
Then the original will be preserved and you'll have an identical copy. We'll talk more
about this later. Let's look at what's next in the dump file.
The next section of the dump file deals with the first table of the
rookery
database. As
the following excerpt shows, it's the table structure of the
bird_families
table:
--
-- Table structure for table `bird_families`
--
DROP TABLE IF EXISTS `bird_families`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `bird_families` (
`family_id` int(11) NOT NULL AUTO_INCREMENT,
