-- ❌ 违反 1NF：地址字段包含了省、市、区
CREATE TABLE bad_design (
    id INT PRIMARY KEY,
    address VARCHAR(200)  -- "北京市朝阳区建国路88号"
);

-- ✅ 符合 1NF：拆分为独立字段
CREATE TABLE good_design (
    id INT PRIMARY KEY,
    province VARCHAR(50),  -- 北京市
    city VARCHAR(50),      -- 朝阳区
    detail_address VARCHAR(200) -- 建国路88号
);

-- ❌ 违反 2NF：course_name 只依赖 course_id，不依赖 student_id
CREATE TABLE student_course_bad (
    student_id INT,
    course_id INT,
    course_name VARCHAR(100),   -- 部分依赖
    score INT,
    PRIMARY KEY (student_id, course_id)
);

-- ✅ 符合 2NF：拆分为多表
CREATE TABLE courses (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE student_scores (
    student_id INT,
    course_id INT,
    score INT,
    PRIMARY KEY (student_id, course_id)
);

-- ❌ 违反 3NF：department_name 依赖 department_id，department_id 依赖 id
CREATE TABLE employees_bad (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    department_name VARCHAR(100)  -- 传递依赖
);

-- ✅ 符合 3NF
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT
);

-- 范式设计：每次查询订单需要 JOIN 用户表
SELECT o.order_no, u.username, o.total_amount
FROM orders o
JOIN users u ON o.user_id = u.id;

-- 反范式设计：orders 表冗余 username
CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    user_name VARCHAR(50) NOT NULL COMMENT '冗余用户名',
    total_amount DECIMAL(12,2) NOT NULL
);
-- 查询无需 JOIN
SELECT order_no, user_name, total_amount FROM orders;

CREATE TABLE example (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    deleted_at  DATETIME DEFAULT NULL COMMENT '软删除时间'
);

-- 1. 尽量 NOT NULL + DEFAULT
-- ❌ 允许 NULL
phone VARCHAR(20) NULL

-- ✅ NOT NULL + 默认值
phone VARCHAR(20) NOT NULL DEFAULT '' COMMENT '手机号'

-- 2. 使用最小够用的类型
-- ❌ 所有字段用 VARCHAR(255)
-- ✅ 根据实际长度选择

-- 3. 金额用 DECIMAL 或 BIGINT 存分
-- ❌ FLOAT/DOUBLE
-- ✅ DECIMAL(12,2) 或 BIGINT UNSIGNED

-- 4. 主键用 BIGINT UNSIGNED
-- ❌ INT（可能溢出）
-- ❌ UUID（占空间、无序、影响 B+Tree 性能）

-- 5. 字符集统一 utf8mb4

-- 按年份分区的订单表
CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    status TINYINT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, created_at),  -- 分区键必须在主键中
    INDEX idx_user_id (user_id),
    INDEX idx_order_no (order_no)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 查询会自动裁剪分区
EXPLAIN SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
-- partitions: p2026（只扫描 p2026 分区）

-- 按月分区（需要定期添加新分区）
CREATE TABLE access_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    action VARCHAR(50) NOT NULL,
    ip_address VARCHAR(45),
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, created_at)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')),
    PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')),
    PARTITION p202603 VALUES LESS THAN (TO_DAYS('2026-04-01')),
    PARTITION p202604 VALUES LESS THAN (TO_DAYS('2026-05-01')),
    PARTITION p202605 VALUES LESS THAN (TO_DAYS('2026-06-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 动态添加分区
ALTER TABLE access_logs REORGANIZE PARTITION p_future INTO (
    PARTITION p202606 VALUES LESS THAN (TO_DAYS('2026-07-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 删除旧分区（比 DELETE 快得多）
ALTER TABLE access_logs DROP PARTITION p202601;

-- 推荐：utf8mb4（支持完整的 Unicode，包括 emoji）
CREATE TABLE my_table (
    content VARCHAR(100)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 查看当前字符集设置
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

-- 修改数据库字符集
ALTER DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 修改表字符集
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- ===================== 电商系统核心表设计 =====================

CREATE DATABASE IF NOT EXISTS ecommerce
    DEFAULT CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

USE ecommerce;

-- 用户表
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
    phone VARCHAR(20) NOT NULL DEFAULT '' COMMENT '手机号',
    password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希',
    nickname VARCHAR(50) DEFAULT '' COMMENT '昵称',
    avatar_url VARCHAR(500) DEFAULT '' COMMENT '头像',
    gender TINYINT UNSIGNED DEFAULT 0 COMMENT '性别：0=未知 1=男 2=女',
    balance DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '余额',
    status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态：1=正常 0=禁用',
    last_login_at DATETIME DEFAULT NULL COMMENT '最后登录时间',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME DEFAULT NULL,
    INDEX idx_phone (phone),
    INDEX idx_status (status)
) ENGINE=InnoDB COMMENT='用户表';

-- 商品表
CREATE TABLE products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '商品ID',
    name VARCHAR(200) NOT NULL COMMENT '商品名称',
    description TEXT DEFAULT NULL COMMENT '商品描述',
    price DECIMAL(10,2) NOT NULL COMMENT '售价',
    cost_price DECIMAL(10,2) DEFAULT NULL COMMENT '成本价',
    category_id INT UNSIGNED NOT NULL COMMENT '分类ID',
    stock INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '库存',
    sales INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '销量',
    status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态：1=上架 0=下架',
    main_image VARCHAR(500) DEFAULT '' COMMENT '主图',
    attributes JSON DEFAULT NULL COMMENT '商品属性',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME DEFAULT NULL,
    INDEX idx_category (category_id),
    INDEX idx_status_sales (status, sales)
) ENGINE=InnoDB COMMENT='商品表';

-- 订单表
CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',
    order_no VARCHAR(32) NOT NULL UNIQUE COMMENT '订单号',
    user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    total_amount DECIMAL(12,2) NOT NULL COMMENT '订单总金额',
    pay_amount DECIMAL(12,2) NOT NULL COMMENT '实付金额',
    status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态：0=待支付 1=已支付 2=已发货 3=已完成 4=已取消 5=已退款',
    pay_type TINYINT UNSIGNED DEFAULT NULL COMMENT '支付方式',
    pay_time DATETIME DEFAULT NULL COMMENT '支付时间',
    receiver_name VARCHAR(50) DEFAULT '' COMMENT '收货人',
    receiver_phone VARCHAR(20) DEFAULT '' COMMENT '收货电话',
    receiver_address VARCHAR(500) DEFAULT '' COMMENT '收货地址',
    remark VARCHAR(500) DEFAULT '' COMMENT '备注',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME DEFAULT NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_user_status (user_id, status)
) ENGINE=InnoDB COMMENT='订单表';

-- 方案 A：邻接表（最常用）
CREATE TABLE follows (
    follower_id BIGINT UNSIGNED NOT NULL COMMENT '关注者ID',
    following_id BIGINT UNSIGNED NOT NULL COMMENT '被关注者ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (follower_id, following_id),
    INDEX idx_following (following_id)
) ENGINE=InnoDB COMMENT='关注关系表';

-- 查询 A 关注了谁
SELECT following_id FROM follows WHERE follower_id = 1;
-- 查询谁关注了 A
SELECT follower_id FROM follows WHERE following_id = 1;