0

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:

  1. Modify the character set to 1 that will store the data in with a smaller footprint
  2. Reduce the length of the field so it fits within the limit
  3. Split the email into 2 sections (username, domain) and use these as unique values
  4. Check for uniqueness in the code utilizing the database
  5. Create a hash of the email field and store this in another field. Use this for unique constraint
  6. 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 > 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 thi?s

  • Only use triggers when there is no MySQL equivalent. As you are going to be searching by email, have it there as an index, at least as a partial key. – danblack Mar 31 '19 at 23:19
  • 1
    Option 7 - use an RDBMS thar supports your Business Requirements. I doubt this is a valid option which is why I'm posting it as a comment. – Michael Kutz Apr 1 '19 at 0:22
  • 1
    5 workarounds for the 767 problem: mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes – Rick James Apr 18 '19 at 17:26
2
  1. A realistic email limit could have a length of 64
  2. email addresses are never utf8, only ANSI
  3. innodb_large_prefix can give you a bigger index.
  4. 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

  • Hi @danblack agree with point 1, was hoping for rfc compliance but maybe trying to do something that is not worth chasing. Re 2, from my understanding this is not quite correct. An IDNA domain can contain unicode characters. Although stored in DNS as ascii (converted to punycode) it's normal for a user to enter the email in that format. Of course this could be overcome by making the conversion prior to storage. Such domains although would appear to be short will be size-ably bigger than they appear. I was unaware of the innodb_large_prefix, I will take a look. – Peter Mar 31 '19 at 23:29
  • The 254 maximum of the IDNA encoding version too but I understand the overhead/complexity of conversions. Glad innodb_large_prefix was of use. – danblack Mar 31 '19 at 23:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.