---恢复内容开始---
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 | - |
---恢复内容结束---