防止 MySQL 中出現空格、空值和無效日期：我可以採取哪些措施？

Question

用戶表

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

觸發器

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欄位

• #如何使其不接受空值？ ("", " ", " ", " ", 等等...) -> 應該回傳錯誤
• #如果已經有一個值，如何刪除其中的所有空格？ ("bf9 d 34 c9 08" = "bf9d34c908")

名稱列

• 如何使其不接受空值？ ("", " ", " ", " ", 等等...) -> 應該回傳錯誤

#電子郵件列

• 如何使其不接受空值？ ("", " ", " ", " ", 等等...) -> 應該回傳錯誤
• #如果已經有一個值，如何刪除其中的所有空格？ ("nkr owks lpehqp jmgdb @ gm ail.com" = "[電子郵件受保護]")

#CREATED_IN 和 SIGNED_IN 欄位

#如何使其不接受無效值？當插入無效值（如字母、數字或無效格式）時，應傳回錯誤，而不是輸入 0000-00-00 00:00:00。

MySQL版本是5.7

在 USERS 表上處理這些要求的最佳方法是什麼？ ###

Answer 1

觸發器可以如下所示：

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

注意 - 此觸發器允許任何值為 NULL（但只有電子郵件可以為 NULL，所有其他欄位都定義為 NOT NULL）。

你可以重新排列這些區塊，並將機率最大的條件放在第一位。執行 SIGNAL 後觸發器不會執行下列程式碼。