Re: Copy entire table - Follow on question - Whats up with the numbering
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)