MySQL:如何防止插入所有列都为NULL的行？

Question

在我的MySQL表中，每一列本身都可以为NULL，但必须至少有一列具有非NULL值。目前，我正在存储过程中包装一条insert语句，以防止插入全部为空的行，但这当然不能阻止任何人使用原生INSERT语句，绕过我的包装器过程。

有没有一种“原生”的方式来定义带有这个约束的表？

Answer 1

由于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 

然后，可以按如下方式实现fail存储过程：

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

double INSERT将确保引发唯一键冲突。如果表中已经存在相同的消息，则会在第一个INSERT中引发冲突，但只要失败，就没有关系。

我们可以从命令行尝试fail存储过程：

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

好消息是我们得到了一个可读的错误消息。然而，我们没有得到正确的错误代码，我们也没有真正的“重复条目”。这显然是这种方法的一个局限性，特别是在使用错误处理的过程中更新或插入记录时，特别是在具体处理1062 Duplicate Entry错误时。

Answer 2

仅授予execute权限，以便用户或您的应用程序用户只能调用存储的过程！

Answer 3

添加简单的检查约束

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

更新：

发现mysql不支持检查约束(解析，但忽略)，一种可能的解决方法是:使用触发器(查看http://db4free.blogspot.com/2006/01/emulating-check-constraints.html)