MySQL alternative to PostgreSQL's custom data type (and domain)

Question

In PostgreSQL, if I store a specific datatype (ex. an mail address) in multiple columns across multiple tables, I could simply define a custom mail type with all constraints like so:

CREATE TYPE status_enum AS enum ('accepted', 'declined', 'awaiting');
CREATE DOMAIN mail_address
    AS varchar(100) CHECK(value ~ '[A-Za-z0-9.]+@[A-Za-z0-9]+[.][A-Za-z]+');

and then use it like so:

CREATE TABLE user (
    ...
    personal_email    mail_address    NOT NULL UNIQUE,
    work_email        mail_address    NOT NULL UNIQUE,
    status            status_enum     NOT NULL,
    ...
);

CREATE TABLE job (
    ...
    status            status_enum     NOT NULL,
    client_email      mail_address    NOT NULL UNIQUE,
    ...
);

I found out that MySQL is not object-relational database and therefore doesn't have custom datatype.

My question is:

How do people handle in MySQL reoccurring datatype in multiple column, in order to prevent copy-pasting identical constraints?

Answer 1

For the first, you can implement using a reference table:

create table statuses (
     status int primary key,
     status_name varchar(255)
);

The relationships would then be handled using foreign key constraints. This is not "arcane" by the way, but how the relationship would be handled in most databases.

The second would be handled by a user-defined function. Most databases support this in a check constraint. Unfortunately, MySQL requires that you use triggers for the data validation -- but they can all use the same underlying function.

Alternatively, create a table of emails and validate the emails only in that table. All other emails would use email_id rather than the email itself. You might find this forward looking. I have found that storing such PII in a separate table is helpful for privacy reasons as well.