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

问题描述

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

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.

这篇关于是否可以使用正则表达式在 MySQL 中强制执行数据检查的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！