(This question is related to Why do I get error "1062 Duplicate entry" when copying tables?, but I have experimented further and now have this question).
I have a table of wines that I wish to copy to a backup table. In the original table I have an empty record with id=0 that is copied in an unexpected way (the empty record is needed in my application, so I can't simply delete or move it). I guess that this has to do with the auto-increment option for the id column.
My table that I try to copy has the following contents:
Table: "mywines"
+----+--------------------------------+------------------+------------+
| id | name | winery | grapes |
+----+--------------------------------+------------------+------------+
| 0 | | | | <-- empty record!
| 1 | Champagne Tentation | A. Bergère | |
| 2 | Champagne Les Clos | A. Bergère | Menunier |
| 4 | Champagne Cuvée Selection | Collard-Picard | |
| 8 | Les Vaupulans Chablis 1er Cru | Vrignaud | Chardonnay |
| 9 | Les Cotes de Fontenay 1er Cru | Vrignaud | Chardonnay |
| 10 | Domaine Sainte Claire | Jean-Marc Brocard| Chardonnay |
+----+--------------------------------+------------------+------------+
The table is constructed and designed according to the following SQL script:
CREATE TABLE `mywines` (
`id` int(11) NOT NULL,
`name` varchar(200) NOT NULL DEFAULT '',
`winery` varchar(100) DEFAULT '',
`grapes` varchar(200) DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `mywines` (`id`, `name`, `winery`, `grapes`) VALUES
(0, '', '', ''),
(1, 'Champagne Tentation', 'A. Bergère', ''),
(2, 'Champagne Les Clos', 'A. Bergère', 'Menunier'),
(4, 'Champagne Cuvée Selection', 'Collard-Picard', ''),
(8, 'Les Vaupulans Chablis 1er Cru', 'Vrignaud', 'Chardonnay'),
(9, 'Les Cotes de Fontenay 1er Cru', 'Vrignaud', 'Chardonnay'),
(10, 'Domaine Sainte Claire', 'Jean-Marc Brocard', 'Chardonnay');
ALTER TABLE `mywines`
ADD PRIMARY KEY (`id`);
ALTER TABLE `mywines`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
Now... I copy this table to a backup copy. I run these SQL-commands:
DROP TABLE IF EXISTS `mywines_copy`;
CREATE TABLE `mywines_copy` LIKE `mywines`;
INSERT `mywines_copy` SELECT * FROM `mywines`;
SELECT * FROM mywines_copy;
The last SELECT statement shows the following table:
Table: "mywines"
+----+--------------------------------+------------------+------------+
| id | name | winery | grapes |
+----+--------------------------------+------------------+------------+
| 1 | Champagne Tentation | A. Bergère | |
| 2 | Champagne Les Clos | A. Bergère | Menunier |
| 4 | Champagne Cuvée Selection | Collard-Picard | |
| 8 | Les Vaupulans Chablis 1er Cru | Vrignaud | Chardonnay |
| 9 | Les Cotes de Fontenay 1er Cru | Vrignaud | Chardonnay |
| 10 | Domaine Sainte Claire | Jean-Marc Brocard| Chardonnay |
| 11 | | | | <-- The empty record?
+----+--------------------------------+------------------+------------+
All record id:s are the same as in the original table, but the empty record is renumbered to id=11... Why is this record renumbered? How can I prevent it from being renumbered? I want it to have id=0 even after the copy.
