赞
踩
SET类型和ENUM类型一样,也是字符串类型的一种。
A SET is a string object that can have zero or more values, each of which must be chosen from a list
of permitted values specified when the table is created. SET column values that consist of multiple set
members are specified with members separated by commas (,). A consequence of this is that SET
member values should not themselves contain commas.
SET的值范围也需要在创建表时显示指定。比如,一个列的数据类型指定为SET(‘one’, ‘two’) NOT NULL,它可以具有以下值:
'one'
'two'
'one,two'
SET类型定义的时候可以包含0~64个成员。根据成员的不同,存储上也有所不同。
1~8成员的集合,占1个字节。
9~16成员的集合,占2个字节。
17~24成员的集合,占3个字节。
25~32成员的集合,占4个自己。
33~64成员的集合,占8个字节。
SET和ENUM最主要的区别在于SET类型一次可以使用多个成员,而ENUM则只能一个。
下面我们就通过例子来测试下。
root@database-one 23:39: [gftest]> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
Query OK, 0 rows affected (0.03 sec)
root@database-one 23:39: [gftest]> INSERT INTO myset (col) VALUES('a,d');
Query OK, 1 row affected (0.00 sec)
root@database-one 23:39: [gftest]> INSERT INTO myset (col) VALUES('d,a');
Query OK, 1 row affected (0.01 sec)
root@database-one 23:40: [gftest]> INSERT INTO myset (col) VALUES('a,d,a');
Query OK, 1 row affected (0.02 sec)
root@database-one 23:40: [gftest]> INSERT INTO myset (col) VALUES('a,d,d');
Query OK, 1 row affected (0.00 sec)
root@database-one 23:40: [gftest]> INSERT INTO myset (col) VALUES('d,a,d');
Query OK, 1 row affected (0.01 sec)
root@database-one 23:41: [gftest]> INSERT INTO myset (col) VALUES('A');
Query OK, 1 row affected (0.01 sec)
root@database-one 23:41: [gftest]> INSERT INTO myset (col) VALUES('a,C,b,d');
Query OK, 1 row affected (0.00 sec)
root@database-one 23:41: [gftest]> select * from myset;
+---------+
| col |
+---------+
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
| a |
| a,b,c,d |
+---------+
7 rows in set (0.00 sec)
通过上面可以看到,SET类型的列确实可以接受多个成员,同时:
值中的每个元素都只会出现一次
插入时元素的顺序无关紧要,会按照在表创建时指定的顺序列出
忽略大小写,在存储时将它们都转换成了建表时定义的大/小写
如果插入不在集合范围内的值时,会怎么样呢?我们来试试:
root@database-one 23:52: [gftest]> INSERT INTO myset (col) VALUES('e');
Query OK, 1 row affected, 1 warning (0.00 sec)
root@database-one 23:54: [gftest]> show warnings;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'col' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.01 sec)
root@database-one 23:54: [gftest]> select * from myset;
+---------+
| col |
+---------+
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
| a |
| a,b,c,d |
| |
+---------+
8 rows in set (0.00 sec)
root@database-one 23:55: [gftest]> INSERT INTO myset (col) VALUES('a,e');
Query OK, 1 row affected, 1 warning (0.00 sec)
root@database-one 23:55: [gftest]> show warnings;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'col' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)
root@database-one 23:55: [gftest]> select * from myset;
+---------+
| col |
+---------+
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
| a |
| a,b,c,d |
| |
| a |
+---------+
9 rows in set (0.00 sec)
通过上面可以看到,插入的值不在集合范围内,会被忽略并且产生警告。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。