MySQL auto increment

Question

Hi,

I have table with an auto-increment field, but I need to transfer the table to another table on another database. Will the value of field 1 be 1, that of field 2 be 2, etc?

Also in case the database get corrupted and I need to restore the data, will the auto-increment effect in some way? will the value change? (eg if the first row, id (auto-inc) = 1, name = john, country = UK .... will the id field remain 1?) I am asking because if other table refer to this value, all data will get out of sync if this field change.

Answer 1

SELECT INTO should keep the same ids on target table

http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-select-into-table.html

Answer 2

Using MySQL backup will do this, if you create your own insert statements make sure that you include your id field and that will insert the value (its not like MSSQL where you have to set identity_insert), a thing to watch for is that if you generate a DDL it sometimes generates "incrorectly" for your identity column (i.e. it states that starting point is at your last identity value? you may not want this behaviour).

Answer 3

It sounds like you are trying to separately insert data into two separate databases in the same order, and using the auto-increment field to link the two rows. It seems you are basically asking, is it OK to rely on the auto-increment being the same in both databases if the data is inserted in the same order.

If so, the answer is no - you cannot rely on this behaviour. It is legitimate for the auto-increment to skip a value, for example see here.

But maybe you are asking, can an auto-increment value suddenly change to another value after it is written and committed? No - they will not change in the future (unless of course you change them explicitly).

Does that answer your question? If not, perhaps you can explain your question again.

Answer 4

Transferring the data wouldn't be a problem, if you completely specify the auto_increment values. MySQL allows you to insert anything you want into an auto_increment field, but only does the actual auto_increment if the value you're inserting is 0 or NULL. At least on my 5.0 copy of MySQL, it'll automatically adjust the auto_increment value to take into account what you've inserted:

mysql> create table test (x int auto_increment primary key);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test (x) values (10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (x) values (null);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (x) values (0);   
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (x) values (5);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----+
| x  |
+----+
| 5  | <--inserted '5' (#4)
| 10 | <--inserted '10' (#1)
| 11 | <--inserted 'null' (#2)
| 12 | <--inserted '0' (#3)
+----+
3 rows in set (0.00 sec)

You can also adjust the table's next auto_increment value as follows:

mysql> alter table test auto_increment=500;
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into test (x) values (null);
Query OK, 1 row affected (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|              500 | 
+------------------+
1 row in set (0.01 sec)