Mysql列约束为“不为空"；/“必需" [英] Mysql Column constraint as "not empty" / "required"

问题描述

我们可以在mysql中将列指定为非空"/必需"吗?要求是确保该字段在任何记录插入时都永远不会为空.

Can we specify a column in mysql as "not empty" / "required". The requirement is to ensure that the field never remains blank on any record insertion.

推荐答案

我假设您也不希望表中允许使用空白(空字符串，而不是NULL)值.

I assume you don't want blank (empty string, as opposed to NULL) values to be allowed in the table either.

通常，这就是CHECK约束的作用.你做类似的事情

Normally, that's what a CHECK constraint for. You do something like

CREATE TABLE
        mytable
        (
        myfield NOT NULL VARCHAR(200),
        CHECK(myfield > '')
        )

但是，MySQL解析约束但不强制执行.您仍然可以插入空值.

However, MySQL parses the constraint but does not enforce it. You are still allowed to insert empty values.

要解决此问题，请创建一个BEFORE INSERT触发器，并在尝试插入空白值时发出信号:

To work around that, create a BEFORE INSERT trigger and raise a signal on an attempt to insert a blank value:

CREATE TRIGGER
        tr_mytable_bi
BEFORE INSERT
ON      mytable
FOR EACH ROW
BEGIN
        IF NEW.myfield = '' THEN
                SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'Blank value on mytable.myfield';
        END IF;
END;

如果您也要禁止更新为空白值，请在BEFORE UPDATE上执行相同的操作.

Do the same on BEFORE UPDATE if you want to forbid updates to a blank value as well.

这篇关于Mysql列约束为“不为空"；/“必需"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！