MySQL 我这里的 SQL 有什么问题？#1089 - 不正确的前缀键

What is wrong with my SQL here? #1089 - Incorrect prefix key

提问by sandorfalot

CREATE TABLE `table`.`users` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL,
    `password` VARCHAR(50) NOT NULL,
    `dir` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`(11))
) ENGINE = MyISAM;

I'm getting the #1089 - Incorrect prefix keyerror and can't figure out what I'm doing wrong. Help, please!

我收到#1089 - Incorrect prefix key错误，无法弄清楚我做错了什么。请帮忙！

回答by

In your PRIMARY KEY definition you've used (id(11)), which defines a prefix key - i.e. the first 11 characters only should be used to create an index. Prefix keys are only valid for CHAR, VARCHAR, BINARYand VARBINARYtypes and your idfield is an int, hence the error.

在您使用的 PRIMARY KEY 定义中(id(11))，它定义了一个前缀键 - 即仅应使用前 11 个字符来创建索引。前缀键仅对、和类型有效CHAR，并且您的字段是，因此出现错误。VARCHARBINARYVARBINARYidint

Use PRIMARY KEY (id)instead and you should be fine.

使用PRIMARY KEY (id)，而不是和你应该罚款。

MySQL reference hereand read from paragraph 4.

MySQL 参考此处并阅读第 4 段。

回答by Ralphkay

If you are using a GUI and you are still getting the same problem. Just leave the size value empty, the primary key defaults the value to 11, you should be fine with this. Worked with Bitnami phpmyadmin.

如果您使用的是 GUI 并且您仍然遇到同样的问题。只需将 size 值留空，主键默认值为 11，您应该没问题。与 Bitnami phpmyadmin 一起工作。

回答by Rafael Helizio Serrate Zago

This

这个

PRIMARY KEY (id(11))

主键 ( id(11))

is generated automatically by phpmyadmin, change to

由phpmyadmin自动生成，改为

PRIMARY KEY (id)

主键 ( id)

.

.

回答by Roshan Padole

CREATE TABLE `table`.`users` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL,
    `password` VARCHAR(50) NOT NULL,
    `dir` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`(11))
) ENGINE = MyISAM;

Change To

改成

CREATE TABLE `table`.`users` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `username` VARCHAR(50) NOT NULL,
        `password` VARCHAR(50) NOT NULL,
        `dir` VARCHAR(100) NOT NULL,
        PRIMARY KEY (`id`)
    ) ENGINE = MyISAM;

回答by Malith Ileperuma

Here the full solution step by step

这里一步一步的完整解决方案

• First of all you have to make the table by inserting all the data. id should AI ticked.
• then press go and #1089 error will be pop-up

• 首先，您必须通过插入所有数据来制作表格。id 应该 AI 打勾。
• 然后按 go 会弹出 #1089 错误

here is the solution

这是解决方案

• theres a button near go called preview SQL
• click that button and copy the sql code
• then click on SQL tab on top of the window
• Clear the text filed and paste that copied code there.
• you will be see (id (11))this on bottom of the code
• replace (id (11))into (id)
• and click go

• go 附近有一个按钮，称为预览 SQL
• 单击该按钮并复制 sql 代码
• 然后单击窗口顶部的 SQL 选项卡
• 清除提交的文本并将复制的代码粘贴到那里。
• 你会(id (11))在代码底部看到这个
• 替换(id (11))成(id)
• 然后点击去

boom now you will be fine

繁荣现在你会没事的

回答by Robot Boy

There is a simple way of doing it. This may not be the expert answer and it may not work for everyone but it did for me.

有一种简单的方法可以做到。这可能不是专家的答案，它可能不适用于所有人，但对我有用。

Uncheck all primary and unique check boxes, jut create a plain simple table.

取消选中所有主要和唯一复选框，只需创建一个简单的表格。

When phpmyadmin (or other) shows you the table structure, make the column primaryby the given button.

当 phpmyadmin（或其他）向您显示表结构时，通过给定的按钮将列设为主列。

Then click on changeand edit the settings of that or other colums like 'unique' etc.

然后单击更改并编辑该列或其他列的设置，例如“唯一”等。

回答by sh6210

In my case, i faced the problem while creating table from phpmyadmin. For id column i choose the primary option from index dropdown and filled the size 10.

就我而言，我在从 phpmyadmin 创建表时遇到了这个问题。对于 id 列，我从索引下拉列表中选择主要选项并填充大小为 10。

If you're using phpmyadmin, to solve this problem change the index dropdown option again, after reselecting the primary option again it'll ask you the size, leave it blank and you're done.

如果您正在使用 phpmyadmin，要解决此问题，请再次更改索引下拉选项，再次重新选择主要选项后，它会询问您的大小，将其留空即可完成。

回答by Issac Nguyen

It works for me:

这个对我有用：

CREATE TABLE `users`(
    `user_id` INT(10) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(255) NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`user_id`)
) ENGINE = MyISAM;       

回答by Arindam

Problem is the same for me in phpMyAdmin. I just created a table without any const. Later I modified the ID to a Primary key. Then I changed the ID to Auto-inc. That solved the issue.

问题对我来说在 phpMyAdmin 中是一样的。我刚刚创建了一个没有任何常量的表。后来我将 ID 修改为主键。然后我将 ID 更改为 Auto-inc。那解决了这个问题。

ALTER TABLE `users` CHANGE `ID` `ID` INT(11) NOT NULL AUTO_INCREMENT;

回答by hemant rao

When you give id as a primary key then a pop up is come and those aske you to how many size of this primary key. So you just leave blank because by default int value is set 11. Click then ok on those pop up without any enter a number. in this type of error never will you face in future. Thank you

当您将 id 作为主键时，会弹出一个窗口，询问您该主键的大小。因此，您只需将其留空，因为默认情况下 int 值设置为 11。单击然后确定弹出的那些没有任何输入数字。在这种类型的错误中，您将来永远不会遇到。谢谢