Menu
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
That document I'm reading seems to say something like:
"In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group."
I don't really understand what's being said there. Aren't the values supposed to be reused automatically?
Thanks in advance...
958
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.
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. VALUES ('Lars','Monsen'); The SQL statement above would insert a new record into the "Persons" table.
You use TRANCATE table to empty the table. TRUNCATE not only deletes the rows but resets the auto increment value by design. Use DELETE FROM table instead.
You can't have two auto-increment columns.
In MySQL, you can reset auto increment values in various ways. First, create a table named tmp and assign the AUTO_INCREMENT attribute to the id primary key column. We have three rows with values of ID column are 1, 2, and 3. Perfect! It is time to practice reset the auto-increment value of the ID column.
Typically, you use the AUTO_INCREMENT attribute for the primary key column of the table. Whenever you insert a new row into a table, MySQL automatically assigns a sequence number to the AUTO_INCREMENT column.
"In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group." I don't really understand what's being said there.
The syntax of the ALTER TABLE statement to reset the auto increment value is as follows: ALTER TABLE table_name AUTO_INCREMENT = value; Code language: SQL (Structured Query Language) (sql) You specify the table name after the ALTER TABLE clause and the value which you want to reset to in the expression AUTO_INCREMENT=value.
InnoDB resets the auto_increment field when you restart the database.
When InnoDB restarts, it finds the highest value in the column and then starts from there.
This won't happen in MyISAM because it caches the last incremented id.
Update
This feature/bug has been around since 2003 and can lead to serious issues. Take the example below,
Table t1 has an auto-inc primary key.
Table t2 has a column for the primary key in t1 without a foreign key "constraint". In other words, when a row is deleted in t1 the corresponding rows in t2 are orphaned.
As we know with InnoDB restart, an id can be re-issued. Therefore orphaned rows in t2 can be falsely linked to new rows in t1.
This bug has been finally fixed in MySQL 8.0.0 WL#6204 - InnoDB persistent max value for autoinc columns.
InnoDB will keep track of the maximum value and on restart preserve that max value and start from there.
178
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
