当前位置：文江博客话题详情

MySQL：如何防止插入所有列= NULL的行？

发布于 2024-09-25 19:44:19 字数 156 浏览 1 评论 0原文

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

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

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

深居我梦 2024-10-02 19:44:19

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

回复收藏 0 原文

旧人哭 2024-10-02 19:44:19

由于 MySQL 不强制执行检查约束，因此您可能需要使用触发器来模拟检查约束。我建议查看这篇 MySQL Forge 文章：

触发器：模拟检查约束

这个想法是这样的将您的检查逻辑移至触发器。如果检查失败，则调用因引发唯一键违规而失败的存储过程。这允许我们向客户端返回一条描述性错误消息。

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

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 ;

双 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错误。

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

Triggers : Emulating Check Constraints

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 ;

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

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 ;

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.

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

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.

回复收藏 0 原文

揽清风入怀 2024-10-02 19:44:19

添加简单的检查约束

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

更新：

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

add simple check constraint

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

UPDATE:

Found that mysql does not support check constraint (parse, but ignore), one is possible workaround: using trigger (look to http://db4free.blogspot.com/2006/01/emulating-check-constraints.html)

回复收藏 0 原文

~没有更多了~