dan dan - 6 months ago 13
SQL Question

If you load a dump file into a table with an auto_increment column, what happens?

Assume I have a

reports
table with an
id
int auto_increment column that serves as the primary key.

If I
mysqldump
this table into a file with
drop table
and
create table
instructions added, and then I load it into another database B which is just like A but missing a few of the newest
reports
records, does database B end up with an exact copy of database A's
reports
table?

what if the
drop table
and
create table
instructions were missing?

Answer

The auto_increment column's data is preserved as is.

Try doing a mysqldump to a single table and view how the column is inserted.

Also note that the table definition of each table keeps the next auto_increment value while the previous values are included in the list of VALUES. Here is a sample:

mysql> create database test;
Query OK, 1 row affected (0.06 sec)

mysql> use test
Database changed
mysql> create table tb (name char(10),id int not null auto_increment primary key) ENGINE=MyISAM;
Query OK, 0 rows affected (0.13 sec)

mysql> insert into tb (name) values ('John'),('Mary'),('Joseph');
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from tb;
+--------+----+
| name | id |
+--------+----+
| John | 1 |
| Mary | 2 |
| Joseph | 3 |
+--------+----+
3 rows in set (0.03 sec)

I performed a mysqldump of the test database and here is what it produced:

DROP TABLE IF EXISTS tb;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/
!40101 SET character_set_client = utf8 */;
CREATE TABLE tb (
name char(10) DEFAULT NULL,
id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table tb
--

LOCK TABLES tb WRITE;
/*!40000 ALTER TABLE tb DISABLE KEYS /;
INSERT INTO tb VALUES ('John',1),('Mary',2),('Joseph',3);
/
!40000 ALTER TABLE tb ENABLE KEYS /;
UNLOCK TABLES;
/
!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

Give it a Try !!!