Storing email VARCHAR(320) as UNIQUE, #1071 - Specified key was too long; max key length is 767

Question

I have already checked what is the reason of this error. Therefore, I know I am exceeding the limit (767 byte) by trying to set email VARCHAR(320) as a UNIQUE key (320 * 3 = 960 byte).

However, I am using MySQL as a database and I need to use the email value as a unique key in my application. Could you please tell me, what should I change to overcome this problem?

You cannot. You'll have to maintain an additional column with a hash to put the index on. — Álvaro González, Mar 31 '16 at 15:55
@ÁlvaroGonzález Could you please write your solution in a table format? — Kaan Burak Sener, Mar 31 '16 at 15:57
Isn't the maximum length 254? See https://en.wikipedia.org/wiki/Email_address & http://stackoverflow.com/questions/386294/what-is-the-maximum-length-of-a-valid-email-address — dnoeth, Mar 31 '16 at 16:02
I used this answer: http://dba.stackexchange.com/questions/37014/in-what-data-type-should-i-store-an-email-address-in-database @dnoeth — Kaan Burak Sener, Mar 31 '16 at 16:04

score 1 · Accepted Answer · edited May 23 '17 at 10:28

1

Create a unique index on the first 254 characters or so:

create unique index idx_t_email on t(email(254));

Emails should be shorter than 254 characters.

According to this answer, the longest possible email is 254 characters anyway, so this should be fine.

edited May 23 '17 at 10:28

Community

1
1

answered Mar 31 '16 at 16:12

Gordon Linoff

1,242,037
58
646
786

Why not use a trigger to calculate a hash of the mail column, then save the hash as unique field? `sha1` is 20 bytes long so a `binary(20)` can be used to hold it. It spends less space and it can be automated as well.. – N.B. Mar 31 '16 at 16:18

score 1 · Answer 2 · answered Mar 31 '16 at 16:13

Assuming you are using MySQL 5.5.14 or newer, you can solve this problem by:

Enabling the innodb_large_prefix server setting
Enabling innodb_file_format = BARRACUDA
Using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED

This will allow you to include columns up to 3072 bytes long in InnoDB indexes, so your 320 character column could be included in a unique index.

Read my blog post for more details:

http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/

Storing email VARCHAR(320) as UNIQUE, #1071 - Specified key was too long; max key length is 767

2 Answers2

Linked