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 08:32PM

Hi guys,

You will see here the same sequence of commands as in an earlier post of mine.
I create a table with a id field that is auto increment and a primary key.
Then I populate it by copying some data from another table. That's where I got to above.
I check the maximum value of the id field and it is 5 as expected.
Then I insert one new row and the id field becomes 8

Where did 6 and 7 go?

Then I add one more row and it goes to 9 as it should.

Normally I would not worry about something like this, but when I do this on my real table which has 10855 lines, when I insert just one more, I get the id jump to 16384. One more and it goes to 16385 as expected. So having what appears to be 5000 more lines makes me curious.



> CREATE TABLE new_table ( id INT(11) NOT NULL AUTO_INCREMENT , PRIMARY KEY (id)) ;
Query OK, 0 rows affected (0.12 sec)

> INSERT INTO new_table SELECT * FROM employees;
ERROR 1136 (21S01): Column count doesn't match value count at row 1

> INSERT INTO new_table(employee_number,employee_name) SELECT * FROM employees;
ERROR 1054 (42S22): Unknown column 'employee_number' in 'field list'

> ALTER TABLE `new_table` ADD `employee_number` INT(11) NOT NULL AFTER `id`, ADD `employee_name` VARCHAR(100) NOT NULL AFTER `employee_number`;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0

> INSERT INTO new_table(employee_number,employee_name) SELECT * FROM employees;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0


> select max(id) from new_table;
+---------+
| max(id) |
+---------+
| 5 |
+---------+
1 row in set (0.00 sec)

> insert into new_table set employee_number='10', employee_name='michaelangelo';
Query OK, 1 row affected (0.01 sec)

> select max(id) from new_table;
+---------+
| max(id) |
+---------+
| 8 |
+---------+
1 row in set (0.00 sec)

MariaDB [syndetic]> insert into new_table set employee_number='11', employee_name='leonardo';
Query OK, 1 row affected (0.01 sec)

MariaDB [syndetic]> select max(id) from new_table;
+---------+
| max(id) |
+---------+
| 9 |
+---------+
1 row in set (0.00 sec)

> select * from new_table;
+----+-----------------+-----------------+
| id | employee_number | employee_name |
+----+-----------------+-----------------+
| 1 | 1 | Frank |
| 2 | 2 | Melany |
| 3 | 3 | mike |
| 4 | 4 | Richard |
| 5 | 6 | Cameron |
| 8 | 10 | michaelangelo |
| 9 | 11 | leonardo |
+----+-----------------+-----------------+
7 rows in set (0.00 sec)

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.