How to Renumber Primary Key Indexes in MySQL for Sequential Values?

Renumbering Primary Key Indexes

A MySQL table can have a primary index (typically an 'id' column) whose values may not be numbered sequentially. To rectify this, consider the following approach:

Method:

Instead of manipulating temp tables, a more efficient technique is as follows:

SET @i=0;
UPDATE table_name SET column_name=(@i:=@i+1);

Explanation:

1. Set a user-defined variable @i to 0.
2. Run an UPDATE query on the table.
3. For each row in the table, assign the value of @i to the column_name column.
4. Increment @i by 1 after assigning the value.

Example:

Consider the following table:

id | name
----+--------
31  | John
35  | Mary
100 | David

The above method will update the table as follows:

id | name
----+--------
1   | John
2   | Mary
3   | David

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