是否可以使用正则表达式在 MySQL 中强制执行数据检查

2021-11-20 00:00:00 regex typechecking mysql triggers enforcement

假设我有一个名为电话号码的属性，并且我想对该字段的条目强制执行某些有效性.我可以为此目的使用正则表达式吗，因为正则表达式在定义约束方面非常灵活.

Suppose I have an attribute called phone number and I would like to enforce certain validity on the entries to this field. Can I use regular expression for this purpose, since Regular Expression is very flexible at defining constraints.

推荐答案

是的，你可以.MySQL 支持正则表达式 (http://dev.mysql.com/doc/refman/5.6/en/regexp.html) 并且对于数据验证，您应该使用触发器，因为 MySQL 不支持 CHECK 约束(您可以随时移动到 PostgreSQL 作为替代:).注意！请注意，尽管 MySQL 确实具有 CHECK 约束构造，但不幸的是 MySQL(到目前为止 5.6)不会根据检查约束验证数据.根据 http://dev.mysql.com/doc/refman/5.6/en/create-table.html:CHECK 子句被解析但被所有存储引擎忽略."

Yes, you can. MySQL supports regex (http://dev.mysql.com/doc/refman/5.6/en/regexp.html) and for data validation you should use a trigger since MySQL doesn't support CHECK constraint (you can always move to PostgreSQL as an alternative:). NB! Be aware that even though MySQL does have CHECK constraint construct, unfortunately MySQL (so far 5.6) does not validate data against check constraints. According to http://dev.mysql.com/doc/refman/5.6/en/create-table.html: "The CHECK clause is parsed but ignored by all storage engines."

您可以为列电话添加检查约束:

You can add a check constraint for a column phone:

CREATE TABLE data (
  phone varchar(100)
);

DELIMITER $$
CREATE TRIGGER trig_phone_check BEFORE INSERT ON data
FOR EACH ROW 
BEGIN 
IF (NEW.phone REGEXP '^(\\+?[0-9]{1,4}-)?[0-9]{3,10}$' ) = 0 THEN 
  SIGNAL SQLSTATE '12345'
     SET MESSAGE_TEXT = 'Wroooong!!!';
END IF; 
END$$
DELIMITER ;


INSERT INTO data VALUES ('+64-221221442'); -- should be OK
INSERT INTO data VALUES ('+64-22122 WRONG 1442'); -- will fail with the error: #1644 - Wroooong!!!

但是，您不应该仅仅依赖 MySQL(在您的情况下是数据层)进行数据验证.数据应在您应用的各个级别进行验证.

However you should not rely merely on MySQL (data layer in your case) for data validation. The data should be validated on all levels of your app.

相关文章