Specified key was too long; max key length is 767 bytes

Question

I understand the InnoDB index max length is 767 bytes.

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(254) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  .....
  `token` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `rank` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_token_index` (`token`),
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I want to create a index on my email.

alter table agent add UNIQUE index idx_on_email (email);

But got the error message:

Specified key was too long; max key length is 767 bytes.

But the length of token column only 128 bytes, email is 254 bytes, not above 767 bytes. Hope anyone can help me! Thanks in advance!

Answer 1

varchar(254) when you use utf8mb4, means 254 character and each character has 4 bytes, the email field requires at least 1016 bytes (254 * 4). you may look at this article: http://wildlyinaccurate.com/mysql-specified-key-was-too-long-max-key-length-is-767-bytes/ so you can make your email column: varchar(100)