How to Renumber a Primary Index for Orderly Sequential Values in MySQL?

Renumbering Primary Index for Orderly Sequential Values

If your MySQL table's primary index (id) appears in an inconsistent order (e.g., 1, 31, 35, 100), you may desire to rearrange them into a sequential series (1, 2, 3, 4).

To accomplish this, you can employ the following approach without creating a temporary table:

<code class="sql">SET @i = 0;
UPDATE table_name SET column_name = (@i := @i + 1);</code>

This SQL statement initiates a counter variable @i to 0. The UPDATE operation iterates through the table_name and assigns each row's column_name column with the incremented value of the counter.

The above is the detailed content of How to Renumber a Primary Index for Orderly Sequential Values in MySQL?. For more information, please follow other related articles on the PHP Chinese website!