创建数据库表使用CONSTRAINT关键字时出错

Question

失败：

CREATE TABLE IF NOT EXISTS `demo`(
    `id` INT ,
    `age` INT  CONSTRAINT c1 CHECK(`age`>=18),
    PRIMARY KEY(`id`)
);

成功:

CREATE TABLE IF NOT EXISTS `demo`(
    `id` INT ,
    `age` INT CHECK(`age`>=18),
    PRIMARY KEY(`id`)
);

是我CONSTRAINT的用法有问题吗，

Answer 1

目前官网已经查不到5.5的文档了,最早的5.7版本文档中对check是这么描述的

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

大概意思就是check语法可以被解析,但是会被忽略。这和你描述的现象一致。
在5.7版本中,是没有 "CONSTRAINT CHECK"语法的,所以更老的5.5估计也没有

https://dev.mysql.com/doc/refman/5.7/en/create-table.html

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

create_definition: {
    col_name column_definition
  | {INDEX | KEY} [index_name] [index_type] (key_part,...)
      [index_option] ...
  | {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] PRIMARY KEY
      [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
      [index_name] [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (col_name,...)
      reference_definition
  | CHECK (expr)
}

但8.0版本是有这个语法的

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

[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]