第四章：表设计

1 数据类型

选择正确的数据类型是表设计的基础。合适的类型不仅节省存储空间，还能提升查询性能并保证数据完整性。MySQL 提供了丰富的数据类型，以下按类别逐一介绍。

整数类型

-- 整数类型示例
CREATE TABLE example_int (
    status    TINYINT UNSIGNED NOT NULL DEFAULT 0,
    age       SMALLINT UNSIGNED,
    user_id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    snowflake BIGINT UNSIGNED NOT NULL
);

浮点数与定点数

-- 金额必须用 DECIMAL，避免浮点精度问题
CREATE TABLE products (
    id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    weight FLOAT
);

-- FLOAT 精度问题演示
SELECT 0.1 + 0.2;           -- 结果: 0.30000000000000004
SELECT CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2)); -- 结果: 0.30

字符串类型

日期时间类型

CREATE TABLE events (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title      VARCHAR(200) NOT NULL,
    event_date DATE NOT NULL,
    start_time TIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- TIMESTAMP 会自动转换时区，DATETIME 不会
-- 推荐：业务时间用 DATETIME，记录操作时间用 TIMESTAMP

其他常用类型

-- BOOLEAN（实际是 TINYINT(1) 的别名）
ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT TRUE;
-- TRUE = 1, FALSE = 0

-- ENUM：枚举类型，值只能是预定义的几个之一
CREATE TABLE articles (
    id     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title  VARCHAR(200) NOT NULL,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft'
);

-- SET：可以存储多个预定义值的组合
CREATE TABLE user_preferences (
    user_id INT UNSIGNED PRIMARY KEY,
    hobbies SET('reading', 'gaming', 'cooking', 'sports', 'music')
);
INSERT INTO user_preferences VALUES (1, 'reading,gaming,music');

-- JSON（MySQL 5.7.8+）
CREATE TABLE configs (
    id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    settings JSON NOT NULL
);
INSERT INTO configs (settings) VALUES ('{"theme": "dark", "lang": "zh"}');
SELECT settings->>'$.theme' AS theme FROM configs;

-- BLOB：二进制大对象（通常建议存文件路径而非文件本身）
CREATE TABLE attachments (
    id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    data MEDIUMBLOB,
    mime VARCHAR(100)
);

2 约束

约束（Constraints）是数据库层面的数据完整性保障。即使应用层有校验，数据库约束是最后一道防线，确保无论数据从哪个入口写入都符合规则。

PRIMARY KEY — 主键

-- 单列主键
CREATE TABLE users (
    id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(100) NOT NULL
);

-- 复合主键（联合主键）
CREATE TABLE order_items (
    order_id   INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    quantity   SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    price      DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

NOT NULL — 非空约束

CREATE TABLE contacts (
    id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(100) NOT NULL,       -- 必填
    email VARCHAR(200) NOT NULL,       -- 必填
    phone VARCHAR(20)                  -- 可选，允许 NULL
);

-- 插入时省略 NOT NULL 字段会报错
INSERT INTO contacts (id) VALUES (1);
-- ERROR 1364: Field 'name' doesn't have a default value

UNIQUE — 唯一约束

CREATE TABLE users (
    id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email    VARCHAR(200) NOT NULL,
    phone    VARCHAR(20),
    UNIQUE KEY uk_email (email),
    UNIQUE KEY uk_phone (phone)
);

-- UNIQUE 列允许多个 NULL（MySQL 中 NULL != NULL）
-- 复合唯一约束
CREATE TABLE enrollments (
    student_id INT UNSIGNED NOT NULL,
    course_id  INT UNSIGNED NOT NULL,
    enrolled_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_student_course (student_id, course_id)
);

DEFAULT — 默认值

CREATE TABLE posts (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title      VARCHAR(200) NOT NULL,
    content    TEXT,
    status     TINYINT UNSIGNED NOT NULL DEFAULT 0,
    view_count INT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CHECK — 检查约束（MySQL 8.0.16+）

CREATE TABLE products (
    id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name     VARCHAR(200) NOT NULL,
    price    DECIMAL(10, 2) NOT NULL,
    stock    INT NOT NULL DEFAULT 0,
    discount DECIMAL(3, 2) DEFAULT 1.00,
    CONSTRAINT chk_price    CHECK (price >= 0),
    CONSTRAINT chk_stock    CHECK (stock >= 0),
    CONSTRAINT chk_discount CHECK (discount BETWEEN 0.00 AND 1.00)
);

-- 违反 CHECK 约束会报错
INSERT INTO products (name, price, stock) VALUES ('测试', -10, 5);
-- ERROR 3819: Check constraint 'chk_price' is violated

FOREIGN KEY — 外键约束

CREATE TABLE users (
    id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE orders (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED NOT NULL,
    total      DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
) ENGINE=InnoDB;

-- ON DELETE 选项：
-- RESTRICT  — 有子记录时禁止删除父记录（默认）
-- CASCADE   — 删除父记录时同步删除所有子记录
-- SET NULL  — 删除父记录时将子记录的外键列设为 NULL
-- NO ACTION — 与 RESTRICT 等价（SQL 标准写法）

-- ON UPDATE 选项同上，控制父记录主键更新时的行为

-- 实际场景示例：用户删除后订单保留但 user_id 设为 NULL
CREATE TABLE orders_v2 (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED,
    total      DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_orders_v2_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
) ENGINE=InnoDB;

3 CREATE TABLE 完整示例

下面设计一个简化的电商数据库，包含商品表、订单表和订单明细表，展示如何综合使用各种数据类型和约束。

商品表 products

CREATE TABLE products (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sku         VARCHAR(50) NOT NULL UNIQUE COMMENT '商品编号',
    name        VARCHAR(200) NOT NULL COMMENT '商品名称',
    description TEXT COMMENT '商品描述',
    price       DECIMAL(10, 2) NOT NULL COMMENT '单价',
    stock       INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '库存数量',
    category    ENUM('electronics', 'clothing', 'food', 'books', 'other') DEFAULT 'other',
    is_active   BOOLEAN NOT NULL DEFAULT TRUE,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT chk_products_price CHECK (price >= 0),
    INDEX idx_category (category),
    INDEX idx_active_category (is_active, category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品表';

订单表 orders

CREATE TABLE orders (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_no     VARCHAR(32) NOT NULL UNIQUE COMMENT '订单号',
    user_id      INT UNSIGNED NOT NULL COMMENT '用户 ID',
    total_amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    status       ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled') NOT NULL DEFAULT 'pending',
    address      VARCHAR(500) NOT NULL COMMENT '收货地址',
    remark       VARCHAR(500) DEFAULT '' COMMENT '备注',
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单表';

订单明细表 order_items

CREATE TABLE order_items (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id   INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    quantity   SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    unit_price DECIMAL(10, 2) NOT NULL COMMENT '下单时的单价快照',
    subtotal   DECIMAL(12, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
    CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_items_product FOREIGN KEY (product_id) REFERENCES products(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT chk_quantity CHECK (quantity > 0),
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细表';

查看建表语句

-- 查看完整的建表 DDL
SHOW CREATE TABLE products\G

-- 查看表结构摘要
DESCRIBE products;
-- 或等价写法
DESC products;
SHOW COLUMNS FROM products;

4 ALTER TABLE 修改表结构

数据库设计不是一成不变的，业务演进常常需要修改表结构。ALTER TABLE 是最常用的 DDL 语句之一。

ADD COLUMN — 添加列

-- 在末尾添加列
ALTER TABLE users ADD COLUMN avatar_url VARCHAR(500) DEFAULT NULL;

-- 在指定列之后添加
ALTER TABLE users ADD COLUMN nickname VARCHAR(50) AFTER name;

-- 在最前面添加
ALTER TABLE users ADD COLUMN uuid CHAR(36) FIRST;

-- 一次添加多列
ALTER TABLE users
    ADD COLUMN bio TEXT,
    ADD COLUMN birthday DATE;

MODIFY COLUMN / CHANGE COLUMN — 修改列

-- MODIFY：修改列的类型或属性（不改名）
ALTER TABLE users MODIFY COLUMN name VARCHAR(200) NOT NULL;

-- CHANGE：同时改列名和属性
ALTER TABLE users CHANGE COLUMN nickname display_name VARCHAR(100) DEFAULT '';

-- 修改默认值
ALTER TABLE products ALTER COLUMN stock SET DEFAULT 0;
ALTER TABLE products ALTER COLUMN category DROP DEFAULT;

DROP COLUMN — 删除列

ALTER TABLE users DROP COLUMN bio;

-- 删除多列
ALTER TABLE users
    DROP COLUMN avatar_url,
    DROP COLUMN birthday;

ADD INDEX — 添加索引

-- 普通索引
ALTER TABLE users ADD INDEX idx_email (email);

-- 唯一索引
ALTER TABLE users ADD UNIQUE INDEX uk_phone (phone);

-- 复合索引
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

-- 删除索引
ALTER TABLE users DROP INDEX idx_email;

ADD / DROP FOREIGN KEY

-- 添加外键
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE RESTRICT;

-- 删除外键（需要知道约束名）
ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;

RENAME TABLE — 重命名表

-- 重命名单个表
RENAME TABLE users TO members;

-- 重命名多个表（原子操作，常用于安全替换表）
RENAME TABLE
    products TO products_old,
    products_new TO products;

5 设计规范建议

命名规范

-- ✅ 推荐：snake_case 小写命名
CREATE TABLE user_addresses (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id      INT UNSIGNED NOT NULL,
    address_line VARCHAR(300) NOT NULL,
    postal_code  VARCHAR(20)
);

-- ❌ 避免：驼峰命名、大写、保留字作表名/列名
-- CREATE TABLE UserAddresses (ID INT, AddressLine VARCHAR(300));
-- CREATE TABLE `order` (`select` VARCHAR(100));  -- 虽然可以用反引号，但不推荐

• 表名用复数名词：users、orders、products
• 外键列名格式：关联表单数_id，如 user_id、product_id
• 索引名前缀：普通索引 idx_，唯一索引 uk_，外键 fk_
• 布尔列用 is_ 或 has_ 前缀：is_active、has_paid

必备设计原则

数据库范式（简要）

-- ❌ 违反 3NF：订单表中冗余了用户名
CREATE TABLE bad_orders (
    id         INT UNSIGNED PRIMARY KEY,
    user_id    INT UNSIGNED NOT NULL,
    user_name  VARCHAR(100),  -- 冗余！应从 users 表 JOIN 获取
    total      DECIMAL(10, 2)
);

-- ✅ 正确：通过外键关联
CREATE TABLE good_orders (
    id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    total   DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    FOREIGN KEY (user_id) REFERENCES users(id)
);
-- 查询时 JOIN 获取用户名
SELECT o.id, u.name, o.total
FROM good_orders o
JOIN users u ON o.user_id = u.id;

6 本章要点