(补1)(single dog single day 人丑就要多读书)
3.2.5字符串数字类型
3.2.5.4enum和set数据类型
————————————————————————————————————
enum和set是比较特殊的字符串数字类型,他们只能从一个固定的(预先定义好的)字符串列表里取值.
这两种类型的主要区别是:
enum列值必须包含且只能包含一个值列表成员,
而set列值允许包含任意多个值列表成员(可以为空,也可以是全体成员).
换句话说,enum类型的值不允许同时出现,而set类型的值允许同时出现.
enum类型定义的是枚举集合,它最多允许有65535个成员.赋给enum列的值只能是创建表时指定的值列表的一个成员.枚举类型通常用于表示类别值.例如:对于某个定义为enum('N,'Y')
的列,它的值只能是N或者Y.也可以用来表示多项选择题的答案,衣服的尺码,颜色之类的由一定的元素组成的结果集.
column_name enum ('thin' , 'regular ',' pan style','deep dish')
size enum ('small','medium','large')
set与enum类型相似,同样在需要时为它指定一个所允许的集合成员列表.set类型最多允许有64个成员.
与enum不同的是每一个值列都可以由集合的任何元素构成.
如果有一组固定值,但与在enum列里的情况不同,他们不是互斥的,这时就可以使用set
set ('pepperoni','sausage','mushromm','onions','ripe olives')
set值示例:
'peperoni,mushrooms'
'sausage,onions'
'sausage,mushroom,ripe olives'
'onions'
''
期中最后那个值(空串)表示的是,顾客在订购时没有选择任何配料.set列允许这样的值
set列的定义,可以写成以逗号分隔的单个字符串列表,表示所有的集合成员.另一方面.set值的列必须是一个字符串并且是一个不包含逗号的字符串,因为set列里,是通过逗号分隔开各个成员的.
使用enum和set列的时候必须考虑几个因素:
1.这个列表已经确定了列的所有允许值
2.enum或set列带有一个不区分大小写的排序规则,那么在插入合法值时也不用区分大小写,他们都能够识别出来.但是在检索enum和set列的数据时,他们必须按照列的定义合法取值列表里的字母大小写形式来显示.
3.enum定义里的值的顺序就是排序所用的顺序.set定义里的值的排序也确定了排序顺序,只是关系更为复杂些,因为其列值可能包含多个集合成员
4.当MySQL显示某个由多个集合成员构成的set值时,他们的顺序由在set列出现的顺序确定
在创建enum和set类型列时,需要以字符串的形式列出枚举和集合成员,因此他们都被归为字符串类型.但是,enum和set类型实际上内部的存储方式是数字,并且也可以把他们当作数字来对待.这意味着,enum和set类型要比其他字符串类型有着更好的处理性能,同时enum和set值可以用在数字环境里.
enum
MySQL将从1开始依次对enum列定义里的成员进行顺序编号(编号0被保留为出错代码,字符串形式为一个空串).enum列占用的存储空间大小由枚举的个数确定.
一个字节表示256个值,两个字节表示65535个值,加上出错代码就是65536个,而对于它所占用的存储空间是一个还是两个字节,取决于成员数是否超过256个
MySQL需要为出错代码预留一个位置,并将他做为每一个枚举的隐含成员,所以在enum定义里最多能够指定的是65535个成员.
create table e_table (e enum('jane','fred','will','marcia'));
insert into e_table
values ('janes'),('fred'),('will'),('marcia'),(NULL);
select e,e+0,e+1,e*3 from e_table;
+--------+------+------+------+
| e | e+0 | e+1 | e*3 |
+--------+------+------+------+
| jane | 1 | 2 | 3 |
| fred | 2 | 3 | 6 |
| will | 3 | 4 | 9 |
| marcia | 4 | 5 | 12 |
| NULL | NULL | NULL | NULL |
+--------+------+------+------+
可以按照名字或者编号来对enum成员进行比较:
select e from e_table where e='will';
select e from e_table where e=3;
也可以把空串定义为有效的枚举成员,不过这个时候需要多加注意.这个字符串被赋值为一个非零值,与该定义里列出的其他成员没什么两样.但是.空串也被用于那个数值为0的错误成员,因此他会对应到两个内部数字元素值.
在下面的示例里,当把非法枚举值’x’插入到enum列时,它会被赋值为出错成员.只有以数字形式检索,才能把列定义里的空串成员区别出来.
create table t (e enum ('a' , '' ,'b'));
insert into t values ('a'),(''),('b'),('x');
select e ,e+0 from t;
预期结果如下
e e+0
-----------
a 1
2
b 3
0
严格模式下’x’会导致错误~~~~我这里就报错了
set
set列的数字表示与enum列稍有不同.set成员并没有按顺序编号.每个set成员对应着set值里的一个二进制.第一个成员对应于第零位,第二个成员对应于为第一位set成员的数字值都是2的幂.空串对应的set数字值为o
set的值都被存储为二进制位值.每个字节对应八个set成员,所以一个 set列占用的存储空间取决于它的成员个数,最多有64个成员.如果set成员数量范围分别为1~8个,9~16个,17~24个,25~32个,33~64个,对应的占用字节空间为1,2,3,4或8个字节
一个set可以由一组二进制位表示,那么一个set值就可以由多个集合成员组成.由于这样的值可以展开为某种二进制的组合,因此它也可以由set定义里与这些二进制位相对应的那些字符串组合而成.
select table s_table (s set( 'table', 'lamp', 'chair','stool' ));
insertt intio s_table
values('table'),('lamp'),('chair'),('stool'),(''),(NULL);
select s,s+0,bin(s+0), from s_table ;
组合效果
当把‘lamp.stool’赋给s列时,MySQL会在其内部把它存储为10(二进制数1010),
因为lamp数字式2(第一位10),’stool’数值为8(第三位1000)
s s+0 bin(s+0)
---------------------------
table 1 1
lamp 2 10
chair 4 100
stool 8 1000
0 0
NULL NULL NULL
在对set列进行赋值时,
其中各个子字符串的顺序与定义这个列时所用的顺序不用完全一样.不过,在以后的检索操作中,这个成员是按他们在声明里的先后顺序现实的.
赋值时,如果这个值包含的某些子字符串不属于集合成员,那么这些子字符串会被剔除,剩余的子字符串会被赋值给这个列.,检索时,也看不到这些非法字符串.
严格模式下~~报错
MySQL会重排set列值里的成员,这一事实说明,如果想要搜索那些使用字符串的值,那么必须按照正确的顺序列出各个成员.
enum set
enum和set列的排序和索引操作都是按照列值的内部值(数字值)执行的.
select e from e_table order by e;
-----
e
--
NULL
jane
will
marcia
为了看清具体的数字形式
select e,e+0 from e_table order by e;
-----------
e e+0
-----------
NULL NULL
jane 1
fred 2
will 3
marcia 4
如果你想要按照某种顺序进行排序,那么可以利用enum类型的排序特性.即先将这些值表示为某个表里的一个enum列,并在该列的定义里按照你想要的顺序依次列出各个值
enum列可以使用cast()来改变enum为一个非enum的字符串,然后再进行排序
不会更改显示值,坠毁改变这些值的排序特性.
select cast(e as char) as e_str from e_table order by e_str;