Making a field greater than the limit unique Firstly, just a quick note. I have searched for an answer but I can only people asking why rather than how to overcome.

I am currently designing a set of database tables for a project I am working on. A couple of the tables such as the following (simplified table):

CREATE TABLE members (
  id int(11) NOT NULL AUTO_INCREMENT
  email varchar(255) NOT NULL
  PRIMARY KEY (id)
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;


I would like to ensure that the email remains unique however I am unable to use the unique constraint due to the length of the field and the fact I am using the utf8mb4 character set (trying to do so results in an error sch as "Specified key was too long; max key length is 767 bytes").

To overcome this I appear to have the following choices:


Modify the character set to 1 that will store the data in with a smaller footprint
Reduce the length of the field so it fits within the limit
Split the email into 2 sections (username, domain) and use these as unique values
Check for uniqueness in the code utilizing the database
Create a hash of the email field and store this in another field. Use this for unique constraint
Use a before insert and before update trigger to enforce the uniqueness


I am not overly keen on options 1 (or am I simply, in this case, using the wrong character set?), 2 (even this field is actually too short for a valid email address according to the RFC) and 3 (the domain would still be too long if following the rules of the RFC's) as I do not believe the structure of the database should be compromised to satisfy a simple constraint.

4 relies on the code to ensure the database is conforming. I am not overly keen on this as it can cause the data stored to lose integrity is someone forgets to check.

5 would require extraneous data to be stored in the database. This could cause collisions but dependent upon the hashing algorithm it may not be a problem.

6 would be implemented something like:

DELIMITER $$

CREATE
DEFINER = 'user'@'localhost'
TRIGGER MemberBeforeInsertTrigger
BEFORE INSERT
ON members
FOR EACH ROW
BEGIN
  DECLARE userCount int;

  SELECT
    COUNT(email) INTO userCount
  FROM members
  WHERE email = new.email;

  -- Ensure Email is not in use.
  IF userCount &gt; 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email address already in use';
  END IF;

END
$$

DELIMITER ;


Would using a trigger such as this cause any negative affects in the performance of the database (compared with above options) or am I simply overthinking this?
A realistic email limit could have a length of 64
email addresses are never utf8, only ANSI
innodb_large_prefix can give you a bigger index.
generated column
with a HASH as a unique index is just as good, like:

ALTER TABLE members ADD email_hash VARBINARY(56) AS (SHA2(email, 224)) UNIQUE KEY