06_1_数据库中数据类型

数据库中数据类型

1. 数据类型的基本概念

1.1 什么是数据类型

数据类型是数据库中对数据的分类，用于定义列中可以存储的数据的类型和范围。数据类型决定了： - 数据的存储格式 - 可以对数据执行的操作 - 数据的有效范围 - 数据的默认值

1.2 数据类型的作用

数据类型的主要作用包括： - 约束数据：确保数据的合法性和一致性 - 优化存储：根据数据类型选择合适的存储方式，节省存储空间 - 提高性能：优化查询和数据操作的性能 - 保证数据完整性：防止无效数据的插入 - 便于数据处理：为不同类型的数据提供相应的操作函数

1.3 数据类型的分类

数据库中的数据类型通常可以分为以下几类： - 数值类型 - 字符类型 - 日期/时间类型 - 布尔类型 - 枚举类型 - 数组类型 - 范围类型 - JSON类型 - 几何类型 - 网络地址类型 - 文本搜索类型 - UUID类型 - 二进制类型

2. openGauss支持的数据类型

openGauss数据库支持丰富的数据类型，以下是常用的数据类型分类和详细说明。

2.1 数值类型

数值类型用于存储数值数据，包括整数类型、浮点数类型和 decimal/numeric 类型。

2.1.1 整数类型

数据类型	存储大小	最小值	最大值	描述
`SMALLINT`	2字节	-32768	32767	小范围整数
`INT` 或 `INTEGER`	4字节	-2147483648	2147483647	常用整数
`BIGINT`	8字节	-9223372036854775808	9223372036854775807	大范围整数

示例：

2.1.2 浮点数类型

数据类型	存储大小	精度	描述
`REAL`	4字节	6位小数	单精度浮点数
`DOUBLE PRECISION`	8字节	15位小数	双精度浮点数

示例：

2.1.3 精确数值类型

DECIMAL 和 NUMERIC 类型用于存储精确的十进制数值，适合需要精确计算的场景，如货币计算。

数据类型	存储大小	描述
`DECIMAL(p, s)` 或 `NUMERIC(p, s)`	可变	p为精度（总位数），s为小数位数

示例：

2.2 字符类型

字符类型用于存储字符串数据，包括固定长度和可变长度的字符类型。

2.2.1 常用字符类型

数据类型	存储大小	描述
`CHAR(n)` 或 `CHARACTER(n)`	n字节	固定长度字符串，不足n字节则用空格填充
`VARCHAR(n)` 或 `CHARACTER VARYING(n)`	可变，最大n字节	可变长度字符串，最大长度为n
`TEXT`	可变，最大1GB	可变长度字符串，无指定最大长度

示例：

2.2.2 特殊字符类型

NAME：用于存储对象名称，最大长度为63字节

BPCHAR：空白填充字符类型，与CHAR类似

2.3 日期/时间类型

日期/时间类型用于存储日期和时间数据。

2.3.1 常用日期/时间类型

数据类型	存储大小	最小值	最大值	描述
`DATE`	4字节	4713 BC	5874897 AD	日期，格式为YYYY-MM-DD
`TIME`	8字节	00:00:00	23:59:59.999999999	时间，格式为HH:MI:SS
`TIMESTAMP`	8字节	4713 BC	5874897 AD	日期和时间，格式为YYYY-MM-DD HH:MI:SS
`TIMESTAMPTZ` 或 `TIMESTAMP WITH TIME ZONE`	8字节	4713 BC	5874897 AD	带时区的日期和时间
`INTERVAL`	16字节	-178000000年	178000000年	时间间隔

示例：

2.3.2 日期/时间函数

openGauss提供了丰富的日期/时间函数，用于处理日期和时间数据：

CURRENT_DATE：返回当前日期

CURRENT_TIME：返回当前时间

CURRENT_TIMESTAMP：返回当前日期和时间

EXTRACT(field FROM source)：从日期/时间中提取指定字段

TO_CHAR(timestamp, format)：将日期/时间转换为字符串

TO_DATE(string, format)：将字符串转换为日期

示例：

2.4 布尔类型

布尔类型用于存储逻辑值，只有TRUE和FALSE两个值。

数据类型	存储大小	描述
`BOOLEAN` 或 `BOOL`	1字节	布尔值，只能是TRUE或FALSE

示例：

2.5 枚举类型

枚举类型用于存储预定义的枚举值，只能从预定义的值中选择一个。

2.5.1 创建枚举类型

示例：

2.6 数组类型

数组类型用于存储相同数据类型的多个值。

2.6.1 声明数组类型

示例：

2.6.2 数组操作函数

openGauss提供了丰富的数组操作函数：

ARRAY[value1, value2, ...]：创建数组

unnest(array)：将数组展开为多行

array_agg(expression)：将多行数据聚合为数组

array_length(array, dimension)：返回数组指定维度的长度

array_cat(array1, array2)：连接两个数组

2.7 范围类型

范围类型用于存储连续的数值范围，如日期范围、数值范围等。

2.7.1 内置范围类型

数据类型	描述
`INT4RANGE`	整数范围
`INT8RANGE`	大整数范围
`NUMRANGE`	数值范围
`TSRANGE`	时间戳范围
`TSTZRANGE`	带时区的时间戳范围
`DATERANGE`	日期范围

示例：

2.7.2 范围操作符

操作符	描述	示例
`=`	范围相等	`[1,5]` = `[1,5]`
`<>`	范围不相等	`[1,5]` <> `[2,6]`
`<`	范围小于	`[1,5]` < `[6,10]`
`>`	范围大于	`[6,10]` > `[1,5]`
`@>`	范围包含元素或范围	`[1,10]` @> `5`
`<@`	元素或范围被包含	`5` <@ `[1,10]`
`&&`	范围重叠	`[1,5]` && `[3,7]`
`<->`	范围距离	`[1,5]` <-> `[6,10]`

2.8 JSON类型

JSON类型用于存储JSON（JavaScript Object Notation）数据，包括JSON和JSONB两种类型。

数据类型	描述	特点
`JSON`	文本JSON	存储原始JSON文本，查询时解析
`JSONB`	二进制JSON	存储二进制格式的JSON，查询效率更高

示例：

2.9 几何类型

几何类型用于存储几何数据，如点、线、多边形等。

2.9.1 常用几何类型

数据类型	描述
`POINT`	平面上的点
`LINE`	无限长的直线
`LSEG`	线段
`BOX`	矩形
`PATH`	路径
`POLYGON`	多边形
`CIRCLE`	圆

示例：

2.10 网络地址类型

网络地址类型用于存储网络地址，如IP地址。

2.10.1 常用网络地址类型

数据类型	描述
`CIDR`	无类域间路由地址
`INET`	IP地址和子网掩码
`MACADDR`	MAC地址

示例：

2.11 UUID类型

UUID（Universally Unique Identifier）类型用于存储全局唯一标识符。

数据类型	存储大小	描述
`UUID`	16字节	全局唯一标识符

示例：

2.12 二进制类型

二进制类型用于存储二进制数据，如图片、音频、视频等。

2.12.1 常用二进制类型

数据类型	存储大小	描述
`BYTEA`	可变，最大1GB	二进制数据

示例：

3. 数据类型转换

openGauss数据库支持显式和隐式的数据类型转换。

3.1 隐式转换

隐式转换是指数据库自动将一种数据类型转换为另一种数据类型，不需要用户显式指定。

示例：

3.2 显式转换

显式转换是指用户使用转换函数或操作符显式地将一种数据类型转换为另一种数据类型。

3.2.1 使用`CAST`函数

示例：

3.2.2 使用`::`操作符

示例：

4. 数据类型的选择原则

选择合适的数据类型对于数据库的性能和数据完整性至关重要，以下是一些选择数据类型的原则：

4.1 最小化存储大小

选择最小的合适数据类型，以减少存储空间和提高查询性能。例如： - 如果整数范围在-32768到32767之间，使用SMALLINT而不是INT - 如果字符串长度固定，使用CHAR(n)而不是VARCHAR(n) - 如果字符串长度不固定且较长，使用TEXT而不是VARCHAR(n)

4.2 保持数据完整性

选择能够确保数据完整性的数据类型，例如： - 使用DATE类型存储日期，而不是VARCHAR类型 - 使用BOOLEAN类型存储布尔值，而不是INT类型 - 使用ENUM类型存储预定义的值，而不是VARCHAR类型

4.3 考虑查询性能

选择有利于查询性能的数据类型，例如： - 使用JSONB类型存储JSON数据，而不是JSON类型 - 使用UUID类型作为主键时，考虑使用顺序UUID以提高索引性能 - 对于频繁查询的列，选择能够高效比较和排序的数据类型

4.4 考虑数据的可扩展性

选择具有足够扩展性的数据类型，以适应未来数据的增长，例如： - 使用BIGINT类型存储可能会增长到很大的整数，而不是INT类型 - 使用VARCHAR(n)或TEXT类型存储可能会变长的字符串，而不是CHAR(n)类型

4.5 考虑应用程序的兼容性

选择与应用程序兼容的数据类型，例如： - 考虑应用程序语言支持的数据类型 - 考虑ORM框架支持的数据类型

5. 示例：综合使用不同的数据类型

6. 小结

本章节介绍了openGauss数据库支持的各种数据类型，包括数值类型、字符类型、日期/时间类型、布尔类型、枚举类型、数组类型、范围类型、JSON类型、几何类型、网络地址类型、UUID类型和二进制类型。通过本章节的学习，读者可以了解各种数据类型的特点、用法和适用场景，掌握数据类型转换的方法，以及选择合适数据类型的原则。

选择合适的数据类型对于数据库的性能、存储空间和数据完整性至关重要，数据库设计人员和开发人员需要根据实际需求选择合适的数据类型。在实际工作中，应遵循最小化存储大小、保持数据完整性、考虑查询性能、考虑数据的可扩展性和应用程序兼容性等原则，选择最适合的数据类型。