|You have a table tbl with a column whose values can be freely overwritten and which needs to be filled with perfectly sequential values starting with 1:
SET @i=0; UPDATE tbl SET keycol=(@i:=@i+1);But more often, what you need is a mechanism for guaranteeing an unbroken sequence of integer key values whenever a row is inserted.
Here is the bad news:
(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 17 Aug 2019