Menu
I used to be able to set an auto-increment value to lower than the existing one after deleting rows in a table, but now I am unable to do so (I can only set it to higher than the current value). I'm wondering if this is because of upgrading MySQL (5.6.11)/PHPMyAdmin (3.5.8), or whether I should still be able to do this?
554
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.
To get the next auto increment id in MySQL, we can use the function last_insert_id() from MySQL or auto_increment with SELECT. Creating a table, with “id” as auto-increment.
If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value is not reused for a MyISAM or InnoDB table. If you delete all rows in the table with DELETE FROM tbl_name (without a WHERE clause) in autocommit mode, the sequence starts over for all storage engines except InnoDB and MyISAM.
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 reset the hidden value by restarting the MySQL server. InnoDB recalculates the auto-increment value after restart.
Update your rows, and restart the mysql deamon. This prevents you from rebuilding your table what can be a hastle with big tables.
182
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
