MySQL:如何防止所有列= NULL的行插入? [英] MySQL: how to prevent insertion of a row with all columns = NULL?

问题描述

在我的MySQL表中，每一列本身可以为NULL，但是必须至少有一个具有非NULL值的列.目前，我正在将一条插入语句包装在一个存储过程中，以防止插入全空行，但是这当然不会阻止任何人使用本机INSERT语句，从而绕过了我的包装程序.

In my MySQL table, every column by itself can be NULL, but there must be at least one column with a non-NULL value. At the moment, I am wrapping an insert statement in a stored procedure which prevents insertion of all-NULL rows, but that of course does not keep anyone from using native INSERT statements, circumventing my wrapper procedure.

是否有一种本机"方式来定义具有该约束的表?

Is there a 'native' way to define the table with that constraint ?

推荐答案

由于MySQL不强制执行检查约束，因此您可能希望使用触发器来模拟一个约束.我建议查看这篇MySQL Forge文章:

Since MySQL doesn't enforce check constraints, you may want to emulate one with a trigger. I suggest checking out this MySQL Forge article:

• 触发器:模拟检查约束

这样做的目的是将检查逻辑移至触发器.如果检查失败，则通过引发唯一键冲突来调用失败的存储过程.这使我们能够将描述性错误消息返回给客户端.

The idea is this to move your check logic to a trigger. If the check fails, call a stored procedure that fails by raising a unique key violation. This allows us to return a descriptive error message back to the client.

您的触发器可能看起来像这样:

Your trigger will probably look something like this:

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在检查失败时中止.上面提到的文章建议创建一个定义如下的内存表:

We need to make the fail sproc raise a unique key violation in order to have the INSERT aborted when the check fails. The above mentioned article suggests creating a memory table defined as follows:

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 sproc:

Then the fail sproc could be implemented as follows:

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

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

The double INSERT will ensure that the unique key violation is raised. If the same message already exists in the table, the violation will get raised on the first INSERT, but it doesn't matter as long as it fails.

我们可以从命令行尝试fail sproc:

We can try the fail sproc from the command line:

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

好消息是我们返回了可读的错误消息.但是，我们没有获得正确的错误代码，并且我们实际上没有重复的条目".这显然是该方法的局限性，尤其是在使用错误处理的过程中更新或插入记录时，尤其是专门处理1062 Duplicate Entry错误.

The good news is that we get back a readable error message. However we don't get back the correct error code, and we don't really have a "duplicate entry". This is obviously one limitation of this method, especially when updating or inserting records in a procedure which uses error handling, in particular handling the 1062 Duplicate Entry error specifically.

这篇关于MySQL:如何防止所有列= NULL的行插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！