Can Data Validation Using Regular Expressions be Implemented in MySQL?

Can Data Validation Using Regular Expression Be Enforced in MySQL?

Validating data is crucial for ensuring data integrity. Regular expressions (regex) offer flexibility in defining constraints. Can MySQL leverage regex for data validation?

Yes, MySQL supports regex for data validation. Notably, it doesn't support CHECK constraints for data validation. Instead, triggers should be used.

For instance, you can create a check constraint for a phone column using a trigger:

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

This trigger checks if the incoming phone number matches the specified regular expression. If it doesn't match, it signals an error and sets a custom error message.

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, relying solely on MySQL for data validation is not recommended. Data should be validated at multiple levels of an application for optimal data integrity.

The above is the detailed content of Can Data Validation Using Regular Expressions be Implemented in MySQL?. For more information, please follow other related articles on the PHP Chinese website!