数据表设计

一、数据库三大范式

1.1 第一范式（1NF）

要求：每个字段都是不可分割的原子值。

反例：

sql

-- 违反1NF：address 包含多个值
CREATE TABLE student_bad (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    address VARCHAR(200)  -- 存储 "北京市海淀区中关村大街1号"
);

正例：

sql

-- 符合1NF：拆分到最小粒度
CREATE TABLE student_good (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    province VARCHAR(20),
    city VARCHAR(20),
    district VARCHAR(20),
    detail_address VARCHAR(100)
);

1.2 第二范式（2NF）

要求：满足1NF，且每个非主键列完全依赖于全部主键（消除部分依赖）。

反例：

sql

-- 违反2NF：主键是 (order_id, product_id)，但 product_name 只依赖 product_id
CREATE TABLE order_detail_bad (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),  -- 部分依赖：只依赖 product_id
    quantity INT,
    price DECIMAL(10,2),
    PRIMARY KEY (order_id, product_id)
);

正例：拆成两张表

sql

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

1.3 第三范式（3NF）

要求：满足2NF，且非主键列不传递依赖于主键。

反例：

sql

-- 违反3NF：dept_name 传递依赖于 dept_id，而 dept_id 依赖于主键 id
CREATE TABLE employee_bad (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT,
    dept_name VARCHAR(50),  -- 传递依赖
    dept_location VARCHAR(100) -- 传递依赖
);

正例：拆成员工表和部门表

sql

CREATE TABLE department (
    id INT PRIMARY KEY,
    dept_name VARCHAR(50),
    location VARCHAR(100)
);

-- employee 中只需保留 dept_id
CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES department(id)
);

1.4 反范式设计

何时反范式：查询性能是瓶颈，且通过冗余字段能减少 JOIN 时。

sql

-- 反范式例子：在 orders 表中冗余 user_name
-- 虽然违反3NF，但避免了频繁 JOIN users 表
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    user_name VARCHAR(50),   -- 冗余字段
    amount DECIMAL(12,2),
    order_date DATETIME,
    -- 通过定时任务或业务逻辑保证一致性
    INDEX idx_user_id (user_id)
);

反范式原则：

冗余可控：冗余字段变更频率低（如用户名不常改）
一致性保证：通过业务代码或触发器同步冗余字段
收益大于成本：显著减少高频查询的JOIN

二、约束

2.1 主键约束（PRIMARY KEY）

sql

-- 单字段主键
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

-- 联合主键
CREATE TABLE course_score (
    student_id INT,
    course_id INT,
    score DECIMAL(5,2),
    PRIMARY KEY (student_id, course_id)
);

2.2 唯一约束（UNIQUE）

sql

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    phone VARCHAR(20) UNIQUE,         -- 手机号唯一
    email VARCHAR(100),
    UNIQUE KEY uk_email (email)       -- 指定约束名
);

2.3 非空与默认值

sql

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,           -- 不能为空
    status TINYINT NOT NULL DEFAULT 1,    -- 默认值 1（启用）
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

2.4 外键约束（FOREIGN KEY）

sql

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(12,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE RESTRICT       -- 删除用户时阻止
        ON UPDATE CASCADE        -- 更新用户ID时级联
);

生产建议：互联网高并发场景下不使用外键，外键约束由应用层保证，避免数据库层面的锁开销和性能瓶颈。

三、字段选型与命名规范

3.1 字段类型选择原则

类型	适用场景	不推荐场景
INT/BIGINT	整数ID、数量、状态	金额（用DECIMAL）
VARCHAR	变长字符串	定长内容（用CHAR）
CHAR	固定长度（手机号、身份证）	长文本
DECIMAL	金额、精确小数	浮点运算（用DECIMAL避免精度丢失）
DATETIME	日期时间	时间戳（INT存储）不够直观
TINYINT	状态标记（0/1）	多值状态（用SMALLINT）

3.2 字段类型选型细节

sql

-- 整数类型
TINYINT      -- 1字节，范围 0~255 或 -128~127
SMALLINT     -- 2字节
MEDIUMINT    -- 3字节
INT          -- 4字节
BIGINT       -- 8字节

-- 无符号推荐
id INT UNSIGNED NOT NULL AUTO_INCREMENT  -- 正数场景用 UNSIGNED

-- 金额类型
price DECIMAL(10,2) NOT NULL   -- 10位有效数字，2位小数
-- 如果需要更大范围
amount DECIMAL(18,2) NOT NULL

-- 字符串
VARCHAR(50)   -- 姓名、短名称
VARCHAR(255)  -- 常用最大长度（超过255会影响索引前缀）
TEXT          -- 长文本（不设默认值，无法直接创建全文索引以外的索引）

3.3 字段命名规范

sql

-- 推荐规范
CREATE TABLE order_item (          -- 表名：小写+下划线，单数
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    order_id      BIGINT UNSIGNED NOT NULL COMMENT '订单ID',
    product_id    BIGINT UNSIGNED NOT NULL COMMENT '商品ID',
    product_name  VARCHAR(200) NOT NULL DEFAULT '' COMMENT '商品名称（快照）',
    product_price DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '商品单价',
    quantity      INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '购买数量',
    total_amount  DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '小计金额',
    create_time   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    is_deleted    TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '逻辑删除：0-未删，1-已删',
    PRIMARY KEY (id),
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细表';

3.4 企业建表规范完整示例

sql

-- ============================================
-- 表名：user
-- 描述：系统用户表
-- 作者：张三
-- 创建时间：2024-01-01
-- ============================================
CREATE TABLE `user` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `username`      VARCHAR(50)     NOT NULL DEFAULT '' COMMENT '用户名',
    `password_hash` VARCHAR(255)    NOT NULL DEFAULT '' COMMENT '密码哈希',
    `phone`         VARCHAR(20)     NOT NULL DEFAULT '' COMMENT '手机号',
    `email`         VARCHAR(100)    NOT NULL DEFAULT '' COMMENT '邮箱',
    `avatar_url`    VARCHAR(500)    NOT NULL DEFAULT '' COMMENT '头像URL',
    `status`        TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态：1-启用，0-禁用',
    `last_login_ip` VARCHAR(45)     NOT NULL DEFAULT '' COMMENT '最后登录IP',
    `last_login_time` DATETIME      DEFAULT NULL COMMENT '最后登录时间',
    `create_time`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `is_deleted`    TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '逻辑删除标记',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_username` (`username`),
    UNIQUE KEY `uk_phone` (`phone`),
    KEY `idx_create_time` (`create_time`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

四、实战案例：电商核心表设计

sql

-- 用户表
CREATE TABLE `user` (
    `id`        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `nickname`  VARCHAR(50)    NOT NULL DEFAULT '',
    `phone`     VARCHAR(20)    NOT NULL DEFAULT '',
    `gender`    TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0-未知，1-男，2-女',
    `birthday`  DATE           DEFAULT NULL,
    `level`     TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '会员等级',
    `create_time` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_phone` (`phone`),
    KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 商品表（反范式：冗余分类名称减少JOIN）
CREATE TABLE `product` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `category_id`   INT UNSIGNED   NOT NULL DEFAULT 0,
    `category_name` VARCHAR(50)    NOT NULL DEFAULT '' COMMENT '冗余分类名',
    `name`          VARCHAR(200)   NOT NULL DEFAULT '',
    `price`         DECIMAL(10,2)  NOT NULL DEFAULT 0.00,
    `stock`         INT UNSIGNED   NOT NULL DEFAULT 0 COMMENT '库存',
    `sales_count`   INT UNSIGNED   NOT NULL DEFAULT 0 COMMENT '销量',
    `status`        TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1-上架，0-下架',
    `create_time`   DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_category_id` (`category_id`),
    KEY `idx_status_sales` (`status`, `sales_count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

-- 订单表
CREATE TABLE `order` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_no`      VARCHAR(32)    NOT NULL DEFAULT '' COMMENT '订单号',
    `user_id`       BIGINT UNSIGNED NOT NULL,
    `total_amount`  DECIMAL(12,2)  NOT NULL DEFAULT 0.00,
    `pay_amount`    DECIMAL(12,2)  NOT NULL DEFAULT 0.00,
    `pay_status`    TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0-待支付，1-已支付，2-已退款',
    `delivery_status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0-待发货，1-已发货，2-已签收',
    `pay_time`      DATETIME       DEFAULT NULL,
    `create_time`   DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_order_no` (`order_no`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- 订单明细表
CREATE TABLE `order_item` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_id`      BIGINT UNSIGNED NOT NULL,
    `product_id`    BIGINT UNSIGNED NOT NULL,
    `product_name`  VARCHAR(200)   NOT NULL DEFAULT '' COMMENT '冗余：商品快照',
    `product_price` DECIMAL(10,2)  NOT NULL DEFAULT 0.00 COMMENT '冗余：下单时价格',
    `quantity`      INT UNSIGNED   NOT NULL DEFAULT 0,
    `subtotal`      DECIMAL(12,2)  NOT NULL DEFAULT 0.00,
    PRIMARY KEY (`id`),
    KEY `idx_order_id` (`order_id`),
    KEY `idx_product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细表';

总结

设计原则	要点
三大范式	1NF 字段原子、2NF 消除部分依赖、3NF 消除传递依赖
反范式设计	减少JOIN、冗余低频变更字段、保证一致性的前提下做
字段选型	能用INT不用VARCHAR、金额用DECIMAL、TEXT谨慎
命名规范	小写+下划线、每个字段加COMMENT、表名单数
企业规范	逻辑删除、create_time/update_time、utf8mb4、索引必要