MySQL数据类型-枚举


枚举类型

枚举类型,字段值只能在列举范围选择

格式:

字段名 enum(值1,值2,值N) 单选

字段名 set(值1,值2,值N) 多选

mysql> create table t25(    -> name char(10),    -> sex enum("boy","girl"),    -> likes set ("book","game","music","file")    -> );Query OK, 0 rows affected (0.50 sec)mysql>
mysql> desc t25;+-------+-----------------------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-----------------------------------+------+-----+---------+-------+| name | char(10) | YES | | NULL | || sex | enum('boy','girl') | YES | | NULL | || likes | set('book','game','music','file') | YES | | NULL | |+-------+-----------------------------------+------+-----+---------+-------+3 rows in set (0.00 sec)
mysql>
mysql> insert into t25 values ("lucy","boy","book,file");Query OK, 1 row affected (0.08 sec)
mysql> select * from t25;+------+------+-----------+| name | sex | likes |+------+------+-----------+| lucy | boy | book,file |+------+------+-----------+1 row in set (0.00 sec)mysql> insert into t25 values ("revon","1","book,file"); //1代表枚举类型里面的第一个数值Query OK, 1 row affected (0.08 sec)
mysql> select * from t25;+-------+------+-----------+| name | sex | likes |+-------+------+-----------+| lucy | boy | book,file || revon | boy | book,file |+-------+------+-----------+2 rows in set (0.00 sec)
mysql> insert into t25 values ("jason","2","book,file");Query OK, 1 row affected (0.09 sec)
mysql> select * from t25;+-----------+------+-----------+ | name | sex | likes |+-----------+------+-----------+| lucy | boy | book,file || revon | boy | book,file || jason | girl | book,file |+-----------+------+-----------+3 rows in set (0.00 sec)
mysql>

约束条件

下面的Null,Key,Default,Extra统称约束条件

mysql> desc t24;+-------+---------------------------+------+-----+---------+-------+| Field | Type                      | Null | Key | Default | Extra |+-------+---------------------------+------+-----+---------+-------+| age   | int(10) unsigned zerofill | YES  |     | NULL    |       |+-------+---------------------------+------+-----+---------+-------+1 row in set (0.00 sec)
mysql>

作用

限制如何给字段赋值

创建表t26,name不允许空,年龄无符号数值型,性别不允许空,默认值boy,爱好默认值book,film

mysql> create table t26( name char(10) not null, age tinyint(2) unsigned default 25,sex enum("boy","girl") not null default "boy", likes set ("book","game","music","film") default "book,film" );Query OK, 0 rows affected (0.41 sec)
mysql> desc t26;+-------+-----------------------------------+------+-----+-----------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-----------------------------------+------+-----+-----------+-------+| name | char(10) | NO | | NULL | || age | tinyint(2) unsigned | YES | | 25 | || sex | enum('boy','girl') | NO | | boy | || likes | set('book','game','music','film') | YES | | book,film | |+-------+-----------------------------------+------+-----+-----------+-------+4 rows in set (0.00 sec)
mysql> insert into t26(name) values("bob");Query OK, 1 row affected (0.05 sec)
mysql>mysql> select * from t26;+------+------+-----+-----------+| name | age | sex | likes |+------+------+-----+-----------+| bob | 25 | boy | book,film |+------+------+-----+-----------+1 row in set (0.00 sec)mysql>

name字段不允许位空NULL,所有insert数据的时候,如果不给name字段赋值会报错

mysql> insert into t26 (age,sex,likes) values(30,"girl","game");ERROR 1364 (HY000): Field 'name' doesn't have a default valuemysql>

给name字段赋值=null也不允许

mysql> insert into t26 (name,age,sex,likes) values(null,30,"girl","game");ERROR 1048 (23000): Column 'name' cannot be nullmysql>

但是给字段name赋值”null”或者””是可以的

mysql> insert into t26 (name,age,sex,likes) values("null",30,"girl","game");Query OK, 1 row affected (0.05 sec)
mysql>mysql> insert into t26 (name,age,sex,likes) values("",30,"girl","game");Query OK, 1 row affected (0.16 sec)
mysql> select * from t26;+------+------+------+-------+| name | age | sex | likes |+------+------+------+-------+| null | 30 | girl | game || | 30 | girl | game |+------+------+------+-------+2 rows in set (0.00 sec)mysql>