MySQL table - to truncate or not to truncate?

Question

I've got a table that exists in both my live (hosted) DB and my development (local) DB. I want to get a bunch of records into the live table. What I've been doing is as follows: - Export/Import a copy of the live table back to the dev DB using phpMyAdmin. - Use a C batch file to load the new data into the dev table. - Export/Import a copy of the updated dev table back to the live DB, again using phpMyAdmin. This all works fine, as far as it goes. The problem is this: if a record has been deleted in the live table, step 1 doesn't remove it from the dev table (even if you choose the "Replace table with file" option). This record then gets recreated in the live table at step 3. My question: should I truncate the dev table (after backing up, of course) before I import at step 1? Will my import set the auto-increment on the dev table to the same point as it is on the live table? Or am I about to stuff things up horribly? Thanks. EDIT: Here's the table. (Can't get it to format better; sorry…

Accepted Answer

Truncating a table in MySQL will reset the autonumber/identity column. Unless your script explicitly inserts the autonumber from live into dev you are going to have a major problem on your hands.

If you can post some table structures and code I would be able to give you a better answer with some code.

UPDATE:

Just to clarify a little about autonumbers and MySQL. Lets create a table as follows:

```
CREATE TABLE `testcust` (
 `TestCustID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(32) DEFAULT NULL,
 PRIMARY KEY (`TestCustID`)
) ENGINE=InnoDB;
```

If I inserted data with the following query:

```
INSERT INTO `test`.`TestCust`
(`TestCustID`,
  `Name`
)
VALUES
(
  NULL,
 'Pieter'
);
```

And ran a select * on this table I would see something like this.

```
TestCustID     Name
 '1',          'Pieter'
```

Since I did not specify the TestCustID MySQl will generate one. If I ran the following insert query:

```
INSERT INTO `test`.`TestCust`
 (`TestCustID`,
  `Name`
 )
 VALUES
 (
     15,
     'Pieter'
 );
```

And then ran a select * on the table I would see

```
TestCustID     Name
 '1'          'Pieter'
 '15'         'Pieter'
```

So if you truncated your table it will reset the TestCustID to 1(starting from scratch).If you explicitly specify the autonumber from your production/live system in your insert statements you will be in sync if you dont specify your autonumbers in the insert statements you will go out of sync.

Hope that clarifies it a bit more.

MySQL table - to truncate or not to truncate?

Solution

Problem