数据类型详解

我是长安！今天我们来聊聊PostgreSQL的数据类型。选对数据类型，能让你的数据库更高效、更安全！

🎯 为什么要了解数据类型？

想象一下，你去超市买东西：

  • 🍎 苹果装在塑料袋里（适合水果）
  • 🥛 牛奶装在盒子里（适合液体）
  • 📱 手机装在纸盒里（适合易碎品）

数据也一样！ 不同类型的数据需要不同的"容器"：

  • 年龄用整数（INT）
  • 价格用小数（DECIMAL）
  • 名字用字符串（VARCHAR）

选对类型的好处：

  • ⚡ 节省存储空间
  • 🚀 提高查询速度
  • 🛡️ 防止错误数据
  • ✅ 让代码更清晰

📊 PostgreSQL数据类型大家族

PostgreSQL支持超多数据类型，我把常用的分成几类：

数据类型
├── 数值类型（存数字）
│   ├── 整数（INT, BIGINT...）
│   └── 小数（DECIMAL, FLOAT...）
├── 字符类型（存文字）
│   ├── VARCHAR
│   └── TEXT
├── 日期时间类型
│   ├── DATE
│   ├── TIME
│   └── TIMESTAMP
├── 布尔类型（TRUE/FALSE）
├── 枚举类型
└── 特殊类型（JSON, ARRAY...）

🔢 数值类型

整数类型

类型字节范围适用场景
SMALLINT2-32,768 到 32,767年龄、数量
INTEGER (INT)4-2,147,483,648 到 2,147,483,647通用整数
BIGINT8±9,223,372,036,854,775,807大数据ID
SERIAL4自增，1到2,147,483,647主键ID
BIGSERIAL8自增，大范围大表主键

实战例子

CREATE TABLE products (
    id SERIAL PRIMARY KEY,         -- 主键，自增
    user_id BIGINT NOT NULL,       -- 用户ID，可能很大
    stock SMALLINT DEFAULT 0,      -- 库存，不会太大
    price INT NOT NULL,            -- 价格（单位：分）
    views BIGINT DEFAULT 0         -- 浏览量，可能很大
);

长安的技巧：价格为什么用INT？因为用分作为单位，避免小数精度问题！比如9.99元存成999分。

小数类型

类型说明适用场景
DECIMAL(p, s)精确小数金额、百分比
NUMERIC(p, s)同DECIMAL金额、百分比
REAL单精度浮点数科学计算
DOUBLE PRECISION双精度浮点数科学计算
  • p（precision）：总位数
  • s（scale）：小数位数

实战例子

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    total_amount DECIMAL(10, 2),   -- 总金额：最多10位，2位小数
    -- 比如：12345678.99
    
    discount_rate DECIMAL(5, 4),   -- 折扣率：0.8888
    -- 比如：0.8888表示88.88%折扣
    
    weight DOUBLE PRECISION        -- 重量（科学计算可用）
);

-- 插入示例
INSERT INTO orders (total_amount, discount_rate, weight) 
VALUES (1999.99, 0.8800, 1.5);

选择建议：

  • 💰 金额：用DECIMAL，精确！
  • 📊 百分比：用DECIMAL(5,4)，比如0.8888
  • 🔬 科学计算：用DOUBLE PRECISION

长安的警告：千万别用FLOAT存金额！会有精度误差，比如9.99可能变成9.98999999！

📝 字符类型

常用字符类型

类型说明适用场景
CHAR(n)固定长度省份代码、性别
VARCHAR(n)可变长度名字、标题
TEXT无限制长度文章内容、描述

区别演示

-- CHAR：固定长度，不足会填充空格
CREATE TABLE test_char (
    code CHAR(5)  -- 总是占5个字符
);
INSERT INTO test_char VALUES ('AB');  -- 存储为'AB   '（3个空格）

-- VARCHAR：可变长度，用多少占多少
CREATE TABLE test_varchar (
    name VARCHAR(50)  -- 最多50字符，但只占用实际长度
);
INSERT INTO test_varchar VALUES ('张三');  -- 只占2个字符

-- TEXT：无限制长度
CREATE TABLE test_text (
    content TEXT  -- 可以存储超长文本
);
INSERT INTO test_text VALUES ('这是一篇很长很长的文章...');

实战例子

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,     -- 用户名，限制长度
    email VARCHAR(100) NOT NULL,       -- 邮箱
    password VARCHAR(255) NOT NULL,    -- 密码（加密后可能很长）
    bio TEXT,                          -- 个人简介，长度不限
    gender CHAR(1) CHECK (gender IN ('M', 'F', 'O')),  -- 性别：M/F/O
    country_code CHAR(2)               -- 国家代码：CN, US, JP...
);

长安的建议：

  • 🎯 长度明确的：用VARCHAR(n)，比如用户名、邮箱
  • 📖 可能很长的：用TEXT，比如文章内容、描述
  • 🔢 固定长度的：用CHAR(n)，比如性别代码、国家代码
  • ❌ 别纠结：实际上VARCHAR和TEXT性能差不多，不确定就用TEXT

📅 日期时间类型

类型说明示例
DATE日期2025-12-01
TIME时间14:30:00
TIMESTAMP日期+时间2025-12-01 14:30:00
TIMESTAMPTZ带时区的时间戳2025-12-01 14:30:00+08
INTERVAL时间间隔3 days, 2 hours

实战例子

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    
    -- 只需要日期
    event_date DATE,                   -- 2025-12-01
    
    -- 只需要时间
    start_time TIME,                   -- 14:30:00
    
    -- 需要日期和时间
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 需要时区（推荐！）
    published_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- 插入示例
INSERT INTO events (title, event_date, start_time) 
VALUES ('编程指南分享会', '2025-12-25', '19:00:00');

-- 查询示例
SELECT * FROM events 
WHERE event_date = '2025-12-25';

SELECT * FROM events 
WHERE created_at > NOW() - INTERVAL '7 days';  -- 最近7天

常用日期函数

-- 当前日期时间
SELECT NOW();                    -- 2025-12-01 14:30:00+08
SELECT CURRENT_DATE;             -- 2025-12-01
SELECT CURRENT_TIME;             -- 14:30:00+08
SELECT CURRENT_TIMESTAMP;        -- 2025-12-01 14:30:00+08

-- 日期计算
SELECT NOW() + INTERVAL '1 day';         -- 明天
SELECT NOW() - INTERVAL '1 week';        -- 一周前
SELECT NOW() + INTERVAL '3 hours';       -- 3小时后

-- 提取部分
SELECT EXTRACT(YEAR FROM NOW());         -- 2025
SELECT EXTRACT(MONTH FROM NOW());        -- 12
SELECT DATE_PART('hour', NOW());         -- 14

-- 格式化
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD');     -- '2025-12-01'
SELECT TO_CHAR(NOW(), 'HH24:MI:SS');     -- '14:30:00'

长安的建议：

  • ✅ 用TIMESTAMPTZ 而不是TIMESTAMP，避免时区问题
  • ✅ 自动记录时间：DEFAULT CURRENT_TIMESTAMP
  • ✅ 存储UTC时间，显示时转换成本地时间

✅ 布尔类型

类型说明值
BOOLEAN真/假TRUE, FALSE, NULL

实战例子

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    
    published BOOLEAN DEFAULT FALSE,        -- 是否发布
    is_featured BOOLEAN DEFAULT FALSE,      -- 是否精选
    allow_comments BOOLEAN DEFAULT TRUE,    -- 是否允许评论
    is_deleted BOOLEAN DEFAULT FALSE        -- 是否删除（软删除）
);

-- 插入示例
INSERT INTO posts (title, content, published) 
VALUES ('我的第一篇文章', '内容...', TRUE);

-- 查询示例
SELECT * FROM posts WHERE published = TRUE;
SELECT * FROM posts WHERE published;  -- 简写
SELECT * FROM posts WHERE NOT is_deleted;  -- 未删除的

长安提醒：

  • TRUE的表示：TRUE, 't', 'true', 'y', 'yes', '1'
  • FALSE的表示：FALSE, 'f', 'false', 'n', 'no', '0'

🎨 枚举类型

枚举类型用于限定值的范围。

创建和使用

-- 创建枚举类型
CREATE TYPE user_role AS ENUM ('admin', 'editor', 'user', 'guest');
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'completed', 'cancelled');

-- 使用枚举类型
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    role user_role DEFAULT 'user'  -- 只能是定义的值
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status order_status DEFAULT 'pending'
);

-- 插入数据
INSERT INTO users (username, role) 
VALUES ('长安', 'admin');  -- ✅ OK

INSERT INTO users (username, role) 
VALUES ('小明', 'superadmin');  -- ❌ 错误！没有这个值

-- 查询
SELECT * FROM users WHERE role = 'admin';

优点：

  • ✅ 限制值的范围，防止错误
  • ✅ 代码更清晰
  • ✅ 性能更好（存储为整数）

长安的建议：如果一个字段只能是几个固定值，用枚举！

🎁 特殊类型

JSON类型

PostgreSQL支持存储JSON数据！

CREATE TABLE user_settings (
    user_id INT PRIMARY KEY,
    preferences JSON,      -- 标准JSON
    metadata JSONB         -- 二进制JSON，更快！
);

-- 插入JSON数据
INSERT INTO user_settings VALUES (
    1,
    '{"theme": "dark", "language": "zh-CN"}',
    '{"notifications": true, "email": "changan@email.com"}'
);

-- 查询JSON数据
SELECT preferences->>'theme' AS theme FROM user_settings;
-- 结果：'dark'

SELECT * FROM user_settings 
WHERE metadata->>'notifications' = 'true';

JSON vs JSONB：

  • JSON：存储原始文本，保留格式
  • JSONB：二进制存储，查询更快，推荐用这个！

数组类型

PostgreSQL支持数组！

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    tags TEXT[],           -- 字符串数组
    related_ids INT[]      -- 整数数组
);

-- 插入数组
INSERT INTO posts (title, tags, related_ids) 
VALUES (
    'PostgreSQL教程',
    ARRAY['数据库', 'PostgreSQL', '教程'],
    ARRAY[1, 2, 3]
);

-- 或者用花括号
INSERT INTO posts (title, tags) 
VALUES ('学习笔记', '{"笔记", "学习"}');

-- 查询数组
SELECT * FROM posts WHERE 'PostgreSQL' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['数据库'];  -- 包含
SELECT tags[1] FROM posts;  -- 第一个元素（从1开始！）

UUID类型

UUID是全局唯一标识符，适合分布式系统。

-- 启用UUID扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    username VARCHAR(50)
);

-- 插入（ID自动生成）
INSERT INTO users (username) VALUES ('长安');

-- 查询
SELECT * FROM users;
-- id: 550e8400-e29b-41d4-a716-446655440000

🎯 如何选择数据类型？

决策树

存储什么数据？
│
├─ 数字
│  ├─ 整数 → INT (通用) / BIGINT (很大) / SMALLINT (很小)
│  ├─ 主键 → SERIAL / BIGSERIAL
│  └─ 小数 → DECIMAL (金额) / DOUBLE PRECISION (科学计算)
│
├─ 文字
│  ├─ 固定长度 → CHAR(n)
│  ├─ 有限制长度 → VARCHAR(n)
│  └─ 长文本 → TEXT
│
├─ 日期时间
│  ├─ 只要日期 → DATE
│  ├─ 只要时间 → TIME
│  └─ 日期+时间 → TIMESTAMPTZ (推荐)
│
├─ 真假值 → BOOLEAN
│
├─ 固定几个值 → ENUM
│
└─ 复杂数据
   ├─ JSON数据 → JSONB
   ├─ 数组 → ARRAY
   └─ 唯一ID → UUID

实战案例：电商系统

CREATE TABLE products (
    -- 主键
    id BIGSERIAL PRIMARY KEY,
    uuid UUID DEFAULT uuid_generate_v4(),
    
    -- 基本信息
    name VARCHAR(200) NOT NULL,
    description TEXT,
    category VARCHAR(50),
    
    -- 价格和库存
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    original_price DECIMAL(10, 2),
    stock INT DEFAULT 0 CHECK (stock >= 0),
    
    -- 统计
    sales_count BIGINT DEFAULT 0,
    views BIGINT DEFAULT 0,
    rating DECIMAL(3, 2) CHECK (rating >= 0 AND rating <= 5),
    
    -- 状态
    status VARCHAR(20) DEFAULT 'active',  -- 或用ENUM
    is_featured BOOLEAN DEFAULT FALSE,
    is_on_sale BOOLEAN DEFAULT FALSE,
    
    -- 元数据
    specs JSONB,  -- 商品规格（JSON）
    tags TEXT[],  -- 标签（数组）
    images TEXT[],  -- 图片URL（数组）
    
    -- 时间戳
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    published_at TIMESTAMPTZ
);

🎮 练习题

任务：设计用户注册表

设计一个用户表，包含以下信息：

  • 用户ID（自增）
  • 用户名（最多30字符）
  • 邮箱（最多100字符）
  • 密码（加密后可能很长）
  • 年龄（0-150）
  • 性别（男/女/未知）
  • 手机号（固定11位）
  • 个人简介（不限长度）
  • 账户余额（精确到分）
  • 是否激活
  • 是否VIP会员
  • 兴趣标签（数组）
  • 个人设置（JSON）
  • 注册时间
  • 最后登录时间
点击查看参考答案
CREATE TYPE gender_type AS ENUM ('male', 'female', 'unknown');

CREATE TABLE users (
    -- 主键
    id BIGSERIAL PRIMARY KEY,
    
    -- 基本信息
    username VARCHAR(30) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    
    -- 个人信息
    age SMALLINT CHECK (age >= 0 AND age <= 150),
    gender gender_type DEFAULT 'unknown',
    phone CHAR(11),
    bio TEXT,
    
    -- 财务
    balance DECIMAL(12, 2) DEFAULT 0.00 CHECK (balance >= 0),
    
    -- 状态
    is_active BOOLEAN DEFAULT FALSE,
    is_vip BOOLEAN DEFAULT FALSE,
    
    -- 扩展信息
    interests TEXT[],
    settings JSONB DEFAULT '{}',
    
    -- 时间戳
    registered_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    last_login_at TIMESTAMPTZ
);

📚 下一步

现在你已经掌握了PostgreSQL的数据类型！这是非常重要的基础知识。

接下来，让我们学习增删改查操作，真正开始操作数据！

长安的经验总结：

选择数据类型的原则：

  1. 够用就好：不要过度设计
  2. 留有余地：但也要考虑未来扩展
  3. 性能优先：合适的类型能提升性能
  4. 语义清晰：让别人一看就懂

记住：好的数据类型选择，是数据库设计的基础！ 💪

有问题来编程指南找我！

最近更新: