CHECK约束与多个条件MySQL

Question

CREATE TABLE shoesize( 
nr TINYINT NOT NULL,
shoesize VARCHAR(6) NOT NULL,
CHECK (shoesize = 'mini' OR 'medium' OR 'maxi'),
PRIMARY KEY (nr)
)engine=innodb;

insert into shoesize(nr,shoesize) values ('1','mini');
insert into shoesize(nr,shoesize) values ('2','medium');
insert into shoesize(nr,shoesize) values ('3','maxi');
insert into shoesize(nr,shoesize) values ('4','ultra');
insert into shoesize(nr,shoesize) values ('5','mega');
Error Code: 3819. Check constraint 'shoesize_chk_1' is violated.    0.015 sec

我试图做一个约束，即只接受特定文本的鞋子。但是，当我尝试输入数据时，约束会激发。就我而言，支票上应该有OR？

Answer 1

您的问题是只检查第一个值mini，但您需要重复该等式才能获得其余的

CREATE TABLE shoesize( 
nr TINYINT NOT NULL,
shoesize VARCHAR(6) NOT NULL,
CHECK (shoesize = 'mini' OR  shoesize =  'medium' OR shoesize = 'maxi'),
PRIMARY KEY (nr)
)engine=innodb;

insert into shoesize(nr,shoesize) values ('1','mini');
insert into shoesize(nr,shoesize) values ('2','medium');
insert into shoesize(nr,shoesize) values ('3','maxi');
insert into shoesize(nr,shoesize) values ('4','ultra');
insert into shoesize(nr,shoesize) values ('5','mega');

SELECT * FROM shoesize

nr	shoesize
1	mini
2	中等
3	最大值