MySQL 更改表添加主键语法错误的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19949842/
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
MySQL alter table add column with primary key syntax error
提问by Agonius
I'm trying to add a column to one of of my database tables, but there is a syntax error and I can't seem to find the problem...
我正在尝试向我的数据库表之一添加一列，但存在语法错误，我似乎无法找到问题...
My current database table looks like this:
我当前的数据库表如下所示：
component + tag_id + item_id
------------|----------|-----------
com_content | 23 | 2642
com_content | 26 | 3481
com_content | 35 | 1868
com_content | 85 | 5827
com_content | 89 | 7882
I want it to look like this, where 'id' is auto increment and all columns part of the primary key
我希望它看起来像这样，其中“id”是自动增量，所有列都是主键的一部分
id + component + tag_id + item_id
-----|--------------|----------|-----------
1 | com_content | 23 | 2642
2 | com_content | 26 | 3481
3 | com_content | 35 | 1868
4 | com_content | 85 | 5827
5 | com_content | 89 | 7882
This is my query:
这是我的查询：
DROP PRIMARY KEY
ALTER TABLE gitags_items
ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST
PRIMARY KEY (id,component,tag_id,item_id)
However I'm getting this error message:
但是我收到此错误消息：
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIMARY KEY ALTER TABLE gitags_items ADD COLUMN id INT NOT NULL AUTO_INC' at line 1
Any help/pointers would be much appreciated
任何帮助/指针将不胜感激
回答by symcbean
The 'ALTER TABLE' bit must come first, and then each part must be seperated by a comma:
'ALTER TABLE' 位必须先出现，然后每个部分必须用逗号分隔：
ALTER TABLE gitags_items
DROP PRIMARY KEY,
ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (id,component,tag_id,item_id);
but I'm not sure if you can drop and create a primary key in the same staatement.
但我不确定您是否可以在同一个声明中删除和创建主键。
回答by Kilasa Mj
This one works fine, there was the problem with the commas, theres no need to drop the primary key since your going to set one yourself
这个工作正常，逗号有问题，没有必要删除主键，因为你要自己设置一个
ALTER TABLE gitags_items ADD id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY(id,component,tag_id,item_id);
回答by Sahin S.
Try to add the following code to my.cnf
尝试将以下代码添加到my.cnf
sql-mode="NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO"
then, restart MySQL
然后，重新启动 MySQL
# service mysqld restart
回答by Ashok Maharjan
First you have to make 'id' column primary key before setting autoincrement.
首先，您必须在设置自动增量之前制作“id”列主键。
