16 SQL语言基础之5.2 数据类型

在上一节，我们讨论了数据定义语言（DDL）和数据操作语言（DML），了解了如何创建和管理数据库对象。在本节中，我们将深入探索 PostgreSQL 中的 数据类型，它们是处理数据的基础。在设计数据库时，选择适当的数据类型非常重要，因为它们直接影响到数据的存储效率、查询性能以及数据的完整性。

1. 数据类型概述

PostgreSQL 支持多种数据类型，可以大致分为以下几类：

• 数值类型：用于存储数字，如整数和浮点数。
• 字符类型：用于存储文本，如字符和字符串。
• 时间日期类型：用于存储日期和时间。
• 布尔类型：用于存储逻辑值，true 或 false。
• 复合类型：可以存储多个值的类型，如数组和 JSON。
• 几何类型：支持地理信息系统 (GIS) 数据的存储。

在选择数据类型时，我们应考虑存储空间、数据的有效范围以及操作的复杂性。

2. 数值类型

PostgreSQL 提供几种数值类型，主要包括：

• 整数类型：
  • smallint：2 字节，范围从 -32768 到 32767。
  • integer 或 int：4 字节，范围从 -2147483648 到 2147483647。
  • bigint：8 字节，范围从 -9223372036854775808 到 9223372036854775807。

CREATE TABLE employees (
    id serial PRIMARY KEY,
    name VARCHAR(100),
    age integer
);

在上面的示例中，age 列使用了 integer 类型，以存储员工的年龄。

• 浮点数类型：
  • real：4 字节的单精度浮点数。
  • double precision 或 float8：8 字节的双精度浮点数。
  • numeric：可以指定精度和小数位数，适合高精度需求。

CREATE TABLE products (
    id serial PRIMARY KEY,
    name VARCHAR(100),
    price numeric(10, 2)  -- 10 位数字，其中 2 位为小数
);

3. 字符类型

PostgreSQL 中的字符类型包括：

• CHAR(n)：固定长度的字符类型，若实际字符少于 n，将会用空格填充。
• VARCHAR(n)：可变长度的字符类型，最多 n 个字符，实际字符数少于 n 时不会填充空格。
• TEXT：可存储任意长度的文本。

CREATE TABLE messages (
    id serial PRIMARY KEY,
    content TEXT
);

在这个示例中，content 列使用了 TEXT 类型，以存储不定长度的消息内容。

4. 日期和时间类型

PostgreSQL 提供如下日期和时间类型：

• DATE：只存储日期（年、月、日）。
• TIME：只存储时间（时、分、秒）。
• TIMESTAMP：同时存储日期和时间，支持时区的 TIMESTAMP WITH TIME ZONE。

CREATE TABLE events (
    id serial PRIMARY KEY,
    event_name VARCHAR(100),
    event_date TIMESTAMP
);

5. 布尔类型

布尔类型用于存储逻辑值，可以是 true、false 或 null。

CREATE TABLE tasks (
    id serial PRIMARY KEY,
    task_name VARCHAR(100),
    completed BOOLEAN DEFAULT false
);

这里的 completed 列将默认设置为 false，表示任务未完成。

6. 复合类型和特殊类型

• 数组：可以在 PostgreSQL 中使用数组类型来存储多个相同类型的值。

CREATE TABLE schedules (
    id serial PRIMARY KEY,
    days_of_week VARCHAR(7)[]
);

• JSON 和 JSONB：用于存储 JSON 数据。JSONB 比 JSON 更高效，因为其采用二进制格式存储。

CREATE TABLE api_responses (
    id serial PRIMARY KEY,
    response_data JSONB
);

7. 数据类型选择的最佳实践

在选择数据类型时，考虑以下几点：

1. 数据的意义：选择能准确表达数据本质的类型，如使用 DATE 储存日期。
2. 存储效率：尽量使用尽可能小的数据类型，如用 smallint 替代 int，如果值的范围允许。
3. 性能考虑：某些数据类型在查询时可能会影响性能，应根据实际需求进行选择。

通过合理的数据类型选择，可以提高 PostgreSQL 数据库的性能与可维护性。

总结

在这一节中，我们深入了解了 PostgreSQL 不同类型的数据及其特性，并通过实例加深了理解。在设计数据表时，选择合适的数据类型是至关重要的一步。接下来，我们将在下一节中学习如何使用 SQL 职责创建表的基本语法，以便将这些数据类型应用于具体的数据库设计中。