如何避免出现此 MySQL 错误列 COLUMN NAME 的列说明符不正确？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26134912/
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
How can I avoid getting this MySQL error Incorrect column specifier for column COLUMN NAME?
提问by JasonDavis
How can I avoid getting this MySQL error Incorrect column specifier for column topic_id?
如何避免出现此 MySQL 错误列 topic_id 的列说明符不正确？
MySQL Error...
MySQL错误...
#1063 - Incorrect column specifier for column 'topic_id'
SQL Schema...
SQL 架构...
CREATE TABLE discussion_topics (
topic_id char(36) NOT NULL AUTO_INCREMENT,
project_id char(36) NOT NULL,
topic_subject VARCHAR(255) NOT NULL,
topic_content TEXT default NULL,
date_created DATETIME NOT NULL,
date_last_post DATETIME NOT NULL,
created_by_user_id char(36) NOT NULL,
last_post_user_id char(36) NOT NULL,
posts_count char(36) default NULL,
PRIMARY KEY (topic_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
回答by M.Svrcek
To use AUTO_INCREMENTyou need to deifne column as INTor floating-point types, not CHAR.
要使用，AUTO_INCREMENT您需要将列定义为INT浮点类型，而不是CHAR.
AUTO_INCREMENTuse only unsigned value, so it's good to use UNSIGNEDas well;
AUTO_INCREMENT只使用无符号值，所以也很好用UNSIGNED；
CREATE TABLE discussion_topics (
topic_id INT NOT NULL unsigned AUTO_INCREMENT,
project_id char(36) NOT NULL,
topic_subject VARCHAR(255) NOT NULL,
topic_content TEXT default NULL,
date_created DATETIME NOT NULL,
date_last_post DATETIME NOT NULL,
created_by_user_id char(36) NOT NULL,
last_post_user_id char(36) NOT NULL,
posts_count char(36) default NULL,
PRIMARY KEY (topic_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
回答by Mureinik
The auto_incrementproperty only works for numeric columns (integer and floating point), not charcolumns:
该auto_increment属性仅适用于数字列（整数和浮点数），不适用于char列：
CREATE TABLE discussion_topics (
topic_id INT NOT NULL AUTO_INCREMENT,
project_id char(36) NOT NULL,
topic_subject VARCHAR(255) NOT NULL,
topic_content TEXT default NULL,
date_created DATETIME NOT NULL,
date_last_post DATETIME NOT NULL,
created_by_user_id char(36) NOT NULL,
last_post_user_id char(36) NOT NULL,
posts_count char(36) default NULL,
PRIMARY KEY (topic_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
回答by J C
I was having the same problem, but using Long type. I changed for INT and it worked for me.
我遇到了同样的问题，但使用的是 Long 类型。我换了 INT，它对我有用。
CREATE TABLE lists (
id INT NOT NULL AUTO_INCREMENT,
desc varchar(30),
owner varchar(20),
visibility boolean,
PRIMARY KEY (id)
);
回答by raina77ow
Quoting the doc:
引用文档：
Some attributes do not apply to all data types.
AUTO_INCREMENTapplies only to integer and floating-point types.DEFAULTdoes not apply to theBLOBorTEXTtypes.
某些属性不适用于所有数据类型。
AUTO_INCREMENT仅适用于整数和浮点类型。DEFAULT不适用于BLOB或TEXT类型。
In your case, you're trying to apply AUTO_INCREMENTmodifier to charcolumn. To solve this, either drop AUTO_INCREMENTaltogether (that means you'll have to generate a unique id on the application level) or just change topic_idtype to the relevant integer one.
在您的情况下，您正在尝试将AUTO_INCREMENT修饰符应用于char列。要解决这个问题，要么AUTO_INCREMENT完全删除（这意味着您必须在应用程序级别生成一个唯一的 id），要么只是将topic_id类型更改为相关的整数。
As a sidenote, it makes little sense using char(36)to store the posts count, so that column's type probably has to be changed as well. It looks like you're going this way to prevent integer overflow - but if you're dealing with more than 18446744073709551615posts (the biggest number that can be stored in BIGINT UNSIGNEDcolumn) in a single topic, you have far bigger problem on your side probably. )
作为旁注，使用char(36)存储帖子计数几乎没有意义，因此该列的类型可能也必须更改。看起来您正在采取这种方式来防止整数溢出 - 但如果您在单个主题中处理多个18446744073709551615帖子（可以存储在BIGINT UNSIGNED列中的最大数字），那么您可能会遇到更大的问题。)
回答by codebot
You cannot auto increment the charvalues. It should be intor long(integers or floating points).
Try with this,
您不能自动增加这些char值。它应该是int或long（整数或浮点数）。试试这个，
CREATE TABLE discussion_topics (
topic_id int(5) NOT NULL AUTO_INCREMENT,
project_id char(36) NOT NULL,
topic_subject VARCHAR(255) NOT NULL,
topic_content TEXT default NULL,
date_created DATETIME NOT NULL,
date_last_post DATETIME NOT NULL,
created_by_user_id char(36) NOT NULL,
last_post_user_id char(36) NOT NULL,
posts_count char(36) default NULL,
PRIMARY KEY (`topic_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
Hope this helps
希望这可以帮助
