---恢复内容开始---
6枚举类型
语法
<enum_type>
: ENUM '(' <char_string_literal_list> ')'
<char_string_literal_list>
: <char_string_literal_list> ',' CHAR_STRING
| CHAR_STRING
如
CREATE TABLE tbl (
color ENUM('red', 'yellow', 'blue')
);
结果
Value |
Index Number |
---|---|
NULL |
NULL |
'red' |
1 |
'yellow' |
2 |
'blue' |
3 |
插入 INSERT into tbl values ('yellow'), ('red'), (2), ('blue');
结果
SELECT color FROM tbl;
color
======================
yellow
red
yellow
blue
SELECT color FROM tbl ORDER BY color ASC;
color
======================
red
yellow
yellow
blue
SELECT color FROM tbl ORDER BY cast(color as char) ASC;
color
======================
blue
red
yellow
yellow
-
使用字符串上下文,则枚举返回字符串 SELECT CONCAT(enum_col, 'color') FROM tbl_name;
CONCAT(color, '_color')
======================
yellow_color
red_color
yellow_color
blue_color
使用数字上下文 返回数字
-
SELECT color + 0 FROM tb;
color + 0
======================
2
1
2
3
-
是条件为数字的时候 ,为枚举的索引 SELECT color FROM tbl WHERE color <= 1;
color
======================
red
- 使用字符串条件 枚举为字符串
-
-
SELECT color FROM tbl WHERE color <= 'red';
color
-
注意索引默认从0开始 字符串的值不可以是null
如果枚举值本身就是数字,请用单引号括起来 如'1'
枚举排序是按索引排序的. 所以下面的语句是什么意思,应该明白了吧.先cast转换下
SELECT color FROM tb ORDER BY cast(color as char) ASC;
枚举和普通类型对应关系
*SHORT |
Index Number |
*INTEGER |
Index Number |
*BIGINT |
Index Number |
*FLOAT |
Index Number |
*DOUBLE |
Index Number |
*NUMERIC |
Index Number |
*MONETARY |
Index Number |
*TIME |
String |
*DATE |
String |
*DATETIME |
String |
*TIMESTAMP |
String |
*CHAR |
String |
*VARCHAR |
String |
BIT |
String |
VARBIT |
String |
注意 如果是使用jdbc驱动,使用枚举有些区别
7 集合
有三种
Type |
Description |
Definition |
Input Data |
Stored Data |
---|---|---|---|---|
SET |
A union which does not allow duplicates |
col_name SET VARCHAR(20) |
{'c','c','c','b','b','a'} |
{'a','b','c'} |
MULTISET |
A union which allows duplicates |
col_name MULTISET VARCHAR(20) |
{'c','c','c','b','b','a'} |
{'a','b','b','c','c','c'} |
LIST |
A union which allows duplicates and stores data in the order of input |
col_name LIST VARCHAR(20) |
{'c','c','c','b','b','a'} |
{'c','c','c','b','b','a'} |
集合间的转换
TO | ||||
---|---|---|---|---|
FROM |
SET |
MULTISET |
LIST |
|
SET |
- |
O |
O |
|
MULTISET |
O |
- |
X |
|
LIST |
O |
O |
- |
---恢复内容结束---