代码之家 › 专栏 › 技术社区 › ssc

MySQL:如何防止所有列都为空的行插入?

definition null mysql

1

ssc · 技术社区 · 14 年前

在mysql表中,每个列本身都可以为空,但必须至少有一个列具有非空值。目前,我正在将insert语句包装在存储过程中,该存储过程阻止插入所有空行,但这当然不会阻止任何人使用本机insert语句,从而绕过包装过程。

是否有一种“本机”方法来定义带有该约束的表?

3 回复 | 直到 7 年前

1

2

Daniel Vassallo 14 年前

由于MySQL不强制检查约束,您可能希望使用触发器来模拟检查约束。我建议查看mysql forge文章:

Triggers : Emulating Check Constraints

其目的是将检查逻辑移动到触发器。如果检查失败,则通过引发唯一键冲突来调用失败的存储过程。这允许我们将描述性错误消息返回给客户机。

您的触发器可能如下所示:

DELIMITER $$
CREATE TRIGGER check_not_all_null BEFORE INSERT ON your_table FOR EACH ROW
BEGIN
   IF COALESCE(field_1, field_2, field_3) IS NOT NULL THEN
      CALL fail('All fields cannot be null');
   END IF;
END $$
DELIMITER ;

我们需要 fail 存储过程引发唯一的密钥冲突,以便 INSERT 检查失败时中止。上面提到的文章建议创建一个内存表,定义如下:

CREATE TABLE `Error` (                                                               
   `ErrorGID` int(10) unsigned NOT NULL auto_increment,                               
   `Message`  varchar(128) default NULL,                                
   `Created`  timestamp NOT NULL default CURRENT_TIMESTAMP
              ON UPDATE CURRENT_TIMESTAMP,           
    PRIMARY KEY (`ErrorGID`),                                                   
    UNIQUE KEY `MessageIndex` (`Message`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED

然后 失败 存储过程可以实现如下:

DELIMITER $$
CREATE PROCEDURE `fail`(_message VARCHAR(128))
BEGIN
  INSERT INTO error (message) VALUES (_message);
  INSERT INTO error (message) VALUES (_message);
END$$
DELIMITER ;

双 插入 将确保引发唯一键冲突。如果表中已存在相同的消息,将在第一个消息上引发冲突 插入 但只要失败就没关系。

我们可以试试 失败 命令行中的存储过程:

mysql> CALL fail('All fields cannot be null');
ERROR 1062 (23000): Duplicate entry 'All fields cannot be null' for key 2

好消息是我们得到了一个可读的错误消息。但是,我们无法返回正确的错误代码,并且我们实际上没有“重复条目”。这显然是此方法的一个限制,尤其是在使用错误处理的过程中更新或插入记录时,尤其是在处理 1062 Duplicate Entry 具体错误。

2

3

Jon Black 14 年前

只授予执行权限,以便用户或应用程序用户只能调用存储的进程!

3

2

Michael Pakhantsov 14 年前

添加简单检查约束

CHECK(COALESCE(Field1, Field2, ... FieldN) IS NOT NULL)

更新:

发现mysql不支持check约束(parse,but ignore),一个可能的解决方法是:使用trigger(look to http://db4free.blogspot.com/2006/01/emulating-check-constraints.html )