How can I renumber a MySQL primary index with non-sequential values in ascending order?

Renumbering Primary Index for Orderly Data

Question:

You have a MySQL table with a primary index whose values are not numbered sequentially. How can you renumber them in order (1, 2, 3, ...)?

Answer:

While other methods are available, here is an alternative approach that does not require creating temporary tables.

Solution:

Execute the following SQL statements:

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

Explanation:

• The first line initializes a session variable @i to 0.
• The second line performs an UPDATE query on the table named table_name.
• Within the UPDATE query, the SET clause assigns a new value to the primary index column column_name.
• The new value is calculated by incrementing the session variable @i by 1.
• As the query executes, the @i variable keeps incrementing, ensuring that the primary index values are renumbered sequentially.

The above is the detailed content of How can I renumber a MySQL primary index with non-sequential values in ascending order?. For more information, please follow other related articles on the PHP Chinese website!