是否可以使用正则表达式在MySQL中强制执行数据检查 [英] Is it Possible to Enforce Data Checking in MySQL using Regular expression
问题描述
假设我有一个称为电话号码的属性,并且我想对该字段的条目强制执行某些有效性.我可以为此使用正则表达式,因为正则表达式在定义约束时非常灵活.
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/zh-CN/regexp.html ),并且由于MySQL不支持CHECK约束(您始终可以选择使用PostgreSQL :)来进行数据验证.注意!请注意,即使MySQL确实具有CHECK约束构造,但不幸的是,MySQL(到目前为止5.6)并未针对检查约束验证数据.根据 http://dev.mysql.com/doc/refman /5.6/zh-CN/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中强制执行数据检查的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!