我在这个SQL查询中创建一个表:
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;
当我尝试运行它时,我收到以下错误:
SQL查询:
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#1071 - 指定密钥太长;最大密钥长度为767字节
我已经发现它与this有关:
767字节是InnoDB表的规定前缀限制--MyISAM表的长度为1,000字节 . 根据对此问题的响应,您可以通过指定列的子集而不是整个数量来获取应用密钥 . IE:ALTER TABLE mytable ADD UNIQUE(column1(15),column2(200));调整需要获取密钥以进行调整,但我想知道是否值得查看有关此实体的数据模型,看看是否有改进可以让您实现预期的业务规则而不会遇到MySQL限制 .
我尝试在索引中添加一个长度,但是MySQL Workbench一直将它们重置为0.我想知道是否可能有另一个原因造成这个问题,或者另一种方法来解决这个问题 .
2 回答
我刚刚学会了一个解决方法......获取5.5.14或5.6.3(或更高版本),执行此处指示的SET,并使用DYNAMIC或COMPRESSED:
“哈希”通常是十六进制,而不是UTF-8 . 哈希通常比255短得多 .
如果适用,那么......
将是一个解决方案,适用于没有任何innodb设置指示的任何版本 .
(注意:191是
VARCHAR
与utf8mb4
的截止值,但很少有标准哈希需要这么多 . )