MySQL 表存在后添加新ID（自动增量）的麻烦
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4829400/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Troubles of adding a new ID (auto increment) after table exist
提问by Bobo
I have a table with 38.000 records, but without any auto increment column like ID.
Now I have to add an IDcolumn, and I'm wondering could there be troubles?
我有一个包含 38.000 条记录的表，但没有像ID.
现在我要加一ID栏，我想知道会不会有麻烦？
采纳答案by Gregory A Beamer
You problem with definition change to any field is the change is "destructive". It is best to treat changes like this as a data migration. I have not done this with MySQL, but have had to add auto numbering to SQL Server. The basic idea is the same.
您对任何字段的定义更改的问题是更改是“破坏性的”。最好将这样的更改视为数据迁移。我没有用 MySQL 这样做过，但不得不为 SQL Server 添加自动编号。基本思想是一样的。
Check the documentation, as MySQL may have a mechanism for adding aut increment without resetting the field. If so, doing it via SQL will solve the problem. In general, I recommend against doing these types of changes visually, with a tool, as most tools are very destructive in their methdology.
检查文档，因为 MySQL 可能具有在不重置字段的情况下添加自动增量的机制。如果是这样，通过 SQL 执行此操作将解决问题。一般来说，我建议不要使用工具在视觉上进行这些类型的更改，因为大多数工具在其方法论上都非常具有破坏性。
回答by A.Baudouin
You can add do that without problem only if your table doesn't have relationship with others.
仅当您的表与其他人没有关系时，您才可以毫无问题地添加。
You must remove the old primary key and upload the table accordingly (perhaps add an unique index on the old primary key).
您必须删除旧的主键并相应地上传表（可能在旧的主键上添加唯一索引）。
Proceed like that :
像这样进行：
Make a dump of your database
Remove the primary key like that
转储您的数据库
像这样删除主键
ALTER TABLE XXX DROP PRIMARY KEY
- Add the new column like that
- 像这样添加新列
ALTER TABLE XXX add column Id INT NOT NULL AUTO_INCREMENT FIRST, ADD primary KEY Id(Id)
The table will be looked and the AutoInc updated.
将查看该表并更新 AutoInc。
回答by GoingDrupal
This will add an auto increment ID to your MySQL table:
这将为您的 MySQL 表添加一个自动增量 ID：
ALTER TABLE `your_table_name` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
Depending on the amount of data on your table it could take a few minutes to complete.
根据您表上的数据量，可能需要几分钟才能完成。
回答by yurislav
This is the solution that i tried with MySQL Workbench:
这是我尝试使用 MySQL Workbench 的解决方案：
ALTER TABLE `tableName` CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT ;
I don't know if this is correct way, but I didn't notice any problem in my Java EE Application yet.
我不知道这是否正确，但我还没有注意到我的 Java EE 应用程序中有任何问题。
回答by Selah
I work with very large data and had an id column filled with NULLS. I chose to run the following SQL to fill with numbers... then I set the id column to be my primary key.
我处理非常大的数据，并且有一个填充了 NULL 的 id 列。我选择运行以下 SQL 来填充数字...然后我将 id 列设置为我的主键。
set @row = 0;
UPDATE philadelphia.msg_geo_sal SET id = @row := @row + 1;
回答by David
"could there be troubles?"
“会不会有麻烦？”
There could be troubles just inserting a record. However...
仅插入记录可能会出现问题。然而...
Generally, adding a new field usually is pretty safe to do, whether it's an auto-incrementing column or not. It's changing or deleting columns that are the most likely to cause you issues if you're not sure of all the dependencies.
通常，添加新字段通常是非常安全的，无论它是否是自动递增的列。如果您不确定所有依赖项，它会更改或删除最有可能导致您出现问题的列。
To be safe, however, I'd try it on a copy (test) version first and run your app(s) against the test version first, just to be safe. It's good practice to use a test environment no matter what anyway.
但是，为了安全起见，我会先在副本（测试）版本上尝试，然后首先针对测试版本运行您的应用程序，以确保安全。无论如何，最好使用测试环境。
Can you be more specific about what types of troubles you're worried about?
您能否更具体地说明您担心的问题类型？
回答by Phuc
You may need to add it as primary key first before you can make it as auto increase field
您可能需要先将其添加为主键，然后才能将其设为自动增加字段
You can look at this example
你可以看看这个例子
ALTER TABLE `category`
ADD PRIMARY KEY (`cat_id`);
ALTER TABLE `category`
MODIFY `cat_id` int(11) NOT NULL AUTO_INCREMENT;
