数据类型
概述
在下面的表中显示了常用的内置数据类型。
| 名字 | 别名 | 描述 |
|---|---|---|
| BLOB | 二进制大对象 | |
| BOOLEAN | 逻辑布尔值（true/false）类型 | |
| CHAR[(n)] | CHARACTER[(n)] | 固定长度为 n 字符串类型 |
| CLOB | 字符串大对象 | |
| DATE | compat 参数为 Oracle 时为精确到秒的日期时间类型，其他情况下为精确到天的日期时间类型 | |
| DOUBLE PRECISION | FLOAT, FLOAT(25) – FLOAT(53), BINARY_DOUBLE | 双精度浮点数值类型 |
| INTEGER | INT, BINARY_INTEGER | 有符号的 4 字节长度整数类型 |
| NUMBER | DECIMAL, NUMERIC | 带有小数位的精确数值类型 |
| NUMBER(p[, s]) | DECIMAL(p[, s]), NUMERIC(p[, s]) | 可使用数值范围 s，带有最大精度 p 的精确数值类型 |
| REAL | FLOAT(1) – FLOAT(24), BINARY_FLOAT | 单精度浮点数值类型 |
| RAW[(n)] | 最大长度为 n 的变长二进制串类型 | |
| TIMESTAMP[(p)] | 可以精确到秒 p 的日期时间类型 | |
| VARCHAR2[(n)] | CHAR VARYING[(n)], CHARACTER VARYING[(n)], VARCHAR[(n)], NVARCHAR2[(n)] | 最大长度为 n 的变长字符串类型 |
在下面的章节中会详细介绍了每一种数据类型。
数值类型
数值类型包含 4 字节整数，4 字节和8 字节浮点数以及固定精度的小数。在下面的表中列出了可以使用的数值类型。
| 名称 | 所占存储大小 | 描述 | 范围 |
|---|---|---|---|
| INTEGER | 4 字节 | 通常用于整数 | -2，147,483,648 to +2,147,483,647 |
| NUMBER | 存储空间大小可变 | 使用用户指定的精度 | 没有限制 |
| NUMBER(p [, s ] ) | 存储空间大小可变 | 可使用范围 s，最大精度为 p 的精确数值类型 | 最大 38 位 |
| NUMERIC | 相当于 number(38,0) | ||
| REAL | 相当于 number | ||
| FLOAT | 相当于 number | ||
| DOUBLE PRECISION | 相当于 number | ||
| BINARY_DOUBLE | 相当于 number | ||
| BINARY_FLOAT | 相当于 number | ||
| DECIMAL | 相当于 number |
在下面的章节中会详细介绍这些数据类型。
整数类型
INTEGER 类型用于存储整个数值，数值不带有小数部分。如果尝试存储的数值超出允许的数值范围，将会产生错误。
带有任意精度的数值
NUMBER 类型能够用于存储对于精度位数没有限制的数字，并且可以用于执行精确计算。当要求高精确度时，推荐使用这种类型来存储货币总量和其他类型的数量值。
下面是我们所使用的术语：NUMBER 类型数值的范围是小数点右边部分的小数位数。NUMBER 类型数值的精度是指整个数值包含的所有数字，也就是小数点左右两边的所有数字。所以，我们可以说数值 23.5141 的精度为 6，范围是 4。我们可以认为整数的范围是 0。
NUMBER 类型数值的精度和范围是可以配置的。通过使用下面所示的语法，可以把列的数据类型声明为 NUMBER。
NUMBER(precision, scale)
精度必须为整数，范围可以是 0 或整数，我们也可以采用如下方法声明：
NUMBER(precision)
指定数值范围为 0。而通过将列指定为不带任意精度和范围的 NUMBER 类型，可以列中存储任意精度和范围的数值，其中精度值可达到实际应用的上限。这种类型的列将不会把输入值强制为转换为任意特定的范围。如果列的数据类型是带有范围的 NUMBER 类型，那么会强制把输入值的范围转换为指定范围。（SQL 标准中要求缺省的范围值为 0，例如：强制指定数值的精度为整数的精度，从最方便使用的角度考虑，最好明确地指定精度和范围）
如果数值的精度或者范围大于列的数据类型所声明的精度和范围，那么系统将会试图对这个值进行四舍五入。如果不能对数值进行四舍五入的处理来满足数据类型的限制，则会产生一个错误。
浮点类型
在 AntDB 中，REAL、DOUBLE PRECISION、BINARY_DOUBLE、BINARY_FLOAT、DECIMAL、NUMERIC都是用 AntDB 中的 numeric 类型实现的，和 number 一样。
antdb=# CREATE TABLE tb(id int, a number(10,4),b binary_float, c float(4), d binary_double, e double precision, f real, g decimal(5,3), h numeric);
CREATE TABLE
antdb=# \d+ tb
Table "public.tb"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+---------------------
id | integer | | | | plain | |
a | numeric(10,4) | | | | main | |
b | numeric | | | | main | |
c | numeric | | | | main | |
d | numeric | | | | main | |
e | numeric | | | | main | |
f | numeric | | | | main | |
g | numeric(5,3) | | | | main | |
h | numeric(38,0) | | | | main | |
Access method: heap
字符类型
在下面的章节中会详细介绍了每一种数据类型。
| 名称 | 描述 |
|---|---|
| CHAR [ (n) ] | 固定长度，实际数据如果没有达到指定长度的话，会添加空格补齐，n<=10485760。 |
| CLOB | 长度可变，用 text 类型实现，存储任意长度字符串 |
| VARCHAR2(n) | 带有限制的可变长度类型，n<=10485760 |
CHAR(n)和VARCHAR2(n) 是两种最常用的字符类型，其中 n 是一个正整数，最大值是 10485760。这些类型可存储长度达到参数n指定长度的字符串。对于 CHAR 类型来说，如果不写 n，那么缺省值就是 1。如果尝试向列存储长度超过最大长度的字符串，将产生错误，除非超出长度的字符都是空格，这种情况下将会把字符串按照最大长度截断。如果存储的字符串长度小于声明的长度，那么对于 CHAR 类型的字符串值来说，将会用空格补齐，而对于 VARCHAR2 类型的字符串值来说，会按照实际长度存储。
如果为 VARCHAR2(n) 或者 CHAR(n) 明确地指定了 n 值的大小，那么将会把超出长度的值截断到参数 n 指定的长度，而不产生错误。（这也是 SQL 标准所要求的）
CHAR 型的字符串值实际上是通过附加空格来达到参数 n 所指定的长度，并且在存储和显示时都是以这种方式进行的。然而，附加的空格在语义上是不重要的。当比较两个 CHAR 型字符串值的时候，对于尾部的空格是忽略不计的，当把 CHAR 型的值转换成另外一种字符串类型的时候将会删除这些空格。需要注意的是，对于 VARCHAR2 来说，尾部的空格在语义上有意义的。
第三种用来存储长字符串的字符类型是 CLOB 数据类型。除了没有指定长度的限制外，CLOB 在语义上等同于 VARCHAR2。通常来说当字符串的最大长度不可知的时候，建议使用 CLOB 类型，而不是 VARCHAR2 类型。
CLOB 类型，用 text 类型实现，可以存储任意长度的字符串。
对于这三种类型数据的存储空间要求来说，如果字符串长度小于 127 个字节，那么实际存储空间的大小就是实际字符串的长度加上 1 个字节，如果字符串长度大于等于 127 个字节，那么实际存储空间的大小就是实际字符串长度加上 4 个字节。对于 CHAR 型数据来说，字符串后面附加的空格也是需要存储空间的。而系统会自动压缩长字符串，这样在磁盘上要求的实际存储空间要少一些。长字符串值是存储在后台表中，所以在快速访问短列值的时候，它们不会产生干扰。
数据库字符集决定了用来存储文本的字符集。
二进制数据
BLOB 数据类型允许存储二进制字符串。
| 名称 | 存储空间 | 描述 |
|---|---|---|
| BLOB | 如果二进制串的长度小于 127 个字节，实际 要求的长度是二进制串长度加上 1 个字节， 如果二进制串长度大于等于 127 个字符，那 么长度为二进制串的长度加上 4 个字节。 | 可变长度的二进制字符串，最大 存储 2G 的数据 |
Blob 类型的数据，select 的结果是如果是 16 进制的数据，可以通过设置参数 bytea_output = 'escape'，来显示实际插入的数据。
antdb=# CREATE TABLE tb(id int, a blob);
CREATE TABLE
antdb=# INSERT INTO tb VALUES(1, 'abcd');
INSERT 0 1
antdb=# SELECT * FROM tb;
ID | A
----+------------
1 | \x61626364
(1 row)
antdb=# SET bytea_output = 'escape';
SET
antdb=# SELECT * FROM tb;
ID | A
----+------
1 | abcd
(1 row)
二进制串是一个 8 位字节(或字节)的序列-与字符串相比，二进制串有两点不同：
首先，二进制串允许存放值为 0 的 8 位字节和其他无法打印显示的八位字节（定义的范围超过32-126）；
其次，在二进制串上进行的操作实际是处理字节，与之相反的是对字符串的编码和处理取决于本地设置。
日期/时间类型
AntDB 支持在下面表中列出的日期/时间类型。
| 名称 | 存储空间 大小 | 描述 | 最低值 | 最高值 | 精确度 |
|---|---|---|---|---|---|
| DATE | 8 字节 | 日期和时间 | 公 元 前 4713年 | 公元 294276年 | 1 秒 |
| TIMESTAMP [ (p) ] | 8 字节 | 日期和时间 | 公 元 前 4173年 | 公元 294276年 | 1 毫秒 |
| INTERVAL | 16 字节 | 时间间隔 | 1 毫秒 |
Date 类型底层实际存储的是年月日时分秒，如果不指定时间，则会显示年月日 00:00:00。
timestamp(p)：我们可以在 TIMESTAMP 类型的值上指定一个代表精度的值 p，用来指定在秒字段中能够保留的小数位数。参数 p 的取值范围在 0 到 6 之间，缺省值是 6，如果用户指定 p 的值大于 6，会自动转化为 6。
Interval：表示时间间隔，可以和时间进行加减操作。与 Oracle 相比，AntDB 对 Interval 并没有完全支持，具体见下文。
日期/时间输入
日期和时间输入值一般格式是 ISO 8601 SQL 兼容格式，Oracle 缺省的 dd-MON-yy 格式，以及其他的一些对年月日有明确的区分的格式。然而避免在格式上出现不明确的最好方法是使用函数 TO_DATE。
任何日期或者时间输入值都需要像文本字符串那样用单引号引起来，我们可以使用下面这种 SQL 标准语法：
type 'value'
其中 type 即可以是日期类型，也可以是 TIMESTAMP 类型。value 是内容为日期/时间的字符串。
日期
在下面的表中列出了一些允许使用的日期输入格式，所有这些格式的值都等同于 1999 年 1 月 8 号。
| 示例 |
|---|
| January 8, 1999 |
| 1999-01-08 |
| 1999-Jan-08 |
| Jan-08-1999 |
| 08-Jan-1999 |
| 08-Jan-99 |
| Jan-08-99 |
| 19990108 |
| 990108 |
我们可以把日期类型值分配给数据类型为 DATE 或者 TIMESTAMP 的列或者变量。如果日期类型的值不附带时间类型值，那么小时，分钟和秒数的字段值都是零。
CREATE TABLE tb(id int, dt date);
INSERT INTO tb VALUES(1,'January 8, 1999');
INSERT INTO tb VALUES(2,'1999-01-08');
INSERT INTO tb VALUES(3,'1999-Jan-08');
INSERT INTO tb VALUES(4,'Jan-08-1999');
INSERT INTO tb VALUES(5,'08-Jan-1999');
INSERT INTO tb VALUES(6,'08-Jan-99');
INSERT INTO tb VALUES(7,'Jan-08-99');
INSERT INTO tb VALUES(8,'19990108');
INSERT INTO tb VALUES(9,'990108');
antdb=# SELECT * FROM tb;
ID | DT
----+---------------------
1 | 1999-01-08 00:00:00
2 | 1999-01-08 00:00:00
3 | 1999-01-08 00:00:00
4 | 1999-01-08 00:00:00
5 | 1999-01-08 00:00:00
6 | 1999-01-08 00:00:00
7 | 1999-01-08 00:00:00
8 | 1999-01-08 00:00:00
9 | 1999-01-08 00:00:00
(9 rows)
时间
在下面的表中显示了一些日期或者时间戳类型值中带有时间部分的示例。
| 示例 | 描述 |
|---|---|
| 04:05:06.789 | ISO 8601 |
| 04:05:06 | ISO 8601 |
| 04:05 | ISO 8601 |
| 040506 | ISO 8601 |
| 04:05 AM | 和 04:05 的含义一样， AM 不影响实际的值 |
| 04:05 PM | 和 16:05 一样; 输入的小时值不能大于 12 |
CREATE TABLE tb(id int, ts timestamp(6));
INSERT INTO tb VALUES(1,'1999-01-08 04:05:06.789');
INSERT INTO tb VALUES(2,'1999-Jan-08 04:05');
INSERT INTO tb VALUES(3,'January 8, 1999 040506');
INSERT INTO tb VALUES(4,'19990108 04:05 AM');
INSERT INTO tb VALUES(5,'08-Jan-99 04:05 PM');
antdb=# SELECT * FROM tb;
ID | TS
----+-------------------------
1 | 1999-01-08 04:05:06.789
2 | 1999-01-08 04:05:00
3 | 1999-01-08 04:05:06
4 | 1999-01-08 04:05:00
5 | 1999-01-08 16:05:00
(5 rows)
时间戳
一个有效的时间戳类型输入值是由一个日期和一个时间类型值组成。时间戳中日期部分和时间部分能够进行格式化。
下面这个关于时间戳的示例，使用了 Oracle 的缺省格式。
08-JAN-99 04:05:06
下面这个关于时间戳的示例，遵循了 ISO8601 标准的格式。
1999-01-08 04:05:06
时间间隔
Interval 是指两个日期或时间之前的间隔。
- INTERVAL YEAR TO MONTH：
格式：
INTERVAL 'integer [- integer ]' { YEAR | MONTH } [ TO { YEAR | MONTH } ]
INTERVAL 'integer [- integer ]' { YEAR | MONTH } [ (precision) ]
其中 precision 是指精度，在 AntDB 中不会实际限制精度，只是语法上支持。在 Oracle 中，如果指定的 precision 太小会报错，但是 AntDB 不会报错，仍然按照实际的精度显示。
示例：
antdb=# SELECT INTERVAL '100' year(2) FROM dual;
INTERVAL
------------------
100 years
(1 row)
- INTERVAL DAY TO SECOND
格式：
INTERVAL '{ integer | integer time_expr | time_expr }'
{{ DAY | HOUR | MINUTE | SECOND }
[ TO { DAY | HOUR | MINUTE | SECOND [ (fractional_seconds_precision) ] } ]
INTERVAL '{ integer | integer time_expr | time_expr }'
{ { DAY | HOUR | MINUTE | SECOND} [ (leading_precision) ] }
其中 precision 是指精度，在 AntDB 中不会实际限制精度，只是语法上支持。在 Oracle 中，如果指定的 precision 太小会报错，但是 AntDB 不会报错，仍然按照实际的精度显示。且秒的精度只能精确到 6 位。如果指定秒的精度大于 6 位，则会转化为 6。
示例：
SELECT INTERVAL '4 5:12:10.222' DAY TO SECOND(3) FROM dual;
SELECT INTERVAL '4 5:12' DAY TO MINUTE FROM dual;
SELECT INTERVAL '400' DAY(3) FROM dual;
SELECT INTERVAL '11:12:10.2222222' HOUR TO SECOND(6) FROM dual;
SELECT INTERVAL '11:20' HOUR TO MINUTE FROM dual;
SELECT INTERVAL '10' HOUR FROM dual;
SELECT INTERVAL '10:22' MINUTE TO SECOND FROM dual;
SELECT INTERVAL '10' MINUTE FROM dual;
SELECT INTERVAL '4' DAY FROM dual;
SELECT INTERVAL '25' HOUR FROM dual;
SELECT INTERVAL '40' MINUTE FROM dual;
SELECT INTERVAL '120' HOUR(3) FROM dual;
SELECT INTERVAL '20' DAY - INTERVAL '240' HOUR = INTERVAL '10-0' DAY TO SECOND FROM dual;
与 Oracle 相比，还有支持不全的地方，例如，不支持以下格式：
SELECT INTERVAL '400 5' DAY(3) TO HOUR FROM dual;
SELECT INTERVAL '30.12345' SECOND(2,4) FROM dual;
SELECT INTERVAL '10:22' MINUTE(1) TO SECOND FROM dual;
日期/时间输出格式
日期/时间类型的缺省输出格式是 ISO8601 格式 yyyy-mm-dd。在 Oracle 中，默认的输出格式是被称为 Redwood 日期的格式(dd-MON-yy)。但是在 AntDB 中，如果想要输出这样的日期格式，需要使用 to_char 函数来格式化输出。
SELECT to_char(sysdate,'DD-MON-MM hh24:mi:ss') FROM dual;
TO_CHAR(SYSDATE,'DD-MON-MM HH24:MI:SS')
------------------------------------------
03-SEP-09 15:51:47
(1 row)
在下面的表中显示了关于 Redwood 和 ISO8601 这两种风格输出格式的示例。
| 描述 | 示例 |
|---|---|
| Redwood 风格 | 31-DEC-05 07:37:16 |
| ISO 8601/SQL 标准 | 1997-12-17 07:37:16 |
布尔类型
AntDB 提供标准 SQL 类型 BOOLEAN。BOOLEAN 只能具备两种状态中的一种：“true”或是“false”。而第三种状态"未知"是由 SQL 语言中的空值表示。
| 名称 | 存储空间大小 | 描述 |
|---|---|---|
| BOOLEAN | 1字节 | 逻辑布尔类型(真/假) |
代表“true”状态的有效值是 TRUE， 代表“false”状态的有效值是 FALSE。