Resetting MySQL Primary Key in Chronological Order

As longtime readers of asisaid know, this blog — and my other sites — run a custom content management system called SAFARI that I started writing way back in 1999. For a long time, SAFARI evaded spam comments simply because most spammers targeted WordPress and other vastly more common CMSes. However, at some point in the mid-2010’s as I started serving as a pastor and professor, this blog went into something of a hibernation and the spammers went crazy with it. I later implemented more serious anti-spam measures, but there were already thousands of junk comments to deal with. I recently realized that my panicked hacking away at the comments back then wiped out nearly 4,000 early comments to the blog that were not spam.

I wanted to restore them from a backup I dug up, but since more comments have been posted since then, the unique id number given to each comment had been given away to other comments. The database table that stores the comments is set to auto-increment the ids, so I could remove the ids from the backed up comments before restoring them, but the result bugged me: older comments that were restored had higher id numbers than the newer comments. It didn’t ruin the function, but it bugged me.

So, I looked for a way to regenerate the unique ids and to do so in chronological order. I combined several of the answers on this Stack Overflow page to achieve the desired result:

SET @count = 0;
ALTER TABLE `uninet_comments` DROP `cid`;
ALTER TABLE `uninet_comments` ADD `cid` int UNSIGNED NOT NULL FIRST;
UPDATE `uninet_comments` SET `uninet_comments`.`cid` = @count:= @count + 1 ORDER BY `gmt` ASC;
ALTER TABLE `uninet_comments` ADD PRIMARY KEY(`cid`);

I figured I would share the queries I used in case anyone else needs to accomplish the same thing on a database.