[ACCEPTED]-What happens when autoincrement clashes with existing data in MySQL?-mysql

Accepted answer

Score: 10

Autoincrement will use the next available 5 id for both InnoDB and MyISAM tables but 4 you can manually change it's next position 3 in the following way:

After your insert, you 2 could set the auto increment to a value 1 above the now-highest id:

ALTER TABLE tbl AUTO_INCREMENT = 9000;

Score: 7

Autoincrement will use the next available id for both InnoDB and MyISAM tables.

I have tested this for MySQL 4.1.22 running 9 on Windows Vista. I created two simple tables, one 8 using InnoDB and one using MyISAM. Each 7 had an autoincrementing primary key called 6 'id' and a varchar column called 'description'.

I 5 ran the following commands (with no errors):

INSERT INTO MyIsamTest (description)     VALUES ('autoincrement id insert'); 
INSERT INTO MyIsamTest (id, description) VALUES (100, 'manual id insert');
INSERT INTO MyIsamTest (description)     VALUES ('autoincrement id insert');

SELECT * FROM MyIsamTest;

I 4 got the following result, which shows that 3 the 'id' column was correctly autoincremented:

+=====+=========================+
| id  | description             |
+=====+=========================+
|   1 | autoincrement id insert |
+-----+-------------------------+
| 100 | manual id insert        |
+-----+-------------------------+
| 101 | autoincrement id insert |
+-----+-------------------------+

I 2 repeated the experiment on my InnoDbTest 1 table with the same outcome.

Score: 0

If you only have one legacy table with no 5 dependencies on the ids, then what I'd do 4 is create a temporary table to insert all 3 your new data into (with the IDs 5000+). Then 2 run this:

INSERT INTO `myrealtable` (column1, column2, column3)
SELECT column1, column2, column3
FROM `temptable`;

DROP TABLE `temptable`;

...where none of the columnX columns 1 are the primary auto_increment id.

Score: 0

I believe MySQL checks if you insert on 5 an auto-incremented column and will update 4 AUTO_INCREMENT so that AUTO_INCREMENT > MAX(id), but 3 I need to look through the docs. You should 2 still follow Andrew Duffy's suggestion, to 1 be safe.

Score: 0

MySQL's doesn't allow you to set the internal 7 auto_increment "value" lower than the current 6 highest ID.

Therefore, if you add 1000 rows 5 starting at 5000, the increment is set to 4 6000. You can still add rows with IDs that 3 don't exist yet (e.g. 4500) but it's not 2 worth bothering, really. There are plenty 1 of numbers between 6000 and 4 billion.

Score: 0

If you insert data with already assigned 6 primary keys into MyISAM, the value of the 5 AUTO_INCREMENT column for the next insert 4 will be max(column) + 1, so it will work.

However 3 you are not using MyISAM since this is important 2 data, you are using InnoDB, which needs 1 the ALTER TABLE statement quoted above.

Source: stackoverflow.com

[ACCEPTED]-What happens when autoincrement clashes with existing data in MySQL?-mysql

Best way to archive live MySQL database

Foreign keys in MySQL?

Store GUID in MySQL from C#

MySQL backup and restore from command line

Creating your own TinyURL

What is the best way to bind decimal / double / float values with PDO in PHP?

Unique constraint that allows empty values in MySQL

Why I am getting Cannot pass parameter 2 by reference error when I am using bindParam with a constant value?

How do I view the warning(s) after a SELECT query using the MySQL command line?

Java, MySQL: Is there a way to embed a MySQL server with a Java program?