数据库设计与SQL规范

工具相关 ·

数据库设计是软件系统架构中最关键的环节之一。一个设计良好的数据库能够支撑系统高效运行数年甚至数十年，而一个设计糟糕的数据库则会在系统规模增长后问题频发，最终不得不进行痛苦的重构。本文全面介绍数据库设计的核心原则、命名规范、数据类型选择、范式设计以及约束管理。

一、命名规范

1.1 数据库和表命名

规范项推荐做法不推荐做法说明
命名风格snake_casecamelCase, PascalCase统一使用下划线分隔
表名单复数使用复数形式使用单数users 而不是 user
前缀使用有意义的模块前缀无意义的t_前缀sys_config 而不是 t_config
保留字避免使用SQL保留字使用order, key等容易引起语法冲突
长度限制控制在30字符以内超长描述性命名便于查询和引用

1.2 字段命名规范

-- 通用字段命名规范

-- 主键
id                    -- 统一使用id作为主键名
user_id               -- 外键使用 表名单数_id 格式

-- 时间字段
created_at            -- 记录创建时间
updated_at            -- 记录更新时间
deleted_at            -- 软删除时间
published_at          -- 发布时间
last_login_at         -- 最后登录时间

-- 状态字段
status                -- 通用状态（active, inactive, suspended）
is_active             -- 布尔型使用is_前缀
is_deleted            -- 软删除标记
is_verified           -- 是否已验证
has_subscription      -- 布尔型使用has_前缀

-- 数量字段
order_count           -- 计数使用_count后缀
total_amount          -- 金额使用amount
max_retry             -- 最大值使用max_前缀
min_score             -- 最小值使用min_前缀

-- 类型字段
user_type             -- 类型使用_type后缀
payment_method        -- 方式/方法使用_method后缀

1.3 索引和约束命名

-- 索引命名规范
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);
CREATE UNIQUE INDEX uk_users_username ON users(username);

-- 外键命名规范
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_user_id
    FOREIGN KEY (user_id) REFERENCES users(id);

-- 检查约束命名规范
ALTER TABLE products
    ADD CONSTRAINT ck_products_price_positive
    CHECK (price > 0);

-- 命名模式总结
-- 索引：idx_表名_列名
-- 唯一索引：uk_表名_列名
-- 外键：fk_表名_关联列
-- 检查约束：ck_表名_描述

二、规范化设计

2.1 三范式回顾

第一范式（1NF）：字段不可再分

-- 不满足1NF
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    phone VARCHAR(100)  -- 存储了多个电话号码："13800138000,13900139000"
);

-- 满足1NF：拆分到关联表
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE user_phones (
    id INT PRIMARY KEY,
    user_id INT,
    phone VARCHAR(20),
    phone_type VARCHAR(10),  -- mobile, home, work
    FOREIGN KEY (user_id) REFERENCES users(id)
);

第二范式（2NF）：消除部分依赖

-- 不满足2NF：order_item描述依赖于product_id（部分依赖于复合主键）
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),  -- 依赖于product_id，不依赖于order_id
    quantity INT,
    unit_price DECIMAL(10,2),
    PRIMARY KEY (order_id, product_id)
);

-- 满足2NF：将部分依赖移到单独的表
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);
-- product_name存储在products表中

第三范式（3NF）：消除传递依赖

-- 不满足3NF：city依赖于zipcode，zipcode依赖于user（传递依赖）
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    zipcode VARCHAR(10),
    city VARCHAR(50),       -- 传递依赖于id（通过zipcode）
    province VARCHAR(50)    -- 传递依赖于id（通过zipcode）
);

-- 满足3NF：将传递依赖提取为独立表
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    zipcode VARCHAR(10),
    FOREIGN KEY (zipcode) REFERENCES areas(zipcode)
);

CREATE TABLE areas (
    zipcode VARCHAR(10) PRIMARY KEY,
    city VARCHAR(50),
    province VARCHAR(50)
);

2.2 反范式的合理使用

在某些高性能场景下，适当的反范式化可以提升查询效率：

-- 反范式示例：冗余存储订单总数和总金额
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    order_count INT DEFAULT 0,      -- 冗余字段：避免每次COUNT
    total_spent DECIMAL(12,2) DEFAULT 0  -- 冗余字段：避免每次SUM
);

-- 下单时更新冗余字段
UPDATE users
SET order_count = order_count + 1,
    total_spent = total_spent + 199.00
WHERE id = 1001;

-- 查询时直接使用，无需JOIN和聚合
SELECT username, order_count, total_spent
FROM users
WHERE id = 1001;
范式化反范式化
数据一致性好需要额外逻辑维护一致性
更新操作简单更新操作需要同步冗余字段
查询需要JOIN查询更加简单高效
存储空间小存储空间增大
适合写多读少适合读多写少

三、数据类型选择

3.1 整数类型

类型存储大小范围适用场景
TINYINT1字节0 ~ 255状态码、年龄、小计数
SMALLINT2字节0 ~ 65535中等计数
MEDIUMINT3字节0 ~ 16777215较大计数
INT4字节0 ~ 4294967295通用整数，大多数主键
BIGINT8字节0 ~ 2^63-1超大计数、分布式ID
-- 根据场景选择整数类型
CREATE TABLE articles (
    id BIGINT PRIMARY KEY,          -- 大表使用BIGINT
    view_count INT DEFAULT 0,       -- 浏览量INT足够
    like_count MEDIUMINT DEFAULT 0, -- 点赞数MEDIUMINT足够
    status TINYINT DEFAULT 1,       -- 状态用TINYINT
    category_id SMALLINT            -- 分类ID用SMALLINT
);

3.2 字符串类型

类型特点适用场景
CHAR(n)固定长度，速度快MD5哈希(32)、国家代码(2)
VARCHAR(n)可变长度，节省空间用户名、邮箱、标题
TEXT大文本，65535字节文章内容、评论
MEDIUMTEXT中等文本，16MB长文章
LONGTEXT大文本，4GB超大文档
-- 合理的字符串类型选择
CREATE TABLE articles (
    id BIGINT PRIMARY KEY,
    title VARCHAR(200),           -- 标题限制合理长度
    slug VARCHAR(200),            -- URL别名
    summary VARCHAR(500),         -- 摘要
    content MEDIUMTEXT,            -- 正文内容
    author_name VARCHAR(100),     -- 作者名
    tags VARCHAR(500),            -- 标签（逗号分隔）
    password_hash CHAR(60),       -- bcrypt哈希固定60字符
    status ENUM('draft','published','archived')  -- 枚举类型
);

3.3 日期时间类型

类型存储范围适用场景
DATE3字节1000-01-01 到 9999-12-31生日、日期
DATETIME8字节1000-01-01 00:00:00 到 9999-12-31 23:59:59通用时间戳
TIMESTAMP4字节1970-01-01 00:00:01 到 2038-01-19记录创建/更新时间
TIME3字节-838:59:59 到 838:59:59持续时间、时刻
YEAR1字节1901 到 2155年份
-- 日期时间类型的选择
CREATE TABLE events (
    id BIGINT PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATE,                -- 只需要日期，不需要时间
    start_time TIME,                -- 开始时刻
    end_time TIME,                  -- 结束时刻
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 创建时间
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    duration_minutes SMALLINT       -- 持续时长用整数分钟表示
);

3.4 金额类型

-- 金额必须使用DECIMAL，绝不使用FLOAT或DOUBLE
-- 错误做法
CREATE TABLE orders_bad (
    total FLOAT,        -- 0.1 + 0.2 = 0.30000000000000004
    tax_rate DOUBLE     -- 精度丢失
);

-- 正确做法
CREATE TABLE orders (
    total DECIMAL(12, 2),    -- 最大9999999999.99
    tax_rate DECIMAL(5, 4),  -- 最大9.9999
    discount DECIMAL(5, 2)   -- 最大999.99
);

-- 更大金额场景
CREATE TABLE transactions (
    amount DECIMAL(20, 2),   -- 支持非常大的金额
    currency CHAR(3)         -- CNY, USD, EUR
);

四、约束设计

4.1 主键设计

-- 方案1：自增主键（最常用）
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

-- 方案2：BIGINT自增（大表推荐）
CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL
);

-- 方案3：UUID主键（分布式系统）
CREATE TABLE sessions (
    id CHAR(36) PRIMARY KEY,  -- UUID格式
    user_id INT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 方案4：雪花算法ID（分布式推荐）
CREATE TABLE distributed_logs (
    id BIGINT PRIMARY KEY,    -- 雪花算法生成的ID
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

4.2 外键约束

-- 外键约束示例
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE RESTRICT    -- 有订单时不允许删除用户
        ON UPDATE CASCADE     -- 用户ID变更时级联更新
);

-- 外键策略选择
-- CASCADE: 父记录删除时，子记录也删除
-- RESTRICT: 有子记录时，阻止删除父记录
-- SET NULL: 父记录删除时，子记录外键设为NULL
-- NO ACTION: 类似RESTRICT

4.3 CHECK约束

-- 数据完整性约束
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock INT NOT NULL CHECK (stock >= 0),
    discount_rate DECIMAL(3, 2) CHECK (discount_rate BETWEEN 0 AND 1),
    status ENUM('active', 'inactive', 'deleted') DEFAULT 'active',
    weight DECIMAL(8, 2) CHECK (weight > 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

五、表结构设计模板

5.1 通用基础字段

每个业务表建议包含以下基础字段：

CREATE TABLE example_table (
    -- 主键
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    -- 业务字段
    -- ... 根据业务需求设计

    -- 状态与标记
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    is_deleted TINYINT(1) NOT NULL DEFAULT 0,

    -- 审计字段
    created_by INT UNSIGNED,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by INT UNSIGNED,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    -- 索引
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_is_deleted (is_deleted)
);

5.2 设计检查清单

检查项要求优先级
每个表有主键必须P0
字段设置NOT NULL强烈建议P0
字段有默认值建议P1
时间字段类型正确TIMESTAMP或DATETIMEP0
金额使用DECIMAL必须P0
外键建立索引必须P0
字符集统一UTF8MB4必须P0
表有适当的注释建议P1
考虑软删除支持根据业务P2
考虑审计字段根据业务P2

六、总结

数据库设计是系统架构的基石。遵循以下核心原则：

  1. 规范化优先：先按范式设计，再根据性能需求适当反范式
  2. 命名要规范：统一的命名规范是团队协作的基础
  3. 数据类型要精确：选择最合适的数据类型，避免过度使用通用类型
  4. 约束不可少：主键、外键、CHECK约束保障数据完整性
  5. 预留扩展性：设计时考虑未来的数据增长和功能扩展

好的数据库设计能够让系统在数据量增长数十倍后依然保持良好的性能和可维护性。

阅读 15

相关文章

  • 为什么 PBKDF2 的输出要自己存参数

    PBKDF2密码哈希输出不包含算法参数，需自行存储迭代次数、盐值等，否则无法校验，与其他自描述哈希算法如Bcrypt不同，推荐使用PHC规范格式存储参数与摘要。

  • 字符数和字节数为什么差三倍

    产品文档中“标题不超过200字”引发开发上线报错，因字符数与字节数计算口径不同，中文标题在UTF-8编码下实际占用空间达三倍，涉及数据库索引长度限制、传输层体积限制、计费配额及前端展示等场景，需明确区分字符数与字节数并规范接口文档。

  • Latin-1 扩展与 UTF-8 字节数估算

    估算文本在 Latin-1 扩展与 UTF-8 编码下的字节数，涉及 ASCII、Latin-1、UTF-8 编码规则及数据库字段设计实践，助开发者解决接口限流与存储问题。

  • Emoji技术原理解析

    Emoji技术原理解析：本文深入探讨Emoji的Unicode编码机制、组合序列原理、平台渲染差异及开发实践，揭示其跨越设备与语言的数字语言技术体系。

  • 请求头大小写问题

    HTTP请求头大小写敏感性问题解析及规范处理方法，避免因大小写差异导致的通信失败，自定义头和代理环境下的常见问题及解决方案

  • 请求头名称规范解析

    本文详细解析HTTP请求头名称规范，涵盖格式要求、常见错误、分类处理及安全注意事项，帮助开发者避免因格式问题导致的请求失败，提升API对接与自定义请求头的开发质量。

  • 特殊符号在代码中的安全使用

    在开发中安全使用特殊符号，了解Unicode编码陷阱、不可见字符风险及符号转义方法，避免乱码注入、逻辑错误与安全漏洞，提升代码兼容性与安全性。

  • 常见大写金额错误与纠正

    本文系统梳理了人民币大写金额书写中最常见的错误类型，包括字符使用、零的处理、单位遗漏及格式问题，并提供了详细的纠正方法和正确写法，帮助避免因金额错误导致的票据退回和合同争议。

  • 人民币大写书写规范与标准

    人民币大写金额书写规范详解，依据国家标准GB/T 15835-2011及《票据法》等法规，涵盖数字字符标准写法、单位字符规范及格式要求，助力合规填写票据与凭证，确保法律效力。本文详细解析壹至玖数字及元角分等单位的正确书写方式，并说明禁止使用易混淆字符，提供完整合规指南。

  • SQL注入防护与代码规范

    本文详细介绍了SQL注入的原理、危害及常见类型，重点讲解了参数化查询等核心防护方法，并提供了安全编码规范，帮助开发者有效防范SQL注入攻击，保障Web应用安全。