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


Subject
Written By
Posted
January 04, 2022 07:53PM
January 04, 2022 09:29PM
January 05, 2022 01:45AM
Re: Copy entire table - Follow on question - Whats up with the numbering
January 05, 2022 11:59PM
January 05, 2022 12:04PM
January 05, 2022 02:44PM
January 05, 2022 03:39PM


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.