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 f开发者_如何学Pythonew 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?

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 !!!

If you use mysqldump. The create table definition will set the AUTO_INCREMENT value whatever value it was when the dump occurred. When you import the dump the INSERT statements will specify the value to insert into the new table.

If the drop/create table statements are missing the AUTO_INCREMENT value should be whatever the last inserted record was + 1.

Autoincrement fields only set automatic values if you insert NULL into them.
Otherwise they accept the value that you put into them.

(Provided there are no other limitations that prevent that from happening, like unique, primary key etc).