MySQL Forums
Forum List  »  Newbie

Re: Copy entire table - Follow on question - Whats up with the numbering
Posted by: Frank Thomson
Date: January 05, 2022 11:59PM

In response to
>> Where did 6 and 7 go?
>Not sure, but you need to be aware that auto_increment columns do not guarantee gapless sequentiality. If you want that, you need to code it.

Agreed. It seems so.

I even tried creating and copying all in one go
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f014af2b303c8e45d1dbb8e3599f747a



>CREATE TABLE new_table (id INT AUTO_INCREMENT PRIMARY KEY) SELECT * FROM old_table;


>SELECT * FROM old_table;
column1 column2
11 value 11
123 value 123
12 value 1234
13 value 1313

>SELECT * FROM new_table;
id column1 column2
1 11 value 11
2 123 value 123
3 12 value 1234
4 13 value 1313

>SHOW CREATE TABLE new_table;
Table Create Table
new_table CREATE TABLE `new_table` (
`id` int NOT NULL AUTO_INCREMENT,
`column1` int DEFAULT NULL,
`column2` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

>select max(id) from new_table;
max(id)
4

>insert into new_table set column1='10', column2='michaelangelo';


>select max(id) from new_table;
max(id)
8 <--- It jumps from 4 to 8

>SELECT * FROM new_table;
id column1 column2
1 11 value 11
2 123 value 123
3 12 value 1234
4 13 value 1313
8 10 michaelangelo

Options: ReplyQuote




Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.