SET @i=0; UPDATE tbl SET keycol=(@i:=@i+1);But 8.0 deprecates this use of user variables, warning that support for it will go away. And in any case, what you more often need is a mechanism for guaranteeing an unbroken sequence of integer key values whenever a row is inserted.
First the bad news: auto_increment does not provide this logic, for example it does not prevent insertion failures, editing of the column, or subsequent deletion.
The good news is that the logic for guaranteeing an unbroken sequence is straightforward:
(i) use InnoDB
(ii) create a table with the desired sequence in one column and further column(s) to track use (to use such a table for sequential columns in multiple other tables, provide a tracking column for each such table)
(iii) write Triggers to fetch the next available sequential key from that table, mark it used, and assign it on Insert
(iv) put all such logic inside transaction blocks
(v) prohibit deletion in the table.
Last updated 9 Jan 2025
