How can I add regular expressions to MySQL datafields?

Question

I have the following table:

Table Account{
    [...]
    email varchar(100),
    [...]
}

and a corresponding regular expression:

/^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$/i

How can I use the MySQL model, to link the regular expression to the the data-field "email", so that the regex is accessible to read out through php as well as a trigger or even better, in a kind of constraint?

I want to define this directly using MySQL workbench, so a separate table won't work for me.

Answer 1

In my opinion, input validation like this is not really the task of the database. Those validation rules may change. You may even make them configurable by the user some day. This becomes awfully difficult if the rules are buried in some trigger somewhere.

Also, you will need to handle a validation error on application level anyway - showing a message of some sort.

The best place to make them accessible to PHP inside mySQL may be a column comment, that you can fetch using SHOW COLUMNS in conjunction with the FULL keyword.