Menu
I have an image gallery which website members can upload images to. When an image is uploaded, a MySQL row is written, containing various pieces of information about the image, member, etc. This row uses AUTO_INCREMENT to create its ID, so that getimage.php?id=XX can fetch the image to be displayed.
I loop through the IDs with a for-loop to display the images within the gallery.
If I delete the 5th row/image, for example, the AUTO_INCREMENT goes from 123456789 to 12346789.
I would like to re-assign the ID to each row in the MySQL table, starting from the ground up. So 12346789 becomes 12345678. How would I achieve this?
404
ALTER TABLE suppliers AUTO_INCREMENT = 1; This example would change the next value in the AUTO_INCREMENT field (ie: next value in the sequence) to 1 for the supplier_id column in the suppliers table.
To reset auto-incrementing column, use TRUNCATE TABLE command. After that, on insertion, it will reset the column.
Yes if the field value is automatically incremented; otherwise, No. The default value is Yes. If you want to manually assign a value to a field that has the AutoIncrement property set to Yes, you must be member of the SQL Server db_owner database permission set.
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.
I found this to work perfectly and quite quickly so here it is:
ALTER TABLE tablename DROP id
ALTER TABLE tablename ADD id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id), AUTO_INCREMENT=1
I know this isn't the proper approach however for my specific situation this is exactly what was needed. There is nothing within the table I was using that is referred to either from or to another table.
82
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