梧桐数据库数值数据类型的精度剖析

梧桐数据库数值数据类型的精度剖析

梧桐数据库作为新一代云原生分布式数据库，遵循 ANSI-SQL 标准，具有极速执行器，提供 PB 级数据交互式查询能力，提供了丰富的数据类型以满足各种数据存储需求。每种数据类型都有其特定的精度和存储要求，了解这些特性对于设计高效、准确的数据库至关重要。本文将深入剖析梧桐数据库中几种常见数值数据类型的精度特性，并通过具体示例来展示它们的应用场景。

数值类型由 2 字节、4 字节或 8 字节的整数以及 4 字节或 8 字节的浮点数和可选精度的十进制数组成。下表列出了所有可用类型。

名字	存储长度	描述	范围
smallint	2字节	小范围整数	-32768 到 +32767
integer	4字节	常用的整数	-2147483648 到 +2147483647
bigint	8字节	大范围整数	-9223372036854775808 到+9223372036854775807
decimal	可变长度	用户指定的精度，精确	小数点前 131072 位；小数点后 16383位
numeric	可变长度	用户指定的精度，精确	小数点前 131072 位；小数点后 16383位
real	4字节	可变精度，不精确	6 位十进制数字精度
double precision	8字节	可变精度，不精确	15 位十进制数字精度
smallserial	2字节	自增的小范围整数	1 到 32767
serial	4字节	自增整数	1 到 2147483647
bigserial	8字节	自增的大范围整	1 到 9223372036854775807

一、整数类型

smallint，integer 和 bigint 类型存储各种取值范围的整数，即，没有小数部分的数字。存储超出取值范围以外的数值将会报错。常用的类型是 integer，因为它在取值范围、存储空间、性能之间提供了最佳的平衡。一般只有在磁盘空间紧张的时候才使用 smallint。当 integer 的取值范围不够用的时候才使用 bigint，因为前者相当快。bigint 类型或许不能在所有平台上都能使用，因为它依赖于编译器支持八字节整数。在没有此类支持的机器上，bigint 和 integer 表现得一样 (但是它依旧占用存储的八个字节)。SQL 标准只定义了整型 integer(或 int) 和 smallint，bigint 类型，而 int2,int4 和 int8 都是扩展名，这些扩展名也在许多其它符合 SQL 标准的数据库系统中使用。

1.1.1. SMALLINT

• 精度范围：-32768 到 +32767。
• 示例：适用于较小的统计数据，如学校班级的学生人数。

CREATE TABLE ClassTable (  
    ClassID smallint ,  
    StudentCount SMALLINT  -- 班级学生人数  
);

1.1.2. INT/INTEGER

• 精度范围：-2147483648 到 +2147483647。
• 示例：适用于一般业务场景中的商品编号、用户ID等。

CREATE TABLE ProductTable (  
    ProductID INT ,  
    ProductName VARCHAR(255) NOT NULL  
);

1.1.3. BIGINT

• 精度范围：大整数范围，-9223372036854775808 到+9223372036854775807。
• 示例：用于存储特别大的整数值，如网站的总访问量。

CREATE TABLE WebsiteTrafficTable (  
    TrafficDate DATE NOT NULL,  
    TotalVisits BIGINT NOT NULL  -- 网站总访问量  
);

二、任意精度数值类型

2.1 NUMERIC

numeric 类型最多可以存放 1000 个数字的数值并且精准地计算。：一个 numeric 类型的标度 (scale) 是小数部分的位数，精度 (precision) 是全部数据位的数目，也就是小数点两边的位数总和。因此数字 23.5141 的精度为 6 而标度为 4 。你可以认为整数的标度为零。

numeric 列的最大精度和最大标度都是可以配置的。numeric是精度最高的一种数值类型，其精度可以达到小数点前 131072 位，小数点后 16383 位。所以，numeric几乎可以看作是一个任意精度的数字，在科学计算中起着重要作用。

• 定义语法：

NUMERIC(precision, scale)

精度必须为正数，标度可以为零或者正数。另外:

NUMERIC(precision)

选择了标度为 0 。不带任何精度与标度的声明:

NUMERIC

• 精度范围： 小数点前 131072 位；小数点后 16383 位。
• 示例：建议将它用于货币金额和其它要求精确计算的场合。

CREATE TABLE numeric_table (  
    id int,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(5,2)
);

然后插入示例记录：

INSERT INTO numeric_table (id, name, price)
VALUES (1,'Phone',500.215), 
       (2,'Tablet',500.214);

我们声明时 price 的 scale 为 2， 但给的值小数部分为 3 ，因此会四舍五入：

SELECT * FROM numeric_table;

2.2 DECIMAL

类型 decimal 和 numeric 是等效的。两种类型都是 SQL 标准。

三、浮点类型

梧桐数据库浮点数类型有两种：REAL 和 DOUBLE PRECISION。数据类型 real 和 double precision 是不精确的、变精度的数字类型。实际上，这些类型是 IEEE 754 标准二进制浮点数算术 (分别对应单和双精度) 的一般实现，外加下层处理器、操作系统和编译器对它的支持。

在大多数平台上，real 类型的范围是至少 1E-37 到 1E+37，精度至少是 6 位小数。double precision 的范围通常是 1E-307 到 1E+308，精度是至少 15 位数字。太大或者太小的数值都会导致错误。如果输入数据的精度太高，那么将会发生圆整。太接近零的数字，如果无法与零值的表现形式相区分就会产生下溢错误。

3.1. REAL

REAL类型对应float(1)~float(24)。

• 精度范围：6 位十进制数字精度。
• 示例：用于不需要极高精度的科学计算。

CREATE TABLE RealTable (  
    CalculationID INT ,  
    Result REAL NOT NULL  
);

3.2. DOUBLE PRECISION

DOUBLE PRECISION 对应 float(25)~float(53)，未声明精度时将被当作 DOUBLE PRECISION 处理。

• 精度范围：15 位十进制数字精度。
• 示例：用于需要较高精度的工程计算。

CREATE TABLE DoubleTable (  
    CalculationID INT ,  
    mount DOUBLE PRECISION NOT NULL  -- 金额  
);

四、序号类型

序列号（SERIAL）类型包括 smallserial（smallint,short）,serial(int) 和 bigserial(bigint,long long int)，不管是 smallserial,serial 还是 bigserial，其范围都是(1,9223372036854775807)，但是序列号类型其实不是真正的类型，当声明一个字段为序列号类型时其实是创建了一个序列，INSERT 时如果没有给该字段赋值会默认获取对应序列的下一个值。

• 定义语法：

CREATE TABLE tablename (

colname SERIAL

);

等价于声明下面几个语句：

CREATE SEQUENCE tablename_colname_seq;

CREATE TABLE tablename (

colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')

);

ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

• **精度范围：**1 到 9223372036854775807。

• 使用示例：

create table serial_table(a serial,b varchar(2));

从结果中看，声明字段 a 为 serial 类型，会自动创建一个名为 serial_table_a_seq 的序列，INSERT 时缺省为该序列的下一个序列值 nextval。

自动创建的序列如下定义：

 select * from sequence_info('serial_table_a_seq');

序列涉及的函数有：

函 数	返 回 类 型	描 述
currval( regclass )	bigint	获取指定序列最近一次使用 netxval 后的数值，如果没有使用nextval而直接使用 currval 会出错。
lastval()	bigint	返回最近一次用 nextval 获取的任意序列的数值
nextval( regclass )	bigint	递增序列并返回新值
setval( regclass,bigint )	bigint	设置序列的当前数值
setval( regclass,bigint ,boolean )	bigint	设置序列的当前数值以及 is_called 标志，如果为 true 则立即生效，如果为 false，则调用一次 nextval 后才会生效。