MySQL唯一1500 varchar字段错误(#1071-指定的密钥太长) [英] MySQL unique 1500 varchar field error (#1071 - Specified key was too long)

问题描述

我有一个字段(链接)，该字段是varchar(1500)，并且我想使其唯一.我将更改应用于mysql配置，并将长度增加到3072字节

I have a field (link) that is varchar (1500) and that I want to make unique. I applied changes to mysql configuaration and increased length to 3072 bytes

ROW_FORMAT=DYNAMIC, innodb_file_format = Barracuda, innodb_large_prefix = true

但是当我对自己的字段应用唯一时，会遇到下一个错误:

But when I apply unique to my field, I got next error:

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

我的字段是varchar(1500)，即3000个字节.

My field is varchar(1500) that is 3000 bytes.

怎么了?

更新(1) 表格数据:

Update (1) Table data:

CREATE TABLE IF NOT EXISTS `pages` (
  `link` varchar(1500) NOT NULL,
  `domain` varchar(255) NOT NULL,
  `lastvisited` datetime DEFAULT NULL,
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `link` (`link`(255))
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ROW_FORMAT=DYNAMIC;

更新(2) 更改命令(通过PHPMYADMIN完成)

Update (2) Alter command (done via PHPMYADMIN)

ALTER TABLE  `pages` ADD UNIQUE (
`link`
)

推荐答案

由于您将URL存储在link列中，因此实际上不需要使用UTF8，因为URL只能包含ASCII字符.为link列指定纯ASCII字符编码，甚至可以将其最大长度增加到3072个字符.

Since you will be storing URLs in the link column, you don't actually need to use UTF8 for it, because URLs can contain only ASCII characters. Specifying a plain ASCII character encoding for your link column will even allow you to raise its max length to 3072 characters.

CREATE TABLE IF NOT EXISTS `pages` (
  `link` varchar(1500) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `domain` varchar(255) NOT NULL,
  `lastvisited` datetime DEFAULT NULL,
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `link` (`link`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ROW_FORMAT=DYNAMIC;

(根据@eggyal对ascii_bin归类的建议进行了更新)

(Updated as per @eggyal's suggestion for the ascii_bin collation)

这篇关于MySQL唯一1500 varchar字段错误(#1071-指定的密钥太长)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！