1
-- 违反 1NF：orders 列包含多个值
2
CREATE TABLE users_bad (
3
    id BIGINT PRIMARY KEY,
4
    name VARCHAR(100),
5
    orders VARCHAR(500)  -- "1001,1002,1003" — 非原子值
6
);
7

8
-- 满足 1NF：拆分为独立行
9
CREATE TABLE users (
10
    id BIGINT PRIMARY KEY,
11
    name VARCHAR(100)
12
);
13

14
CREATE TABLE orders (
15
    id BIGINT PRIMARY KEY,
16
    user_id BIGINT REFERENCES users(id),
17
    status VARCHAR(20)
18
);

1
-- 违反 2NF：(order_id, product_id) 是主键，但 product_name 只依赖 product_id
2
CREATE TABLE order_items_bad (
3
    order_id BIGINT,
4
    product_id BIGINT,
5
    quantity INT,
6
    product_name VARCHAR(200),  -- 只依赖 product_id，不依赖 order_id
7
    PRIMARY KEY (order_id, product_id)
8
);
9

10
-- 满足 2NF：将 product_name 移到 products 表
11
CREATE TABLE order_items (
12
    order_id BIGINT,
13
    product_id BIGINT,
14
    quantity INT,
15
    PRIMARY KEY (order_id, product_id)
16
);
17

18
CREATE TABLE products (
19
    id BIGINT PRIMARY KEY,
20
    name VARCHAR(200)
21
);

1
-- 违反 3NF：user_id → city → city_timezone（传递依赖）
2
CREATE TABLE orders_bad (
3
    id BIGINT PRIMARY KEY,
4
    user_id BIGINT,
5
    city VARCHAR(50),
6
    city_timezone VARCHAR(50),  -- 依赖 city，不直接依赖 order 的主键
7
    status VARCHAR(20)
8
);
9

10
-- 满足 3NF：city 和 timezone 移到 users 表
11
CREATE TABLE orders (
12
    id BIGINT PRIMARY KEY,
13
    user_id BIGINT REFERENCES users(id),
14
    status VARCHAR(20)
15
);
16

17
CREATE TABLE users (
18
    id BIGINT PRIMARY KEY,
19
    name VARCHAR(100),
20
    city VARCHAR(50),
21
    city_timezone VARCHAR(50)  -- 或者进一步拆分到 cities 表
22
);

1
-- 满足 3NF 但违反 BCNF
2
-- 假设一个学生选一门课只有一个老师，一个老师只教一门课
3
-- 候选键：(student_id, course_id) 和 (student_id, teacher_id)
4
-- teacher_id → course_id（teacher_id 是决定因素但不是候选键）
5

6
CREATE TABLE enrollments_bad (
7
    student_id BIGINT,
8
    course_id BIGINT,
9
    teacher_id BIGINT,
10
    PRIMARY KEY (student_id, course_id)
11
    -- teacher_id → course_id，但 teacher_id 不是候选键
12
);
13

14
-- 满足 BCNF：拆分为两张表
15
CREATE TABLE teacher_courses (
16
    teacher_id BIGINT PRIMARY KEY,
17
    course_id BIGINT  -- 每个老师只教一门课
18
);
19

20
CREATE TABLE enrollments (
21
    student_id BIGINT,
22
    teacher_id BIGINT,
23
    PRIMARY KEY (student_id, teacher_id)
24
);

1
-- 3NF：查询订单详情需要 JOIN 5 张表
2
SELECT o.id, u.name, oi.quantity, p.name, p.price, c.name AS category
3
FROM orders o
4
JOIN users u ON o.user_id = u.id
5
JOIN order_items oi ON o.id = oi.order_id
6
JOIN products p ON oi.product_id = p.id
7
JOIN categories c ON p.category_id = c.id
8
WHERE o.id = 1001;

1
-- 策略1：冗余列 — 在 order_items 中冗余 product_name
2
ALTER TABLE order_items ADD COLUMN product_name VARCHAR(200);
3

4
-- 查询不再需要 JOIN products 表
5
SELECT oi.order_id, oi.product_name, oi.quantity
6
FROM order_items oi
7
WHERE oi.order_id = 1001;
8

9
-- 策略2：预计算列 — 在 orders 中存储 total_amount
10
ALTER TABLE orders ADD COLUMN total_amount DECIMAL(10,2);
11

12
-- 策略3：汇总表 — 按月按品类汇总销售额
13
CREATE TABLE monthly_category_sales (
14
    month DATE,
15
    category_id BIGINT,
16
    category_name VARCHAR(100),  -- 冗余
17
    total_sales DECIMAL(15,2),
18
    order_count INT,
19
    PRIMARY KEY (month, category_id)
20
);

1
-- MySQL 中不同 ALTER 操作的代价
2
-- 即时操作（仅修改元数据）
3
ALTER TABLE users ALTER COLUMN name SET DEFAULT 'unknown';
4

5
--  INPLACE 操作（不阻塞读写，但可能耗时）
6
ALTER TABLE users ADD INDEX idx_city (city);  -- Online DDL
7

8
-- COPY 操作（重建整表，长时间锁表）
9
ALTER TABLE users MODIFY COLUMN name VARCHAR(300);  -- 增大 VARCHAR 可能需要重建
10
ALTER TABLE users CHANGE COLUMN id user_id BIGINT;  -- 改列名需要重建

1
# gh-ost：GitHub 开源的在线表变更工具
2
# 原理：创建幽灵表 → 增量同步 binlog → 切换表名
3
gh-ost \
4
  --host=mysql-master \
5
  --database=shop \
6
  --table=orders \
7
  --alter="ADD COLUMN shipping_address VARCHAR(500)" \
8
  --allow-on-master \
9
  --execute
10

11
# pt-online-schema-change：Percona 工具
12
# 原理：创建新表 → 增量同步触发器 → 切换表名
13
pt-online-schema-change \
14
  --host=mysql-master \
15
  --user=admin \
16
  --alter="ADD COLUMN shipping_address VARCHAR(500)" \
17
  D=shop,t=orders \
18
  --execute

1
# 双写伪代码示例
2
class OrderRepository:
3
    def create(self, order):
4
        # 写旧表（兼容期）
5
        old_db.execute(
6
            "INSERT INTO orders (id, user_id, status, amount) VALUES (%s, %s, %s, %s)",
7
            (order.id, order.user_id, order.status, order.amount)
8
        )
9
        # 写新表（新增 shipping_address 列）
10
        new_db.execute(
11
            "INSERT INTO orders_v2 (id, user_id, status, amount, shipping_address) "
12
            "VALUES (%s, %s, %s, %s, %s)",
13
            (order.id, order.user_id, order.status, order.amount, order.shipping_address)
14
        )
15

16
    def get(self, order_id):
17
        # 读取仍走旧表（切换前）
18
        return old_db.query("SELECT * FROM orders WHERE id = %s", (order_id,))

1
-- Flyway 迁移脚本：V10__add_shipping_address.sql
2
ALTER TABLE orders ADD COLUMN shipping_address VARCHAR(500);
3

4
-- Flyway 迁移脚本：V11__create_order_stats_table.sql
5
CREATE TABLE order_stats (
6
    stat_date DATE PRIMARY KEY,
7
    total_orders INT,
8
    total_amount DECIMAL(15,2),
9
    avg_order_amount DECIMAL(10,2)
10
);

1
# Liquibase 变更日志
2
databaseChangeLog:
3
  - changeSet:
4
      id: 10
5
      author: team-data
6
      changes:
7
        - addColumn:
8
            tableName: orders
9
            columns:
10
              - column:
11
                  name: shipping_address
12
                  type: VARCHAR(500)
13
  - changeSet:
14
      id: 11
15
      author: team-data
16
      changes:
17
        - createTable:
18
            tableName: order_stats
19
            columns:
20
              - column: { name: stat_date, type: DATE, constraints: { primaryKey: true } }
21
              - column: { name: total_orders, type: INT }
22
              - column: { name: total_amount, type: DECIMAL(15,2) }

1
{
2
  "orderId": 1001,
3
  "userId": 42,
4
  "items": [
5
    {"productId": 7, "quantity": 2, "unitPrice": 29.99},
6
    {"productId": 13, "quantity": 1, "unitPrice": 149.00}
7
  ],
8
  "status": "shipped",
9
  "createdAt": "2026-04-21T10:00:00Z"
10
}

1
{
2
  "type": "record",
3
  "name": "Order",
4
  "namespace": "com.example",
5
  "fields": [
6
    {"name": "orderId", "type": "long"},
7
    {"name": "userId", "type": "long"},
8
    {"name": "items", "type": {"type": "array", "items": "OrderItem"}},
9
    {"name": "status", "type": {"type": "enum", "name": "OrderStatus", "symbols": ["PENDING", "SHIPPED", "DELIVERED", "CANCELLED"]}},
10
    {"name": "createdAt", "type": "long", "logicalType": "timestamp-millis"}
11
  ]
12
}

1
// v1: 原始定义
2
message User {
3
  int64 id = 1;
4
  string name = 2;
5
  string email = 3;
6
}
7

8
// v2: 演化后的定义
9
message User {
10
  int64 id = 1;
11
  string name = 2;
12
  // string email = 3;  ← 删除字段：编号 3 保留，不可复用
13
  string username = 4;   // 新增字段：使用新编号
14
  int32 age = 5;         // 新增字段
15
  reserved 3;            // 声明保留，防止误用
16
  reserved "email";      // 保留字段名
17
}

1
// 写入模式（v1）
2
{
3
  "type": "record",
4
  "name": "User",
5
  "fields": [
6
    {"name": "id", "type": "long"},
7
    {"name": "name", "type": "string"},
8
    {"name": "email", "type": ["null", "string"], "default": null}
9
  ]
10
}
11

12
// 读取模式（v2）— 删除 email，新增 age
13
{
14
  "type": "record",
15
  "name": "User",
16
  "fields": [
17
    {"name": "id", "type": "long"},
18
    {"name": "name", "type": "string"},
19
    {"name": "age", "type": ["null", "int"], "default": null}
20
  ]
21
}
22
// 解析规则：email 在写模式有、读模式无 → 忽略
23
//           age 在读模式有、写模式无 → 使用默认值 null