---恢复内容开始---

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

blue

SELECT color FROM tbl ORDER BY cast(color as char) ASC;

color

======================

blue

red

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) col_name SET (VARCHAR(20))	{'c','c','c','b','b','a'} {'c','c','c','b','b', 'a'}	{'a','b','c'} {'a','b','c'}
MULTISET	A union which allows duplicates	col_name MULTISET VARCHAR(20) col_name MULTISET (VARCHAR(20))	{'c','c','c','b','b','a'} {'c','c','c','b','b','a'}	{'a','b','b','c','c','c'} {'a','b','b', 'c','c','c'}
LIST SEQUENCE	A union which allows duplicates and stores data in the order of input	col_name LIST VARCHAR(20) col_name LIST (VARCHAR(20))	{'c','c','c','b','b','a'} {'c','c','c','b','b', 'a'}	{'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	-

---恢复内容结束---

ITPub博客

CUBRID学习笔记 25 数据类型2

最新文章