Menu
I have a MySQL table with an autoincremented id column. The id started from 1 and is now in the 4000s.
However, I also need to port some legacy data into this table from an old version of the application. The ids of this data start from 5000 and must be preserved for auditing purposes.
What happens if I insert an entry after my autoincrement counter is up to 4999? Is autoincrement smart enough to look for the next available id, or will it crash because it tries to insert id 5000, which already exists?
While advice on how to work around this problem is very helpful, I'd also like to understand what MySQL would do in this situation and if I need to intervene at all.
599
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
When the AUTO_INCREMENT column reaches the upper limit of data type then the subsequent effort to generate the sequence number fails. That is why it is advised to use a large enough integer data type for the AUTO_INCREMENT column to hold the maximum sequence value required by us.
You can't have two auto-increment columns.
In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name. The name of the table whose AUTO_INCREMENT value you wish to change.
MySQL AUTO_INCREMENT Keyword MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. The following SQL statement defines the "Personid" column to be an auto-increment primary key field in the "Persons" table:
Missing auto increment values are not necessarily an indication that a query failed to insert data — it could just be that the data already existed in the table. That is technically still a failure, but it is an expected failure.
When auto_increment is skipping values it is often because of a failed insert query. These gaps between values can also occur if someone has been deleting rows from the MySQL table.
When that is full, you have probably set the record of having the most data in a MySQL database ever. Assuming you don't mess around with the auto_increment column and just let it do it's work. What I mean to say is, that you don't have to worry about that. Have you looked at how huge the highest possible bigint value is?
Autoincrement will use the next available id for both InnoDB and MyISAM tables but you can manually change it's next position in the following way:
After your insert, you could set the auto increment to a value above the now-highest id:
ALTER TABLE tbl AUTO_INCREMENT = 9000;
Autoincrement will use the next available id for both InnoDB and MyISAM tables.
I have tested this for MySQL 4.1.22 running on Windows Vista. I created two simple tables, one using InnoDB and one using MyISAM. Each had an autoincrementing primary key called 'id' and a varchar column called 'description'.
I 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 got the following result, which shows that the 'id' column was correctly autoincremented:
+=====+=========================+
| id | description |
+=====+=========================+
| 1 | autoincrement id insert |
+-----+-------------------------+
| 100 | manual id insert |
+-----+-------------------------+
| 101 | autoincrement id insert |
+-----+-------------------------+
I repeated the experiment on my InnoDbTest table with the same outcome.
25
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With