Leerzeichen, Nullwerte und ungültige Datumsangaben in MySQL verhindern: Was kann ich tun?

Question

Benutzertabelle

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

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

• Wie kann man dafür sorgen, dass keine Nullwerte akzeptiert werden? ("", " ", " ", " ", 等等...) -> 应该返回错误
• Wenn es bereits einen Wert gibt, wie entferne ich alle Leerzeichen darin? ("bf9 d 34 c9 08" = "bf9d34c908")

Namensspalte

• Wie kann man dafür sorgen, dass keine Nullwerte akzeptiert werden? ("", " ", " ", " ", 等等...) -> 应该返回错误

E-Mail-Leiste

• Wie kann man dafür sorgen, dass keine Nullwerte akzeptiert werden? ("", " ", " ", " ", 等等...) -> 应该返回错误
• Wenn es bereits einen Wert gibt, wie entferne ich alle Leerzeichen darin? ("nkr owks lpehqp jmgdb @ gm ail.com" = "[电子邮件受保护]")

Spalten CREATED_IN und SIGNED_IN

• Wie kann verhindert werden, dass ungültige Werte akzeptiert werden? Beim Einfügen eines ungültigen Werts wie eines Buchstabens, einer Zahl oder eines ungültigen Formats sollte anstelle der Eingabe von 0000-00-00 00:00:00 ein Fehler zurückgegeben werden.

MySQL-Version ist 5.7

Wie können diese Anforderungen im BENUTZER-Formular am besten gehandhabt werden?

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 后触发器不会执行以下代码。