MySQL：使用Date检查约束 [英] MySQL: Check constraint with Date

问题描述

我正在使用 MySQL ，这里是一个简单的查询集：

I am using MySQL and here is a simple query set:

create table dcheck (
    fdate date,
    sdate date,
    check (fdate <= sdate)
);

insert into dcheck values ('2006-12-12','2003-12-12');
insert into dcheck values ('2003-12-12', '2006-12-12');

这里我期待第一个insert语句失败。但是令人惊讶的是，这两个查询都通过，表中有两行。

Here I expect the first insert statement to fail. But surprisingly, both the queries passes and two rows are there in the table.

任何人都可以解释为什么？

Can anyone please explain why?

谢谢

推荐答案

MySQL不实现CHECK约束。从最新（5.6）精细手册：

MySQL doesn't implement CHECK constraints. From the latest (5.6) fine manual:

CHECK 子句被解析，但被所有存储引擎忽略。

The CHECK clause is parsed but ignored by all storage engines.

所以语法被解析以兼容其他其他SQL，但检查没有实现。

So the syntax is parsed for compatibility other other SQLs but the checking is not implemented.

你可以伪造您的CHECK约束与BEFORE INSERT和BEFORE UPDATE触发抛出异常，如果所需条件没有遇到。

You could fake your CHECK constraint with BEFORE INSERT and BEFORE UPDATE triggers that threw an exception if the desired condition was not met.

这篇关于MySQL：使用Date检查约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！