mariadb – Make a field greater than the single limit

First of all, just a quick note. I've looked for an answer but I can only ask why rather than know how to overcome.

I am currently designing a set of database tables for a project I am working on. Some 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)
)
MOTOR = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

I would like to make sure the e-mail stays unique, but I can not use this unique constraint because of the length of the field and the fact that I'm using the utf8mb4 character set (this attempt generates a sch error such that "specified key" was too long, the maximum length of the key is 767 bytes ").

To overcome this, I seem to have the following choices:

  1. Change the character set to 1 which will store the data with a smaller footprint
  2. Reduce the length of the field so that it fits within the limits
  3. Split the email into 2 sections (user name, domain) and use them as unique values
  4. Check the uniqueness of the code using the database
  5. Create a hash of the email field and store it in another field. Use this for a single constraint
  6. Use a trigger before insert and before update to apply uniqueness

I'm not particularly interested in options 1 (or simply, in this case, I'm 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 I followed the rules of the RFC) because I do not think that the structure of the database should be compromised to satisfy a simple constraint.

4 s' press the code to make sure the database is compliant. I am not very enthusiastic about this as this can result in loss of integrity of stored data if someone forgets to check it.

5 would require unnecessary data to be stored in the database. This could cause collisions but, depending on the hash algorithm, this might not be a problem.

6 would be implemented something like:

DELIMITE $$

CREATE
DEFINING = & # 39; user & # 39; @ & # 39; localhost & # 39;
TRIGGER MemberBeforeInsertTrigger
BEFORE INSERTING
ON Members
FOR EACH ROW
TO START
DECLARE userCount int;

TO SELECT
COUNT (email) IN userCount
Members
O email = new.email;

- Make sure the email is not used.
IF userCount> 0 THEN
SQLSTATE SIGNAL> 45000 & # 39; SET MESSAGE_TEXT = & # 39; Email address already used & # 39 ;;
END IF;

END
$$

DELIMITER;

Would using a trigger like this have a negative effect on the performance of the database (compared to the options above) or am I just thinking too much about this?