GaussDB数据类型详解：从基础到高级应用（上）

GaussDB数据类型详解：从基础到高级应用
数据类型是数据库设计的基础要素，直接影响存储效率、查询性能和数据完整性。GaussDB作为华为云关系型数据库服务，支持丰富的数据类型体系，涵盖数值、文本、日期、JSON、空间数据等类别。本文将系统解析GaussDB数据类型的核心特性，并通过真实场景示例展示最佳实践。

一、基础数据类型分类

1. 数值类型
   类型名称 存储空间 取值范围 典型场景
   ​​INT4/INTEGER​​ 4字节 -2,147,483,648 ~ 2,147,483,647 用户年龄、订单数量
   ​​INT8/BIGINT​​ 8字节 -9,223,372,036,854,775,808 ~ ... 身份证号、金融交易流水号
   ​​NUMERIC(p,s)​​ 可变 精度p（最多131072位） 高精度计算（如税务计算）
   ​​FLOAT4/REAL​​ 4字节 ±1.175494e-38 ~ ±3.402823e+38 科学测量数据
   ​​FLOAT8/DOUBLE​​ 8字节 ±2.2250738585072014e-308 ~ ... 地理坐标计算
   ​​示例：数值类型应用​​

-- 存储精确货币值
CREATE TABLE financial (
    account_id INT,
    balance NUMERIC(15,2)  -- 总位数15，小数位2
);

-- 高精度科学计算
INSERT INTO research_data VALUES (1, 3.14159265358979323846);

2. 文本类型
   类型名称 最大长度 特性 典型场景
   ​​VARCHAR(n)​​ 可变（最大1GB） 可指定最大长度，超长截断 用户地址、产品描述
   ​​TEXT​​ 可变（无限制） 存储任意长度文本 日志记录、富文本内容
   ​​CHAR(n)​​ 固定长度 不足补空格，适合固定格式数据 国家代码（CN/US）、状态码
   ​​BYTEA​​ 可变 二进制数据存储 文件内容、图像存储
   ​​示例：文本处理技巧​​

-- 存储JSON格式的用户配置
CREATE TABLE user_settings (
    user_id INT,
    config JSONB
);

-- 使用CHAR(2)存储ISO国家代码
INSERT INTO country_codes VALUES (1, 'CN'), (2, 'US');

-- 字符串模式匹配
SELECT * FROM products 
WHERE product_name LIKE '%智能%手机%';

3. 日期时间类型
   类型名称 存储空间 精度 典型场景
   ​​DATE​​ 4字节 年-月-日 生日、订单日期
   ​​TIME​​ 8字节 时:分:秒.微秒 事件发生时间
   ​​TIMESTAMP​​ 8字节 日期+时间（无时区） 系统日志时间戳
   ​​TIMESTAMP WITH TIME ZONE​​ 8字节 带时区的时间 跨时区业务系统
   ​​INTERVAL​​ 可变 时间间隔 工期计算、定时任务
   ​​示例：时区处理​​

-- 存储带时区的时间
CREATE TABLE events (
    event_id INT,
    start_time TIMESTAMPTZ
);

-- 计算时间间隔
SELECT 
    event_id,
    EXTRACT(EPOCH FROM (end_time - start_time)) / 3600 AS duration_hours
FROM events;

二、高级数据类型应用

1. JSON数据类型

-- 创建支持JSON操作的表
CREATE TABLE product_catalog (
    product_id SERIAL PRIMARY KEY,
    details JSONB
);

-- 插入JSON数据
INSERT INTO product_catalog (details) 
VALUES ('{
    "name": "智能手表",
    "specs": {"cpu": "A12", "ram": "8GB"},
    "variants": ["黑色", "银色"]
}');

-- JSON查询操作
SELECT 
    details->>'name' AS product_name,
    details->'specs'->>'cpu' AS processor
FROM product_catalog
WHERE details @> '{"specs": {"cpu": "A12"}}';

2. 数组类型

-- 存储多值属性
CREATE TABLE student_scores (
    student_id INT,
    subjects TEXT[],
    scores INT[]
);

-- 数组操作
SELECT 
    student_id,
    subjects[1] AS main_subject,
    scores[1] AS main_score
FROM student_scores;

-- 数组聚合
SELECT 
    student_id,
    array_agg(subjects) FILTER (WHERE scores > 80) AS passed_subjects
FROM student_scores
GROUP BY student_id;

3. 空间数据类型（PostGIS扩展）

-- 创建地理信息表
CREATE TABLE locations (
    place_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geog GEOGRAPHY(Point,4326)
);

-- 空间查询
SELECT 
    name,
    ST_Distance(
        geog,
        ST_MakePoint(114.05, 22.55)::GEOGRAPHY
    ) AS distance_from_shenzhen
FROM locations
WHERE ST_DWithin(
    geog,
    ST_MakePoint(114.05, 22.55)::GEOGRAPHY,
    10000  -- 10公里范围内
);

三、特殊类型与最佳实践

1. UUID类型

-- 生成全局唯一标识
CREATE TABLE sessions (
    session_id UUID DEFAULT gen_random_uuid(),
    user_id INT,
    created_at TIMESTAMPTZ
);

-- 查询示例
SELECT * FROM sessions 
WHERE session_id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';

2. 网络地址类型
   -- 存储IP地址
   CREATE TABLE network_logs (
   log_id INT,
   client_ip INET, -- 支持IPv4/IPv6
   subnet CIDR -- 子网掩码
   );

-- IP地址计算
SELECT
client_ip,
host(client_ip) AS ip_str,
text(subnet) AS subnet_mask
FROM network_logs;