MySQL 中的 CHECK 约束不起作用

CHECK constraint in MySQL is not working

提问by JohnRaja

First I created a table like

首先我创建了一个表

CREATE TABLE Customer (
  SD integer CHECK (SD > 0),
  Last_Name varchar (30),
  First_Name varchar(30)
);

and then inserted values in that table

然后在该表中插入值

INSERT INTO Customer values ('-2','abc','zz');

MySQL doesn't show an error, it accepted the values.

MySQL 没有显示错误，它接受了这些值。

回答by David Kerins

MySQL 8.0.16is the first version that supports CHECK constraints.

MySQL 8.0.16是第一个支持 CHECK 约束的版本。

Read https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html

阅读https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html

If you use MySQL 8.0.15 or earlier, the MySQL Reference Manualsays:

如果您使用 MySQL 8.0.15 或更早版本，MySQL 参考手册说：

The CHECKclause is parsed but ignored by all storage engines.

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

Try a trigger...

试试触发器...

mysql> delimiter //
mysql> CREATE TRIGGER trig_sd_check BEFORE INSERT ON Customer 
    -> FOR EACH ROW 
    -> BEGIN 
    -> IF NEW.SD<0 THEN 
    -> SET NEW.SD=0; 
    -> END IF; 
    -> END
    -> //
mysql> delimiter ;

Hope that helps.

希望有帮助。

回答by Michel Feldheim

Unfortunately MySQL does not support SQL check constraints. You can define them in your DDL query for compatibility reasons but they are just ignored.

不幸的是，MySQL 不支持 SQL 检查约束。出于兼容性原因，您可以在 DDL 查询中定义它们，但它们会被忽略。

There is a simple alternative

有一个简单的选择

You can create BEFORE INSERTand BEFORE UPDATEtriggers which either cause an error or set the field to its default value when the requirements of the data are not met.

您可以创建BEFORE INSERT和BEFORE UPDATE触发，在不满足数据要求时导致错误或将字段设置为其默认值。

Example for BEFORE INSERTworking after MySQL 5.5

BEFORE INSERT在 MySQL 5.5 之后工作的示例

DELIMITER $$
CREATE TRIGGER `test_before_insert` BEFORE INSERT ON `Test`
FOR EACH ROW
BEGIN
    IF CHAR_LENGTH( NEW.ID ) < 4 THEN
        SIGNAL SQLSTATE '12345'
            SET MESSAGE_TEXT := 'check constraint on Test.ID failed';
    END IF;
END$$   
DELIMITER ;  

Prior to MySQL 5.5 you had to cause an error, e.g. call a undefined procedure.

在 MySQL 5.5 之前，您必须导致错误，例如调用未定义的过程。

In both cases this causes an implicit transaction rollback. MySQL does not allow the ROLLBACK statement itself within procedures and triggers.

在这两种情况下，这都会导致隐式事务回滚。MySQL 不允许在过程和触发器中使用 ROLLBACK 语句本身。

If you don't want to rollback the transaction ( INSERT / UPDATE should pass even with a failed "check constraint" you can overwrite the value using SET NEW.ID = NULLwhich will set the id to the fields default value, doesn't really make sense for an id tho

如果您不想回滚事务（即使“检查约束”失败，INSERT / UPDATE 也应该通过，您可以使用SET NEW.ID = NULL它将 id 设置为字段默认值的值覆盖该值，对于 id 没有意义寿

Edit:Removed the stray quote.

编辑：删除了杂散的报价。

Concerning the :=operator:

关于:=运营商：

Unlike =, the :=operator is never interpreted as a comparison operator. This means you can use :=in any valid SQL statement (not just in SET statements) to assign a value to a variable.

与 不同=，:=运算符永远不会被解释为比较运算符。这意味着您可以:=在任何有效的 SQL 语句中使用（不仅仅是在 SET 语句中）来为变量赋值。

https://dev.mysql.com/doc/refman/5.6/en/assignment-operators.html

https://dev.mysql.com/doc/refman/5.6/en/assignment-operators.html

Concerning backtick identifier quotes:

关于反引号标识符引号：

The identifier quote character is the backtick (“`”)

If the ANSI_QUOTES SQL mode is enabled, it is also permissible to quote identifiers within double quotation marks

标识符引号字符是反引号（“`”）

如果启用了 ANSI_QUOTES SQL 模式，也允许在双引号内引用标识符

http://dev.mysql.com/doc/refman/5.6/en/identifiers.html

http://dev.mysql.com/doc/refman/5.6/en/identifiers.html

回答by ypercube??

CHECKconstraints are ignored by MySQL as explained in a miniscule comment in the docs: CREATE TABLE

CHECKMySQL 会忽略约束，如文档中的一个小注释中所述： CREATE TABLE

The CHECKclause is parsed but ignored by all storage engines.

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

回答by ryanprayogo

The CHECKconstraint doesn't seem to be implemented in MySQL.

该CHECK约束似乎没有在 MySQL 中实现。

See this bug report: https://bugs.mysql.com/bug.php?id=3464

请参阅此错误报告：https: //bugs.mysql.com/bug.php?id=3464

回答by Markus Barthlen

As mentioned by joanq MariaDB now seems to support CHECK constraints among other goodies:

正如 joanq MariaDB 所提到的，现在似乎支持 CHECK 约束以及其他好处：

"Support for CHECK CONSTRAINT (MDEV-7563)."

“支持检查约束 ( MDEV-7563)。”

https://mariadb.com/kb/en/mariadb/mariadb-1021-release-notes/

https://mariadb.com/kb/en/mariadb/mariadb-1021-release-notes/

回答by James

Check constraints are supported as of version 8.0.15 (yet to be released)

从 8.0.15 版本开始支持检查约束（尚未发布）

https://bugs.mysql.com/bug.php?id=3464

https://bugs.mysql.com/bug.php?id=3464

[23 Jan 16:24] Paul Dubois

Posted by developer: Fixed in 8.0.15.

Previously, MySQL permitted a limited form of CHECK constraint syntax, but parsed and ignored it. MySQL now implements the core features of table and column CHECK constraints, for all storage engines. Constraints are defined using CREATE TABLE and ALTER TABLE statements.

[1 月 23 日 16:24] 保罗·杜波依斯

开发者发布：在 8.0.15 中修复。

以前，MySQL 允许有限形式的 CHECK 约束语法，但会解析并忽略它。MySQL 现在为所有存储引擎实现了表和列 CHECK 约束的核心功能。使用 CREATE TABLE 和 ALTER TABLE 语句定义约束。

回答by sdlins

Update to MySQL 8.0.16 to use checks:

更新到 MySQL 8.0.16 以使用checks：

As of MySQL 8.0.16, CREATE TABLE permits the core features of table and column CHECK constraints, for all storage engines. CREATE TABLE permits the following CHECK constraint syntax, for both table constraints and column constraints

从 MySQL 8.0.16 开始，CREATE TABLE 允许所有存储引擎的表和列 CHECK 约束的核心功能。CREATE TABLE 允许以下 CHECK 约束语法，用于表约束和列约束

MySQL Checks Documentation

MySQL 检查文档

回答by Kanagu

try with set sql_mode = 'STRICT_TRANS_TABLES'OR SET sql_mode='STRICT_ALL_TABLES'

尝试set sql_mode = 'STRICT_TRANS_TABLES'或SET sql_mode='STRICT_ALL_TABLES'