11
19
2011
2

ENUM和SET的区别

ENUM和SET的区别_freeman_新浪博客

ENUM和SET都是比较特殊的字符串数据列类型,它们的取值范围是一个预先定义好的列表。ENUM或SET数据列的取值只能从这个列表中进行选择。ENUM和SET的主要区别是:



ENUM只能取单值,它的数据列表是一个枚举集合。它的合法取值列表最多允许有65535个成员。例如:ENUM("N","Y")表示,该数据列的取值要么是"Y",要么就是"N"。





SET可取多值。它的合法取值列表最多允许有64个成员。空字符串也是一个合法的SET值。



ENUM和SET的值是以字符串形式出现的,但在内部,MySQL以数值的形式存储它们。



ENUM的合法取值列表中的字符串被按声明定义的顺序被编号,从1开始。





SET
的编号不是按顺序进行编号的,SET中每一个合法取值都对应着SET值里的一个位。第一个合法取值对应0位,第二个合法取值对应1位,以此类推,如果数值形式的SET值等于0,则说明它是一个空字符串,如果某个合法的取值出现在SET数据列里,与之对应的位就会被置位;如果某个合法的取值没有出现在SET
数据列里,与之对应的位就会被清零。正因为SET值与位有这样的对应关系,所以SET数据列的多个合法取值才能同时出现并构成SET值。




集合 SET

mysql> create table jihe(f1 set('f','m'));

Query OK, 0 rows affected (0.11 sec)

mysql> desc jihe;

+-------+--------------+------+-----+---------+-------+

| Field |
Type        
| Null | Key | Default | Extra |

+-------+--------------+------+-----+---------+-------+

|
f1   
| set('f','m') | YES 
|    
|
NULL   
|      
|

+-------+--------------+------+-----+---------+-------+

可以插入值 "f" "m"

mysql> insert into jihe values("f");

Query OK, 1 row affected (0.56 sec)

mysql> insert into jihe values("m");

Query OK, 1 row affected (0.19 sec)

可以按照序号输入 注意序号为 1 2 4 8 16 32
....

mysql> insert into jihe values("1");

Query OK, 1 row affected (0.11 sec)

mysql> insert into jihe values("2");

Query OK, 1 row affected (0.11 sec)

所以如果是3那插入的值为 第一个和第二个

mysql> insert into jihe values("3");

Query OK, 1 row affected (0.17 sec)

+-------+

|
f1   
|

+-------+

|
f    
|

|
m    
|

|
f    
|

|
m    
|

| f,m   |

+-------+

其他字母不能插入

mysql> insert into jihe values("q");

ERROR 1265 (01000): Data truncated for column 'f1' at row 1

插入空

mysql> insert into jihe values("0");

Query OK, 1 row affected (0.11 sec)

超出序号之和不能插入

mysql> insert into jihe values("4");

ERROR 1265 (01000): Data truncated for column 'f1' at row 1

 

枚举

mysql> create table meiju(f1
enum('1','2','3','4','5','6'));

Query OK, 0 rows affected (0.92 sec)

mysql> desc meiju;

+-------+-------------------------------+------+-----+---------+-------+

| Field |
Type                         
| Null | Key | Default | Extra |

+-------+-------------------------------+------+-----+---------+-------+

|
f1   
| enum('1','2','3','4','5','6') | YES 
|    
|
NULL   
|      
|

+-------+-------------------------------+------+-----+---------+-------+

 

mysql> insert into meiju values(2);

mysql> insert into meiju values("2");

mysql> insert into meiju values("6");

序号为 1  2 
3  4  5

内容没有序号也没有就不能插入

mysql> insert into meiju values(8);

ERROR 1265 (01000): Data truncated for column 'f1' at row 1

mysql> insert into meiju values("8");

ERROR 1265 (01000): Data truncated for column 'f1' at row 1

mysql> select * from meiju;

+------+

| f1   |

+------+

|
1   
|

|
2   
|

|
2   
|

|
6   
|

+------+

改表

mysql> alter table meiju modify f1
enum("a","b","c","d","e","f");

Query OK, 4 rows affected (0.39 sec)

Records: 4  Duplicates:
0  Warnings: 0

可以自动将记录当序号匹配成新的字段值(挺神奇)

mysql> select * from meiju;

+------+

| f1   |

+------+

|
a   
|

|
b   
|

|
b   
|

|
f   
|

+------+

一个记录只能有一个枚举值 不像set 可以累加

mysql> insert into meiju values(7);

ERROR 1265 (01000): Data truncated for column 'f1' at row 1

注意最好输入的是值得时候值要加引号 否则当作序号来处理

当带引号的数字不符合枚举要求时会试一试当作序号合适不合适

如 insert into meiju values("7");

7不在枚举值内 就插入第7个值 如果没有7个值则报错

集合 和 枚举的区别

1 集合可以有64个值 枚举有65535个

2 集合的序号是 1 2 4 8 16 枚举是 1 2 3 4 5 6

3 集合一个字段值可以有好几个值

+-------+

|
f1   
|

+-------+

|
f,m   |

+-------+

而枚举只能有一个

Category: 数据库 | Tags: | Read Count: 2513
Avatar_small
Comprar seguidores 说:
2020年9月02日 19:54

Eu posso definir minha nova idéia a partir deste post. Ele fornece informações detalhadas. Obrigado por esta informação valiosa para todos.

Avatar_small
Grade 5 Result Sylhe 说:
2022年9月03日 02:09

Government of the People’s Republic of Bangladesh, Directorate of Primary Education (DPE), is going to announce PSC Result 2022 in student wide on 30th December 2022 for all divisional Grade 5 exam result with Ebtedayee Result 2022 for annual final terminal examinations, The Primary School Certificate Examination Result 2022 will be announced for both of General and Madhrsah students in division wise to all education board known as Prathomik Somaponi Result 2022. Grade 5 Result Sylhet BoardThe DPE has successfully conducted the class 5th grade PSC and Ebtedayee Examination tests from 17th to 24th November 2022 under all education boards of Dhaka, Chittagong, Comilla, Rajshahi, Sylhet, Barisal, Jessore, Dinajpur and Madrasah Board, and the DPE Grade-5 exams are successfully conducted at all 7,194 centers across the country.


登录 *


loading captcha image...
(输入验证码)
or Ctrl+Enter

Host by is-Programmer.com | Power by Chito 1.3.3 beta | Theme: Aeros 2.0 by TheBuckmaker.com