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.

解決方案

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!!!

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.

總結(jié)

以上是生活随笔為你收集整理的mysql触发器可以使用正则表达式,是否可以使用正则表达式在MySQL中强制执行数据检查...的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。