＃1071 - 指定密钥太长;最大密钥长度为767字节 [英] #1071 - Specified key was too long; max key length is 767 bytes

问题描述

我在这个SQL查询中创建一个表：

I this SQL query to create a table:

CREATE TABLE IF NOT EXISTS `local_sysDB`.`hashtags` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `hashtag` VARCHAR(255) NOT NULL COMMENT 'hashtag must be unique. Must be saved without #',
  `accountId` INT NULL,
  `startTracking` DATETIME NOT NULL COMMENT 'When tracking of the hashtag start',
  `endTracking` DATETIME NOT NULL COMMENT 'When tracking of the hashtag ends',
  `channelInstagram` TINYINT(1) NOT NULL DEFAULT 1,
  `channelTwitter` TINYINT(1) NOT NULL DEFAULT 1,
  `channelYoutube` TINYINT(1) NOT NULL DEFAULT 1,
  `postLimit` INT NOT NULL,
  `suspendOnLimit` TINYINT(1) NOT NULL DEFAULT 1,
  `created` TIMESTAMP NOT NULL DEFAULT NOW(),
  `updated` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  `approveBeforeView` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'If account should approve posts before being displayed public',
  `suspended` TINYINT(1) NOT NULL DEFAULT 0,
  `InstagramSubscriptionId` INT(10) UNSIGNED NULL,
  `deleted` TINYINT(1) NULL DEFAULT 0 COMMENT 'if hashtag is marked for deletion',
  `collectedPosts` BIGINT(50) UNSIGNED NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `hashtags_hashtag` (`hashtag` ASC)  KEY_BLOCK_SIZE=255,
  INDEX `hashtags_accounts_accountId` (`accountId` ASC),
  INDEX `hashtag_trackingDate` (`startTracking` ASC, `endTracking` ASC),
  INDEX `hashtag_collectedPosts` (`collectedPosts` ASC),
  INDEX `hashtag_updated` (`updated` ASC),
  FULLTEXT INDEX `hashtag_search` (`hashtag` ASC),
  CONSTRAINT `hashtags_accounts_accountId`
    FOREIGN KEY (`accountId`)
    REFERENCES `local_sysDB`.`accounts` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
ROW_FORMAT = COMPRESSED
KEY_BLOCK_SIZE = 16;

当我尝试运行时，我收到以下错误：

When I try to run this, I get the following error:

SQL查询：

SQL-query:

CREATE TABLE IF NOT EXISTS `local_sysDB`.`hashtags` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `hashtag` VARCHAR(255) NOT NULL COMMENT 'hashtag must be unique. Must be saved without #',
  `accountId` INT NULL,
  `startTracking` DATETIME NOT NULL COMMENT 'When tracking of the hashtag start',
  `endTracking` DATETIME NOT NULL COMMENT 'When tracking of the hashtag ends',
  `channelInstagram` TINYINT(1) NOT NULL DEFAULT 1,
  `channelTwitter` TINYINT(1) NOT NULL DEFAULT 1,
  `channelYoutube` TINYINT(1) NOT NULL DEFAULT 1,
  `postLimit` INT NOT NULL,
  `suspendOnLimit` TINYINT(1) NOT NULL DEFAULT 1,
  `created` TIMESTAMP NOT NULL DEFAULT NOW(),
  `updated` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  `approveBeforeView` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'If account should approve posts before being displayed public',
  `suspended` TINYINT(1) NOT NULL DEFAULT 0,
  `InstagramSubscriptionId` INT(10) UNSIGNED NULL,
  `deleted` TINYINT(1) NULL DEFAULT 0 COMMENT 'if hashtag is [...]

MySQL meldt：Documentatie

MySQL meldt: Documentatie

＃1071 - 指定密钥太长;最大密钥长度是767字节

#1071 - Specified key was too long; max key length is 767 bytes

我已经发现它与 this ：

767字节是InnoDB表的规定前缀限制 - MyISAM表的
1,000字节长。

767 bytes is the stated prefix limitation for InnoDB tables - its 1,000 bytes long for MyISAM tables.

根据对此问题的回答，您可以通过指定列的子集而不是整个数量来获取应用
的密钥。
IE：

According to the response to this issue, you can get the key to apply by specifying a subset of the column rather than the entire amount. IE:

ALTER TABLE mytable ADD UNIQUE（column1（15），column2（200）） ;因为你需要获得密钥才能调整
，但是我想知道是否值得
来审查你关于这个实体的数据模型，看看是否有
的改进可以让你在没有达到MySQL限制的情况下实现预期的业务
规则。

ALTER TABLE mytable ADD UNIQUE ( column1(15), column2(200) ); Tweak as you need to get the key to apply, but I wonder if it would be worth it to review your data model regarding this entity to see if there's improvements that would allow you to implement the intended business rules without hitting the MySQL limitation.

我尝试在索引中添加一个长度，但MySQL Workbench保持将它们重置为0.
我想知道是否有其他原因可以解决这个问题，或者是另一种方法来解决这个问题。

I tried adding a length to my indexes, but MySQL Workbench keeps resetting them to 0. I'd like to know if there could be another cause of this problem, or another way to solve this problem.

推荐答案

我刚刚学会了解决方法...获取5.5.14或5.6.3（或更高版本），执行此处指示的SET，并使用DYNAMIC或COMPRESSED：

I just learned a workaround... Get 5.5.14 or 5.6.3 (or later), do the SETs indicated here, and use DYNAMIC or COMPRESSED:

SET GLOBAL innodb_file_per_table = ON,
           innodb_file_format = Barracuda,
           innodb_large_prefix = ON;
CREATE TABLE so29676724 (
  `id` INT NOT NULL AUTO_INCREMENT,
  `hashtag` VARCHAR(255) NOT NULL COMMENT 'hashtag must be unique. Must be saved without #',
   PRIMARY KEY (`id`),
  UNIQUE INDEX `hashtags_hashtag` (`hashtag` ASC)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET  utf8mb4
ROW_FORMAT = COMPRESSED;

SHOW CREATE TABLE so29676724\G

mysql> CREATE TABLE so29676724 (
    ->   `id` INT NOT NULL AUTO_INCREMENT,
    ->   `hashtag` VARCHAR(255) NOT NULL COMMENT 'hashtag must be unique. Must be saved without #',
    ->    PRIMARY KEY (`id`),
    ->   UNIQUE INDEX `hashtags_hashtag` (`hashtag` ASC)
    -> )
    -> ENGINE = InnoDB
    -> DEFAULT CHARACTER SET  utf8mb4
    -> ROW_FORMAT = COMPRESSED;
Query OK, 0 rows affected (0.09 sec)

这篇关于＃1071 - 指定密钥太长;最大密钥长度为767字节的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！