Avatar of ncw
ncw

asked on 

Reindex MySql table

I have a MySql table with an auto-increment id field, and in the table I have 4 records that do not change and have ids 1 to 10. The table also contain another 20+ records with id's eg 11-31. The last 20 records will be regularly deleted and re-iniserted with slightly different data, is there anyway of resetting the autoincrement back to the last record id+1, using Php without creating a new table?
MySQL Server

Avatar of undefined
Last Comment
johanntagle
ASKER CERTIFIED SOLUTION
Avatar of Lukasz Chmielewski
Lukasz Chmielewski
Flag of Poland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of ncw
ncw

ASKER

This option from your link could be usefull:

ALTER TABLE tbl AUTO_INCREMENT = 100;
Should reset the autoincrement

    $q = "alter table test AUTO_INCREMENT = 2";
    $r = mysql_query($q);
Avatar of johanntagle
johanntagle
Flag of Philippines image

Deleted and reinserted with slightly different data?  So why not just update the rows instead of all that trouble?
Avatar of ncw
ncw

ASKER

Deleting and reinserting data causes the id to start at the next auto-incremented id unless you recreate the table or alter the auto-increment start value, as far as I know.
Avatar of johanntagle
johanntagle
Flag of Philippines image

Deleting and reinserting data causes the id to start at the next auto-incremented id unless you recreate the table or alter the auto-increment start value, as far as I know.

Exactly.  My point is, why delete and reinsert at all when you can just update the rows to reflect the slightly different data?
MySQL Server
MySQL Server

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.

49K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo