CREATE TABLE `movies`.`movie`
( `movie_id` INT(3) NULL AUTO_INCREMENT, `movie_name` VARCHAR(25) NULL,
`movie_embedded_id` VARCHAR(50) NULL, `rating_no` INT(3) NULL,
`movie_description` VARCHAR(50) NULL, PRIMARY KEY (`movie_id`(3))) ENGINE = InnoDB;
Я продолжаю получать эту ошибку:
# 1089 — Неправильный префиксный ключ; используемая ключевая часть не является строкой, используемая длина длиннее ключевой части, или механизм хранения не поддержка уникальных префиксных ключей.
но я понятия не имею, что это значит, у кого есть ключ?
Ответ 1
С частью
PRIMARY KEY (`movie_id`(3))
вы сообщаете mysql для создания ключа вспомогательной части * для первых трех букв идентификатора фильма. Это работает только для типов строк.
Вам нужно использовать
PRIMARY KEY (`movie_id`)
без предоставления длины.
* Является ли это уверенным, что запрос приводит к ошибке? Никогда не видел этого на первичном ключе, его использовали для индексов.
Discussion
This error message (shown in full below) arises from an ALTER TABLE operation where you try to define a key or index with a prefix improperly or it is not allowed by the storage engine.
Error: 1089 - Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys SQLSTATE: HY000 (ER_WRONG_SUB_KEY)
The first part of the error message (Incorrect sub part key) is the general message. It’s followed by three other reasons why the error might be caused:
- The used key part isn’t a string
- The used length is longer than the key part
- The storage engine doesn’t support unique sub keys
For the first reason above (the used key part isn’t a string), this is suggesting that may have tried to create an index prefix on a column that is not a string. For example, if the column on which an index prefix is based is a non-string (i.e., INT, TIMESTAMP, etc.), you will receive this error:
CREATE TABLE table (a INT); ALTER TABLE table1 ADD INDEX (col1(5)); ERROR 1089 (HY000): Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys
The second SQL statement is altering the table created in the first statement and is attempting to create an index prefix based on the first 5 bytes of column col1, but column col1 is an INT. This is not possible.
CREATE TABLE table1 (col1 TIMESTAMP); ALTER TABLE table1 ADD UNIQUE (col1(1)); ERROR HY000: Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys
Similarly in this example, the second SQL statement is attempting to create an index prefix on the first byte of column col1, but column col1 is a TIMESTAMP in this example. This is not possible either.
As for the second component of the error message for error 1089, the used length is longer than the key part, this arises when you try to create an index prefix that is longer than the actual column you are indexing. Below is an example of this:
CREATE TABLE table1 (col1 CHAR(10)); ALTER TABLE table1 ADD INDEX (col1(15)); ERROR 1089 (HY000): Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys
In this example, column col1 is of type CHAR (which is acceptable) and it’s 10 characters long. The ALTER TABLE statement, though, attempts to add an index prefix that requires the first 15 characters of col1. Since col1 is only 10 characters wide, the error is generated.
Finally, as for the third component of the error message, the storage engine doesn’t support unique sub keys, this suggests that you were trying to create an index prefix on a table in which the storage engine does not allow this. For example, prior to 4.0.14, there was restriction that prevented InnoDB tables from having indexes with column prefixes.
SELECT VERSION(); +---------------+ | version() | +---------------+ | 4.0.13-nt-log | +---------------+ 1 row in set (0.00 sec) CREATE TABLE table1 (col1 CHAR(10)) TYPE=InnoDB; ALTER TABLE table1 ADD PRIMARY KEY (col1(2)); ERROR 1089 (00000): Incorrect sub part key. The used key part isn't a string, the used length is longer than the key part or the table handler doesn't support unique sub keys
This error doesn’t occur with InnoDB as of version 4.0.14 of MySQL. Should another storage engine give you this error, then you cannot use index prefixes and you should either create the index on the entire column, or you could always change storage engines should you require the need for column index prefixes.
First, give the solution directly:
Click a_ I, do not enter the size, directly click to execute
Analysis
When you use phpMyAdmin to create a database table, we usually need to set a primary key and let it grow by itself. But sometimes when you finish setting, you may find such an error:
1089 - Incorrect prefix key; the used key part isn’t a string, the
used length is longer than the key part, or the storage engine doesn’t
support unique prefix keys
The picture is shown as follows:
Fault analysis:
References
Preview SQL statement:
We found that there is indeed an additional 4 in primary key, so how to solve the problem
do you really need the command line to handle it?Of course not
Solution:
When setting self growth, we will see this interface:
Tips:
it is worth noting that this size is not a required value, but it is not very familiar with phpMyAdmin. It is easy to subconsciously set a value for the first time. Once it is set, the above error will appear
so the final solution is the size in the diagram. Here, we just need to leave the blank and click execute to save the table successfully