如何更新表以添加主键并更新具有增量ID的所有现有行？ [英] How do I update a table to add a primary key and update all of the existing rows with incremented IDs?

问题描述

我有一个表格，我导入了20,000行数据，但我忘了在其上放置主键，因此每一行都有一个唯一键。

I have a table with 20,000 rows of data that I imported but I forgot to put a primary key on it so that each row has a unique key.

我希望第一行从ID 1开始，一直增加到最后一行，并以ID 20000结束。如何使用单个查询更新所有行？

I want the first row to start at ID 1 and increment all the way up to the last row and finish at ID 20000. How do I update all of the rows with a single query?

我使用MySQL。已尝试使用PhpMyAdmin但不会这样做。

I'm using MySQL. Have tried using PhpMyAdmin but it wouldn't do it.

推荐答案

添加新的ID列主键，并且不打开自动增量）运行：

After adding a new ID column (don't set as a primary key just yet, and don't turn on auto increment) run:

SET @index = 1;
UPDATE tablename SET ID = (@index:=@index+1);

这会设置从1开始的每个现有行的递增ID值，从而解决重复的键问题

This sets an incrementing ID value starting from 1 onto each of your existing rows thus solving the duplicate key issue you would face if you tried to insert a new primary key column after data has been already entered.

完成后，您可以将ID列设置为主键，并将其设置为自动增量。

Once this is done you can set the ID column as a primary key with auto increment.

这篇关于如何更新表以添加主键并更新具有增量ID的所有现有行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！