CODEWITHSUNDEEP
mysqlauto-increment
masohaso
masohaso

Reputation: 35

bad autoincrementation when copying tables in mysql

I have two tables in my databaes: original_db and archive_db.

in original_db I have two cols:

  1. id (auto-increment)
  2. name

in archive_db I have three cols:

  1. id (auto-increment)
  2. user_id
  3. name

I want to copy original_db to archive_db when user_id is let's say '1'

sql query:

INSERT INTO archive_db (user_id, name)
SELECT '1', name FROM original_db

After query this at first time everything is ok. So I Have archive_db:

  1. id (auto-increment) [1,2,3]
  2. user_id [1,1,1]
  3. name [nam1,name2,name3]

but when I do again this query with another user_id let's say '2':

    INSERT INTO archive_db (user_id, name)
    SELECT '2', name FROM original_db

I get this archive_db table:

  1. id (auto-increment) [1, 2, 3, 7, 8, 9]
  2. user_id [1, 1, 1, 2, 2, 2]
  3. name [name1, name2, name3, name1, name2, name3]

Why the id has the wrong autoincrementation, it should be 1,2,3,4,5,6

Upvotes: 0

Views: 62

Answers (1)

OtiK.cz
OtiK.cz

Reputation: 95

And didn't 3 rows be deleted during the test? Then just set autoincrement: ALTER TABLE archive_db AUTO_INCREMENT = 4;

Upvotes: 1

Related Questions