How can I prevent white spaces, empty values, and invalid dates in MySQL?-CodePudding

USERS table

CREATE TABLE `USERS` (
 `ID` char(255) COLLATE utf8_unicode_ci NOT NULL,
 `NAME` char(255) COLLATE utf8_unicode_ci NOT NULL,
 `EMAIL` char(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `CREATED_IN` datetime NOT NULL,
 `SIGNED_IN` datetime NOT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The trigger

CREATE TRIGGER `ABC` BEFORE INSERT ON `USERS` FOR EACH ROW 
IF NEW.ID = "" OR NEW.NAME = "" OR 
NEW.CREATED_IN = "" OR NEW.CREATED_IN = "0000-00-00 00:00:00" OR 
NEW.SIGNED_IN = "" OR NEW.SIGNED_IN = "0000-00-00 00:00:00" 
THEN SIGNAL SQLSTATE "45000"; END IF

ID column

How can I make it doesn't accept empty values? ("", " ", " ", " ", etc...) -> Should return an error
If there was a value already, How can I remove all spaces from it? ("bf9 d 34 c9 08" = "bf9d34c908")

NAME column

How can I make it doesn't accept empty values? ("", " ", " ", " ", etc...) -> Should return an error

EMAIL column

How can I make it doesn't accept empty values? ("", " ", " ", " ", etc...) -> Should return an error
If there was a value already, How can I remove all spaces from it? ("nkr owks lpehqp jmgdb @ gm ail. com" = "[email protected]")

CREATED_IN and SIGNED_IN columns

How can I make it doesn't accept invalid values? When inserting invalid values like letters, numbers, or invalid format then should return an error instead of putting 0000-00-00 00:00:00.

MySQL version is 5.7

What is the best way to handle these requirements on the USERS table?

CodePudding user response：

The trigger can look like this:

CREATE TRIGGER process_input
BEFORE INSERT ON USERS
FOR EACH ROW
BEGIN
    SET NEW.id = REPLACE(NEW.id, ' ', '');
    IF NEW.id = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '`id` cannot be empty.';
    END IF;

    SET NEW.name = TRIM(NEW.name);
    IF NEW.name = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '`name` cannot be empty.';
    END IF;

    SET NEW.email = REPLACE(NEW.email, ' ', '');
    IF NEW.email = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '`email` cannot be empty.';
    END IF;

    IF STR_TO_DATE(created_in, '%Y-%m-%d %H:%i:%s') IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Incorrect `created_in` datetime value.';
    END IF;

    IF STR_TO_DATE(signed_in, '%Y-%m-%d %H:%i:%s') IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Incorrect `signed_in` datetime value.';
    END IF;
END

Pay attention - this trigger allows NULL in any value (but only email can be NULL, all another columns are defined as NOT NULL).

You may resort the blocks and put the conditions with the most probability first. The trigger does not execute the below code after SIGNAL is executed.