MySQL数据类型 ENUM和SET
来源:互联网 发布:淘宝商城女装卫衣套装 编辑:程序博客网 时间:2024/06/07 00:29
【1】ENUM和SET类型都是集合类型,不同的是ENUM类型最多可枚举65 535个元素,而SET类型最多枚举64个元素。
由于MySQL不支持传统的CHECK约束,因此通过ENUM和SET类型并结合SQL_MODE可以解决一部分问题。例如,表中有一个“性别”列,规定域的范围只能是male和female,
在这种情况下可以通过ENUM类型结合严格的SQL_MODE模式进行约束,过程如下:
mysql> CREATE TABLE t (
-> user VARCHAR(30),
-> sex ENUM('male','female')
-> )ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> SET SQL_MODE='strict_trans_tables';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t SELECT 'David','male';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t SELECT 'Mariah','female';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t SELECT 'John','bimale';
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
可以看到,前两次的操作正确地插入了“male”和“female”值,而对于未定义的“bimale”,
MySQL数据库在严格的SQL_MODE下抛出了报错警告,起到了一定的约束作用。
【2】
ENUM 管理一个成员多达65535个的字符串集合,集合中每个字符串都有一个顺序排列的编号,
而MySQL实际存入ENUM数据列的正是这些编号而不是字符串本身。ENUM数据列每个字段只能存入一个字符串。
在涉及字符串比较操作时,MySQL不区分ENUM数据的字母大小写情况。
空白字符串(注意,不是空字符串)总是可以存入ENUM字段,
但只有不带not null属性的ENUM数据列才允许null(空字符串)存入。
SET 可以把任意多个字符串组合存入SET字段,MySQL在其内部会对SET集合里的字符串按2的幂次进行编号,
这样可以利用各种二进制按位比较出来SET数据,但造成可用空间的巨大浪费,同一个SET字段最多只能容纳64个字符串构成的组合。
一个SET数据占用8个字节,则有8*8=64位,所以每个SET字段最多容纳64个字符串,如有以下set数据集合
{1,2,3,4,5,6,7……64}
ALTER TABLE table_name ADD color SET ('red','green','yellow')
INSERT table_name(color) values('red,green')
插入新记录时,SET字段里的字符串顺序无关紧要,存储和查询结果里,那些字符串按它们在SET集合的定义顺序排列显示。
在查询命令中,’=’操作符比较SET数据的意思是两组字符串组合完全匹配,如果想查找SET字段包含的某给定字符串的记录,用find_in_set()函数。
select * from testset where find_in_set('italic',fontattr)>0
返回给定字符串’italic’在字段fontattr中的定义顺序,如果大于0,说明在字段中。。。
describe tablename columnname
查找某个ENUM或SET集合里包含哪些成员字符串。
ENUM和SET字段排序时按照它们在ENUM或SET集合的先后顺序,如果要按照字母表顺序,需要转换。
select concat(x) as xstr .... order by xstr
【3】
SET类型
SET是一个字符串对象,可以有零或多个值,其值来自表创建时规定的允许的一列值。指定包括多个SET成员的SET列值时各成员之间用逗号(‘,’)间隔开。所以SET成员值本身不能包含逗号。
例如,指定为SET('one', 'two') NOT NULL的列可以有下面的任何值:
''
'one'
'two'
'one,two'
SET最多可以有64个不同的成员。
当创建表时,SET成员值的尾部空格将自动被删除。
当检索时,保存在SET列的值使用列定义中所使用的大小写来显示。请注意可以为SET列分配字符集和校对规则。对于二进制或大小写敏感的校对规则,当为列分配值时应考虑大小写。
MySQL用数字保存SET值,所保存值的低阶位对应第1个SET成员。如果在数值上下文中检索一个SET值,检索值的位置对应组成列值的SET成员。例如,你可以这样从一个SET列检索数值值:
mysql> SELECT set_col+0 FROM tbl_name;
如果将一个数字保存到SET列中,数字中二进制表示中的位确定了列值中的SET成员。对于指定为SET('a','b','c','d')的列,成员有下面的十进制和二进制值:
SET成员
十进制值
二进制值
'a'
1
0001
'b'
2
0010
'c'
4
0100
'd'
8
1000
如果你为该列分配一个值9,其二进制形式为1001,因此第1个和第4个SET值成员'a'和'd'被选择,结果值为'a,d'。
对于包含多个SET元素的值,当插入值时元素所列的顺序并不重要。在值中一个给定的元素列了多少次也不重要。当以后检索该值时,值中的每个元素出现一次,根据表创建时指定的顺序列出元素。例如,假定某个列指定为SET('a','b','c','d'):
mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
插入值'a,d'、'd,a'、'a,d,d'、'a,d,a'和'd,a,d':
mysql> INSERT INTO myset (col) VALUES
-> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
当检索时所有这些值显示为 'a,d':
mysql> SELECT col FROM myset;
+------+
| col |
+------+
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
+------+
5 rows in set (0.04 sec)
如果将SET列设置为一个不支持的值,则该值被忽略并发出警告:
mysql> INSERT INTO myset (col) VALUES ('a,d,d,s');
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'col' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.04 sec)
mysql> SELECT col FROM myset;
+------+
| col |
+------+
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
+------+
6 rows in set (0.01 sec)
SET值按数字顺序排序。NULL值排在非NULL SET值的前面。
通常情况,可以使用FIND_IN_SET()函数或LIKE操作符搜索SET值:
mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;
mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
第1个语句找出SET_col包含value set成员的行。第2个类似,但有所不同:它在其它地方找出set_col包含value的行,甚至是在另一个SET成员的子字符串中。
下面的语句也是合法的:
mysql> SELECT * FROM tbl_name WHERE set_col & 1; #寻找包含第1个set成员的值
mysql> SELECT * FROM tbl_name WHERE set_col & 2; #寻找包含第2个set成员的值
mysql> SELECT * FROM tbl_name WHERE set_col & 4; #寻找包含第3个set成员的值
mysql> SELECT * FROM tbl_name WHERE set_col & 3; #寻找包含第1个或第2个set成员的值
mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
该语句寻找一个确切匹配的值。将set值与'val1,val2'比较返回的结果与同'val2,val1'比较返回的结果不同。指定值时的顺序应与在列定义中所列的顺序相同。
如果想要为SET列确定所有可能的值,使用SHOW COLUMNS FROM tbl_name LIKE set_col并解析输出中第2列的SET定义。
ENUM类型
ENUM是一个字符串对象,其值来自表创建时在列规定中显式枚举的一列值。
在某些情况下,ENUM值也可以为空字符串('')或NULL:
·
·
每个枚举值有一个索引:
·
·
· mysql> SELECT * FROM tbl_name WHERE enum_col=0;
·
例如,定义为ENUM的列('one','two','three')可以有下面所示任何值。还显示了每个值的索引:
值
索引
NULL
NULL
''
0
'one'
1
'two'
2
'three'
3
枚举最多可以有65,535个元素。
当创建表时,ENUM成员值的尾部空格将自动被删除。
当检索时,保存在ENUM列的值使用列定义中所使用的大小写来显示。请注意可以为ENUM列分配字符集和校对规则。对于二进制或大小写敏感的校对规则,当为列分配值时应考虑大小写。
如果在数值上下文中检索一个ENUM值,将返回列值的索引。例如,你可以这样从ENUM列搜索数值值:
mysql> SELECT enum_col+0 FROM tbl_name;
如果将一个数字保存到ENUM列,数字被视为索引,并且保存的值是该索引对应的枚举成员。(但是,这不适合LOAD DATA,它将所有输入视为字符串)。不建议使用类似数字的枚举值来定义一个ENUM列,因为这很容易引起混淆。例如,下面的列含有字符串值'0'、'1'和'2'的枚举成员,但数值索引值为1、2和3:
numbers ENUM('0','1','2')
根据枚举成员在列定义中列出的顺序对ENUM值进行排序。(换句话说,ENUM值根据索引编号进行排序)。例如,对于ENUM('a','b'),'a'排在'b'前面,但对于ENUM('b','a'),'b'排在'a'前面。空字符串排在非空字符串前面,并且NULL值排在所有其它枚举值前面。要想防止意想不到的结果,按字母顺序规定ENUM列。还可以使用GROUP BY
如果你想要确定一个ENUM列的所有可能的值,使用SHOW COLUMNS FROM tbl_name LIKE enum_col,并解析输出中第2列的ENUM定义。
原文链接:
1》http://book.2cto.com/201211/8319.html
2》http://blog.sina.com.cn/s/blog_5d2026870100fjdy.html
- MySQL数据类型 ENUM和SET
- mysql中的数据类型enum和set
- ENUM和SET类型 mysql
- MySQL 四种特殊数据类型 ENUM、SET、BOOL/BOOLEAN、TINYINT
- mysql set类型和enum类型
- mysql中的enum和set类型
- MySQL SET类型和ENUM类型
- Mysql数据类型---ENUM
- mysql中的set和enum类型的用法和区别
- 关于Mysql的Enum和Set字段类型
- mysql中的较少用到的数据类型的一点说明,real;enum,set;POINT,LINESTRING,POLYGON等
- ENUM和SET的区别
- Mysql数据类型---SET
- MySQL数据类型:TINYINT,SMALLINT,MEDIUMINT,INT,INTEGER,BIGINT;text,longtext,mediumtext,ENUM,SET等字段类型区别
- mysql之set、enum的认识
- MySQL SET,ENUM类型数据小结
- MySQL的enum与set约束
- MySQL 改变列的数据类型为ENUM
- Java的clone方法
- java基本的数据类型
- Cocos2d-x高斯模糊
- 面试题:十大经典算法总结(Javascript描述)
- java -- 传值与传引用
- MySQL数据类型 ENUM和SET
- Java-面向对象(基础篇)--实例讲解
- Universal-Image-Loader的简单介绍和使用
- viicms仿乐享微信源码官方版
- Android 加入购物车动画
- 配置php.ini令当使用$_REQUEST的时候保存的是get的数据
- Mybatis快速入门
- Java clone() 浅克隆与深度克隆
- 线性可分支持向量机(一)