Database Reference
In-Depth Information
`scientific_name` varchar(100) COLLATE latin1_bin DEFAULT NULL,
`brief_description` varchar(255) COLLATE latin1_bin DEFAULT NULL,
`order_id` int(11) DEFAULT NULL,
PRIMARY KEY (`family_id`),
UNIQUE KEY `scientific_name` (`scientific_name`)
) ENGINE=MyISAM AUTO_INCREMENT=334 DEFAULT CHARSET=latin1
COLLATE=latin1_bin;
/*!40101 SET character_set_client = @saved_cs_client */;
The first SQL statement here may concern you. It should. It'sa
DROP TABLE
statement
that will delete the
bird_families
table. No data ought to be lost because the follow-
ing SQL lines will re-create the table and insert data into it from the time the dump file
was created. However, if there have been changes to the data in the
bird_families
table since the dump file was created, those changes will be lost when the table is restored
to its previous state. For such a situation, there are other methods you can resort to besides
the bulk clobbering of tables. One method uses the suggestion made previously to alter the
USE
statement to point all schema and data statements to a different, temporary database.
Then you can attempt to merge the old and new data together. Depending on the situation,
you might be able to do this by changing the
INSERT
to a
REPLACE
statement.Another
method would be to remove the
DROP TABLE
statement and change the name of
CREATE TABLE
statement that follows to create a new table name. We'll cover such
techniques later in this chapter in
Restoring Backups
.
The
IF EXISTS
option ensures that a restore will drop the table only if it exist. If this
statement was omitted, a restore would probably try to run the statement when the table
didn't exist, and thus generate an error that could abort the restore.
After the
DROP TABLE
statement, there are more conditional SQL statements for vari-
ables related to the table and the client. These are followed by the
CREATE TABLE
state-
ment, which matches the results of a
SHOW CREATE TABLE
statementfor the table.
This section ends by returning the variable changed to its previous setting.
Now the
bird_families
table is ready for the data. The next set of entries in the
dump file are:
--
-- Dumping data for table `bird_families`
--
LOCK TABLES `bird_families` WRITE;
/*!40000 ALTER TABLE `bird_families` DISABLE KEYS */;
