DDL 操作

CREATE DATABASE [IF NOT EXISTS] 数据库名
[CHARACTER SET 字符集]
[COLLATE 排序规则];

-- 基本创建
CREATE DATABASE testdb;

-- 完整创建(推荐)
CREATE DATABASE IF NOT EXISTS testdb 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 查看创建语句
SHOW CREATE DATABASE testdb;

-- 查看所有数据库
SHOW DATABASES;

-- 查看匹配模式的数据库
SHOW DATABASES LIKE 'test%';

-- 查看当前使用的数据库
SELECT DATABASE();

USE 数据库名;

-- 示例
USE testdb;

ALTER DATABASE 数据库名
[CHARACTER SET 字符集]
[COLLATE 排序规则];

-- 示例:修改字符集和排序规则
ALTER DATABASE testdb 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

DROP DATABASE [IF EXISTS] 数据库名;

-- 示例
DROP DATABASE IF EXISTS testdb;

CREATE TABLE example_numbers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    age TINYINT UNSIGNED COMMENT '年龄(0-255)',
    user_id BIGINT UNSIGNED COMMENT '用户ID',
    score SMALLINT COMMENT '分数(-32768~32767)',
    status TINYINT DEFAULT 0 COMMENT '状态: 0-禁用, 1-启用'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE example_decimal (
    id INT PRIMARY KEY,
    price DECIMAL(10,2) COMMENT '价格(最大99999999.99)',
    weight FLOAT COMMENT '重量(近似值)',
    rate DOUBLE COMMENT '比率(高精度)',
    amount DECIMAL(20,4) COMMENT '金额(精确计算)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- BIT类型用于存储位字段
CREATE TABLE example_bit (
    id INT PRIMARY KEY,
    flags BIT(8) COMMENT '8位标志位',
    gender BIT(1) COMMENT '性别: 0-女, 1-男'
);

-- 插入数据
INSERT INTO example_bit VALUES (1, b'00000111', b'1');

-- 查询(默认显示为二进制)
SELECT id, flags, flags+0 AS flags_value FROM example_bit;

CREATE TABLE example_string (
    id INT PRIMARY KEY,
    phone CHAR(11) COMMENT '手机号(固定11位)',
    id_card CHAR(18) COMMENT '身份证号(固定18位)',
    username VARCHAR(50) COMMENT '用户名(最多50字符)',
    email VARCHAR(100) COMMENT '邮箱(最多100字符)',
    intro TEXT COMMENT '个人简介',
    content MEDIUMTEXT COMMENT '文章内容',
    status ENUM('active', 'inactive', 'deleted') DEFAULT 'active' COMMENT '状态',
    tags SET('tech', 'life', 'travel') COMMENT '标签'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- CHAR定长,不足补空格
phone CHAR(11)  -- 存储'13800138000',占用11字节

-- VARCHAR变长,按实际长度存储
username VARCHAR(50) -- 存储'tom',占用4字节(3字符+1长度字节)

-- 性能对比:
-- CHAR: 访问快,但可能浪费空间
-- VARCHAR: 节省空间,但访问稍慢(需计算长度)

-- ENUM: 单选
CREATE TABLE users (
    id INT PRIMARY KEY,
    gender ENUM('male', 'female', 'other') COMMENT '性别',
    level ENUM('vip', 'svip', 'admin') DEFAULT 'vip' COMMENT '会员等级'
);

-- 插入ENUM
INSERT INTO users VALUES (1, 'male', 'vip');

-- SET: 多选
CREATE TABLE articles (
    id INT PRIMARY KEY,
    tags SET('tech', 'java', 'database', 'web') COMMENT '标签'
);

-- 插入SET
INSERT INTO articles VALUES (1, 'tech,java,database');

-- 查询SET
SELECT * FROM articles WHERE FIND_IN_SET('java', tags) > 0;

CREATE TABLE example_datetime (
    id INT PRIMARY KEY,
    birth_date DATE COMMENT '出生日期',
    work_time TIME COMMENT '工作时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间(自动更新)',
    publish_year YEAR COMMENT '发布年份'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE example_binary (
    id INT PRIMARY KEY,
    avatar BLOB COMMENT '头像图片',
    file_data MEDIUMBLOB COMMENT '文件数据'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE example_json (
    id INT PRIMARY KEY,
    user_info JSON COMMENT '用户信息JSON',
    preferences JSON COMMENT '用户偏好设置'
);

-- 插入JSON数据
INSERT INTO example_json VALUES (
    1, 
    '{"name": "张三", "age": 25, "skills": ["Java", "MySQL"]}',
    '{"theme": "dark", "language": "zh-CN"}'
);

-- 查询JSON字段
SELECT 
    id,
    user_info->>'$.name' AS name,
    user_info->>'$.age' AS age,
    preferences->>'$.theme' AS theme
FROM example_json;

-- JSON函数示例
SELECT 
    JSON_EXTRACT(user_info, '$.name') AS name,
    JSON_UNQUOTE(JSON_EXTRACT(user_info, '$.name')) AS name_unquoted,
    user_info->'$.skills[0]' AS first_skill
FROM example_json;

-- 更新JSON字段
UPDATE example_json 
SET user_info = JSON_SET(user_info, '$.age', 26)
WHERE id = 1;

-- 创建表时定义主键
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

-- 或使用表级约束
CREATE TABLE users (
    user_id INT AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    PRIMARY KEY (user_id)
);

-- 复合主键(不推荐,建议使用单列主键+唯一索引)
CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    PRIMARY KEY (user_id, role_id)
);

CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT,
    -- 外键约束
    CONSTRAINT fk_dept 
        FOREIGN KEY (dept_id) 
        REFERENCES departments(dept_id)
        ON DELETE CASCADE        -- 父表删除时级联删除
        ON UPDATE CASCADE        -- 父表更新时级联更新
) ENGINE=InnoDB;

-- 列级唯一约束
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

-- 表级唯一约束(可命名)
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    CONSTRAINT uk_username UNIQUE (username),
    CONSTRAINT uk_email UNIQUE (email)
);

-- 复合唯一约束
CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    UNIQUE KEY uk_user_role (user_id, role_id)
);

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20)  -- 允许NULL
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0,
    -- 检查约束
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT chk_stock CHECK (stock >= 0)
);

-- 插入数据时会检查约束
INSERT INTO products VALUES (1, 'iPhone', -100, 10);  -- 错误:price必须大于0

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    status TINYINT DEFAULT 1 COMMENT '1-正常,0-禁用',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE [IF NOT EXISTS] 表名 (
    列定义1 [列约束],
    列定义2 [列约束],
    ...
    [表约束]
) ENGINE=存储引擎 
  DEFAULT CHARSET=字符集 
  COLLATE=排序规则
  COMMENT='表注释';

列名 数据类型 
    [NOT NULL | NULL] 
    [DEFAULT 默认值] 
    [AUTO_INCREMENT] 
    [UNIQUE | PRIMARY KEY] 
    [COMMENT '列注释']

CREATE TABLE IF NOT EXISTS users (
    -- 主键
    user_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    
    -- 基本信息
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希',
    email VARCHAR(100) NOT NULL COMMENT '邮箱',
    phone CHAR(11) COMMENT '手机号',
    
    -- 个人信息
    nickname VARCHAR(50) COMMENT '昵称',
    avatar VARCHAR(255) DEFAULT '/default/avatar.png' COMMENT '头像URL',
    gender ENUM('male', 'female', 'other') DEFAULT 'other' COMMENT '性别',
    birthday DATE COMMENT '生日',
    
    -- 状态信息
    status TINYINT UNSIGNED DEFAULT 1 COMMENT '状态: 0-禁用, 1-正常, 2-冻结',
    level TINYINT UNSIGNED DEFAULT 1 COMMENT '会员等级: 1-普通, 2-VIP, 3-SVIP',
    
    -- 时间戳
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    last_login_at TIMESTAMP NULL COMMENT '最后登录时间',
    
    -- 唯一约束
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email (email),
    UNIQUE KEY uk_phone (phone)
    
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_unicode_ci
  COMMENT='用户表';

-- 查看当前数据库的所有表
SHOW TABLES;

-- 查看匹配的表
SHOW TABLES LIKE 'user%';

-- 查看表结构(简洁)
DESC users;
-- 或
DESCRIBE users;

-- 查看表创建语句(完整)
SHOW CREATE TABLE users\G

-- 查看表详细信息
SHOW TABLE STATUS LIKE 'users'\G

-- 查看列详细信息
SHOW FULL COLUMNS FROM users;

-- 添加单列
ALTER TABLE users ADD COLUMN age TINYINT UNSIGNED COMMENT '年龄';

-- 指定位置
ALTER TABLE users ADD COLUMN age TINYINT UNSIGNED AFTER username COMMENT '年龄';
ALTER TABLE users ADD COLUMN id_card CHAR(18) FIRST COMMENT '身份证号';

-- 添加多列
ALTER TABLE users 
    ADD COLUMN age TINYINT UNSIGNED COMMENT '年龄',
    ADD COLUMN address VARCHAR(200) COMMENT '地址';

ALTER TABLE users DROP COLUMN age;

-- 删除多列
ALTER TABLE users 
    DROP COLUMN age,
    DROP COLUMN address;

-- MODIFY: 只修改列定义,不改变列名
ALTER TABLE users MODIFY COLUMN email VARCHAR(150) NOT NULL COMMENT '邮箱地址';

-- CHANGE: 可以修改列名和列定义
ALTER TABLE users CHANGE COLUMN email user_email VARCHAR(150) NOT NULL COMMENT '邮箱地址';

-- 修改列的位置
ALTER TABLE users MODIFY COLUMN age TINYINT AFTER username;

-- 添加主键
ALTER TABLE users ADD PRIMARY KEY (user_id);

-- 删除主键
ALTER TABLE users DROP PRIMARY KEY;

-- 添加唯一约束
ALTER TABLE users ADD UNIQUE KEY uk_email (email);

-- 删除唯一约束
ALTER TABLE users DROP INDEX uk_email;

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

-- 删除外键
ALTER TABLE orders DROP FOREIGN KEY fk_user;

-- 修改存储引擎
ALTER TABLE users ENGINE=InnoDB;

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

-- 修改表注释
ALTER TABLE users COMMENT='用户信息表';

-- 重命名表
ALTER TABLE users RENAME TO sys_users;
-- 或
RENAME TABLE users TO sys_users;

-- 删除表
DROP TABLE users;

-- 如果存在才删除
DROP TABLE IF EXISTS users;

-- 删除多个表
DROP TABLE table1, table2, table3;

TRUNCATE TABLE users;

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    author_id INT NOT NULL,
    publish_time DATETIME,
    
    -- 普通索引
    INDEX idx_author (author_id),
    -- 复合索引
    INDEX idx_author_time (author_id, publish_time),
    -- 唯一索引
    UNIQUE KEY uk_title (title),
    -- 全文索引
    FULLTEXT INDEX ft_content (content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建普通索引
CREATE INDEX idx_author ON articles(author_id);

-- 创建复合索引
CREATE INDEX idx_author_time ON articles(author_id, publish_time);

-- 创建唯一索引
CREATE UNIQUE INDEX uk_title ON articles(title);

-- 创建全文索引
CREATE FULLTEXT INDEX ft_content ON articles(content);

-- 添加普通索引
ALTER TABLE articles ADD INDEX idx_author (author_id);

-- 添加复合索引
ALTER TABLE articles ADD INDEX idx_author_time (author_id, publish_time);

-- 添加唯一索引
ALTER TABLE articles ADD UNIQUE KEY uk_title (title);

-- 添加全文索引
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);

-- 查看表的索引
SHOW INDEX FROM articles;

-- 查看索引信息
SHOW INDEX FROM articles\G

-- 查看表状态
SHOW TABLE STATUS LIKE 'articles'\G

-- 使用DROP INDEX
DROP INDEX idx_author ON articles;

-- 使用ALTER TABLE
ALTER TABLE articles DROP INDEX idx_author;

-- 删除主键
ALTER TABLE articles DROP PRIMARY KEY;

-- 经常查询的列
SELECT * FROM users WHERE username = 'tom';
CREATE INDEX idx_username ON users(username);

-- 经常排序的列
SELECT * FROM articles ORDER BY publish_time DESC;
CREATE INDEX idx_publish_time ON articles(publish_time);

-- 复合索引
CREATE INDEX idx_name_age_city ON users(name, age, city);

-- 可以使用索引的查询
WHERE name = 'tom'
WHERE name = 'tom' AND age = 25
WHERE name = 'tom' AND age = 25 AND city = '北京'

-- 无法使用索引的查询
WHERE age = 25
WHERE city = '北京'
WHERE name = 'tom' AND city = '北京'  -- 跳过了age,只能用到name部分

-- 性别列只有2个值,选择性低,不适合单独建索引
SELECT * FROM users WHERE gender = 'male';  -- 全表扫描更快

-- 如果经常按性别和年龄查询,可以建复合索引
CREATE INDEX idx_gender_age ON users(gender, age);

-- 区分度计算
SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

-- 区分度接近1适合建索引,接近0不适合

-- NULL值会影响索引效率
-- 推荐设置默认值代替NULL
ALTER TABLE users MODIFY COLUMN nickname VARCHAR(50) NOT NULL DEFAULT '';

-- 索引过多会影响INSERT、UPDATE、DELETE性能
-- 建议单表索引数量不超过5个

-- 对长字符串列,使用前缀索引
CREATE INDEX idx_email ON users(email(20));

-- 确定前缀长度
SELECT 
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS prefix_10,
    COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS prefix_15,
    COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) AS prefix_20
FROM users;
-- 选择区分度接近1的最小长度

-- 覆盖索引:查询的列都在索引中,无需回表
CREATE INDEX idx_username_email ON users(username, email);

-- 使用覆盖索引
SELECT username, email FROM users WHERE username = 'tom';  -- 无需回表

-- 使用EXPLAIN查看
EXPLAIN SELECT username, email FROM users WHERE username = 'tom';
-- Extra列显示: Using index

-- 1. 在索引列上使用函数
SELECT * FROM users WHERE DATE(created_at) = '2024-01-01';  -- 索引失效
-- 优化:
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';

-- 2. 使用OR连接非索引列
SELECT * FROM users WHERE username = 'tom' OR age = 25;  -- 如果age无索引,全部失效
-- 优化: 使用UNION
SELECT * FROM users WHERE username = 'tom'
UNION
SELECT * FROM users WHERE age = 25;

-- 3. LIKE以通配符开头
SELECT * FROM users WHERE username LIKE '%tom%';  -- 索引失效
-- 优化:
SELECT * FROM users WHERE username LIKE 'tom%';  -- 索引有效

-- 4. 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;  -- phone是VARCHAR,索引失效
-- 优化:
SELECT * FROM users WHERE phone = '13800138000';

-- 5. 使用!=或<>
SELECT * FROM users WHERE status != 0;  -- 索引可能失效(取决于数据分布)

-- 6. 使用NOT IN
SELECT * FROM users WHERE status NOT IN (0, 1);  -- 索引失效

-- 7. 复合索引不满足最左前缀
-- 索引: idx_name_age_city(name, age, city)
SELECT * FROM users WHERE age = 25;  -- 索引失效

-- 1. 用户表
CREATE TABLE users (
    user_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希',
    email VARCHAR(100) NOT NULL COMMENT '邮箱',
    phone CHAR(11) COMMENT '手机号',
    nickname VARCHAR(50) COMMENT '昵称',
    avatar VARCHAR(255) DEFAULT '/default/avatar.png' COMMENT '头像URL',
    gender ENUM('male', 'female', 'other') DEFAULT 'other' COMMENT '性别',
    birthday DATE COMMENT '生日',
    status TINYINT UNSIGNED DEFAULT 1 COMMENT '状态: 0-禁用, 1-正常, 2-冻结',
    level TINYINT UNSIGNED DEFAULT 1 COMMENT '会员等级',
    balance DECIMAL(10,2) UNSIGNED DEFAULT 0.00 COMMENT '账户余额',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    last_login_at TIMESTAMP NULL COMMENT '最后登录时间',
    last_login_ip VARCHAR(45) COMMENT '最后登录IP',
    
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email (email),
    UNIQUE KEY uk_phone (phone),
    INDEX idx_created_at (created_at),
    INDEX idx_status_level (status, level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

-- 2. 商品分类表
CREATE TABLE categories (
    category_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '分类ID',
    parent_id INT UNSIGNED DEFAULT 0 COMMENT '父分类ID, 0表示顶级分类',
    category_name VARCHAR(50) NOT NULL COMMENT '分类名称',
    level TINYINT UNSIGNED NOT NULL COMMENT '分类层级: 1-一级, 2-二级, 3-三级',
    sort_order INT UNSIGNED DEFAULT 0 COMMENT '排序序号',
    icon VARCHAR(255) COMMENT '分类图标',
    status TINYINT UNSIGNED DEFAULT 1 COMMENT '状态: 0-禁用, 1-启用',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_parent_id (parent_id),
    INDEX idx_level_sort (level, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品分类表';

-- 3. 商品表
CREATE TABLE products (
    product_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '商品ID',
    category_id INT UNSIGNED NOT NULL COMMENT '分类ID',
    product_name VARCHAR(200) NOT NULL COMMENT '商品名称',
    subtitle VARCHAR(255) COMMENT '商品副标题',
    main_image VARCHAR(255) COMMENT '主图URL',
    sub_images TEXT COMMENT '子图URL列表(JSON数组)',
    detail TEXT COMMENT '商品详情(HTML)',
    price DECIMAL(10,2) UNSIGNED NOT NULL COMMENT '销售价',
    original_price DECIMAL(10,2) UNSIGNED COMMENT '原价',
    stock INT UNSIGNED DEFAULT 0 COMMENT '库存',
    sales INT UNSIGNED DEFAULT 0 COMMENT '销量',
    status TINYINT UNSIGNED DEFAULT 1 COMMENT '状态: 0-下架, 1-上架, 2-删除',
    weight DECIMAL(10,2) COMMENT '重量(kg)',
    is_hot TINYINT UNSIGNED DEFAULT 0 COMMENT '是否热销: 0-否, 1-是',
    is_new TINYINT UNSIGNED DEFAULT 0 COMMENT '是否新品: 0-否, 1-是',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_category (category_id),
    INDEX idx_price (price),
    INDEX idx_sales (sales),
    INDEX idx_status_hot_new (status, is_hot, is_new),
    FULLTEXT INDEX ft_name (product_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

-- 4. 订单表
CREATE TABLE orders (
    order_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',
    order_no VARCHAR(64) NOT NULL COMMENT '订单号',
    user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    
    -- 收货信息
    receiver_name VARCHAR(50) NOT NULL COMMENT '收货人姓名',
    receiver_phone CHAR(11) NOT NULL COMMENT '收货人手机号',
    receiver_province VARCHAR(50) NOT NULL COMMENT '省',
    receiver_city VARCHAR(50) NOT NULL COMMENT '市',
    receiver_district VARCHAR(50) NOT NULL COMMENT '区',
    receiver_address VARCHAR(255) NOT NULL COMMENT '详细地址',
    
    -- 金额信息
    total_amount DECIMAL(10,2) UNSIGNED NOT NULL COMMENT '商品总金额',
    freight_amount DECIMAL(10,2) UNSIGNED DEFAULT 0.00 COMMENT '运费',
    discount_amount DECIMAL(10,2) UNSIGNED DEFAULT 0.00 COMMENT '优惠金额',
    pay_amount DECIMAL(10,2) UNSIGNED NOT NULL COMMENT '实付金额',
    
    -- 状态信息
    status TINYINT UNSIGNED DEFAULT 0 COMMENT '状态: 0-待付款, 1-待发货, 2-已发货, 3-已完成, 4-已取消, 5-售后中',
    payment_type TINYINT UNSIGNED COMMENT '支付方式: 1-支付宝, 2-微信, 3-银行卡',
    payment_time TIMESTAMP NULL COMMENT '支付时间',
    delivery_time TIMESTAMP NULL COMMENT '发货时间',
    receive_time TIMESTAMP NULL COMMENT '收货时间',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY uk_order_no (order_no),
    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 DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- 5. 订单商品表
CREATE TABLE order_items (
    item_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '订单商品ID',
    order_id BIGINT UNSIGNED NOT NULL COMMENT '订单ID',
    product_id BIGINT UNSIGNED NOT NULL COMMENT '商品ID',
    product_name VARCHAR(200) NOT NULL COMMENT '商品名称(冗余)',
    main_image VARCHAR(255) COMMENT '商品主图(冗余)',
    price DECIMAL(10,2) UNSIGNED NOT NULL COMMENT '商品单价',
    quantity INT UNSIGNED NOT NULL COMMENT '购买数量',
    total_amount DECIMAL(10,2) UNSIGNED NOT NULL COMMENT '小计金额',
    
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id),
    
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单商品表';

-- 6. 地址表
CREATE TABLE addresses (
    address_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '地址ID',
    user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    receiver_name VARCHAR(50) NOT NULL COMMENT '收货人姓名',
    receiver_phone CHAR(11) NOT NULL COMMENT '收货人手机号',
    province VARCHAR(50) NOT NULL COMMENT '省',
    city VARCHAR(50) NOT NULL COMMENT '市',
    district VARCHAR(50) NOT NULL COMMENT '区',
    address VARCHAR(255) NOT NULL COMMENT '详细地址',
    is_default TINYINT UNSIGNED DEFAULT 0 COMMENT '是否默认: 0-否, 1-是',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='收货地址表';

-- 1. 用户表
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    nickname VARCHAR(50),
    avatar VARCHAR(255) DEFAULT '/default/avatar.png',
    bio TEXT COMMENT '个人简介',
    website VARCHAR(255) COMMENT '个人网站',
    github VARCHAR(100) COMMENT 'GitHub用户名',
    role ENUM('user', 'author', 'admin') DEFAULT 'user' COMMENT '角色',
    status TINYINT DEFAULT 1 COMMENT '0-禁用, 1-正常',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_created_at (created_at),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 2. 文章表
CREATE TABLE articles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL COMMENT '作者ID',
    title VARCHAR(200) NOT NULL COMMENT '标题',
    slug VARCHAR(200) NOT NULL COMMENT 'URL友好标题',
    summary VARCHAR(500) COMMENT '摘要',
    content LONGTEXT COMMENT '文章内容',
    cover_image VARCHAR(255) COMMENT '封面图',
    view_count INT UNSIGNED DEFAULT 0 COMMENT '浏览量',
    like_count INT UNSIGNED DEFAULT 0 COMMENT '点赞数',
    comment_count INT UNSIGNED DEFAULT 0 COMMENT '评论数',
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft' COMMENT '状态',
    is_top TINYINT DEFAULT 0 COMMENT '是否置顶',
    allow_comment TINYINT DEFAULT 1 COMMENT '是否允许评论',
    published_at TIMESTAMP NULL COMMENT '发布时间',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY uk_slug (slug),
    INDEX idx_user_id (user_id),
    INDEX idx_status_published (status, published_at),
    INDEX idx_view_count (view_count),
    INDEX idx_like_count (like_count),
    FULLTEXT INDEX ft_title_content (title, content),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';

-- 3. 分类表
CREATE TABLE categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    slug VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255),
    parent_id INT UNSIGNED DEFAULT 0,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_parent_id (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分类表';

-- 4. 标签表
CREATE TABLE tags (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    slug VARCHAR(50) NOT NULL UNIQUE,
    article_count INT UNSIGNED DEFAULT 0 COMMENT '文章数量',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='标签表';

-- 5. 文章分类关联表
CREATE TABLE article_category (
    article_id BIGINT UNSIGNED NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (article_id, category_id),
    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章分类关联表';

-- 6. 文章标签关联表
CREATE TABLE article_tag (
    article_id BIGINT UNSIGNED NOT NULL,
    tag_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (article_id, tag_id),
    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章标签关联表';

-- 7. 评论表
CREATE TABLE comments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    article_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    parent_id BIGINT UNSIGNED DEFAULT 0 COMMENT '父评论ID, 0表示顶级评论',
    reply_to_user_id BIGINT UNSIGNED COMMENT '回复用户ID',
    content TEXT NOT NULL,
    like_count INT UNSIGNED DEFAULT 0,
    status TINYINT DEFAULT 1 COMMENT '0-待审核, 1-已审核, 2-已删除',
    ip VARCHAR(45) COMMENT '评论IP',
    user_agent VARCHAR(255) COMMENT '浏览器信息',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_article_id (article_id),
    INDEX idx_user_id (user_id),
    INDEX idx_parent_id (parent_id),
    
    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='评论表';

-- 原用户表(字段过多)
CREATE TABLE users (
    user_id BIGINT PRIMARY KEY,
    username VARCHAR(50),
    password_hash VARCHAR(255),
    email VARCHAR(100),
    phone CHAR(11),
    nickname VARCHAR(50),
    avatar VARCHAR(255),
    gender ENUM('male', 'female'),
    birthday DATE,
    bio TEXT,
    interests JSON,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 垂直拆分后

-- 用户基础信息表(频繁访问)
CREATE TABLE users_base (
    user_id BIGINT PRIMARY KEY,
    username VARCHAR(50),
    password_hash VARCHAR(255),
    email VARCHAR(100),
    phone CHAR(11),
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 用户详细信息表(较少访问)
CREATE TABLE users_profile (
    user_id BIGINT PRIMARY KEY,
    nickname VARCHAR(50),
    avatar VARCHAR(255),
    gender ENUM('male', 'female'),
    birthday DATE,
    bio TEXT,
    interests JSON,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users_base(user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 订单表按月分表
CREATE TABLE orders_202401 (
    order_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(64) NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status TINYINT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE KEY uk_order_no (order_no),
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE orders_202402 (
    -- 结构相同
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 订单表按user_id分4张表
CREATE TABLE orders_0 (
    order_id BIGINT UNSIGNED PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    -- ...
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE orders_1 (
    -- ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 应用层路由: table_name = 'orders_' + (user_id % 4)

-- 使用雪花算法生成全局唯一ID
-- 或使用数据库序列
CREATE TABLE sequence (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    stub CHAR(1) NOT NULL UNIQUE
) ENGINE=InnoDB;

-- 获取ID
REPLACE INTO sequence (stub) VALUES ('a');
SELECT LAST_INSERT_ID();

-- 不推荐:跨库JOIN
SELECT o.*, u.username 
FROM db1.orders o 
JOIN db2.users u ON o.user_id = u.id;

-- 推荐:应用层组装
-- 1. 查询订单
-- 2. 提取user_id列表
-- 3. 批量查询用户
-- 4. 应用层合并数据

-- 订单表冗余用户信息
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    username VARCHAR(50) NOT NULL,  -- 冗余字段
    user_phone CHAR(11),            -- 冗余字段
    -- ...
);

-- 优点:避免跨库查询
-- 缺点:数据一致性问题,需应用层同步

-- 分库分表场景不使用外键
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,  -- 无外键约束
    -- ...
);

-- 表名:小写,下划线分隔,复数形式
users, orders, order_items, product_categories

-- 列名:小写,下划线分隔
user_id, created_at, is_deleted

-- 主键:id 或 表名单数_id
id, user_id, order_id

-- 外键:关联表名_id
user_id, product_id, category_id

-- 布尔列:is_xxx, has_xxx
is_deleted, is_active, has_paid

-- 时间列:xxx_at
created_at, updated_at, deleted_at, paid_at

-- 索引:idx_列名, uk_列名(唯一索引), fk_表名(外键)
idx_user_id, uk_email, fk_order

-- 约束:chk_列名, fk_表名
chk_age, fk_user

-- 单机应用:自增ID
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -- ...
);

-- 分布式应用:雪花算法ID或UUID
CREATE TABLE users (
    id BIGINT UNSIGNED PRIMARY KEY COMMENT '雪花算法ID',
    -- ...
);

-- 或使用UUID(不推荐,索引效率低)
CREATE TABLE users (
    id CHAR(36) PRIMARY KEY COMMENT 'UUID',
    -- ...
);

CREATE TABLE example_design (
    -- 1. 主键
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    -- 2. 外键(尽量使用BIGINT UNSIGNED)
    user_id BIGINT UNSIGNED NOT NULL,
    
    -- 3. 业务字段
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    
    -- 4. 状态字段(使用TINYINT + COMMENT,避免直接使用ENUM)
    status TINYINT UNSIGNED DEFAULT 1 COMMENT '1-正常, 0-禁用, 2-冻结',
    
    -- 5. 金额字段(必须使用DECIMAL)
    amount DECIMAL(10,2) UNSIGNED DEFAULT 0.00,
    
    -- 6. 标志位(使用TINYINT(1),MySQL 8.0可使用BOOLEAN)
    is_deleted TINYINT(1) UNSIGNED DEFAULT 0,
    
    -- 7. 时间戳(必须包含created_at和updated_at)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- 8. 软删除时间(可选)
    deleted_at TIMESTAMP NULL,
    
    -- 索引
    INDEX idx_user_id (user_id),
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='示例表';

-- 完全范式化(需要JOIN,性能差)
SELECT o.*, u.username, c.category_name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN categories c ON o.category_id = c.id;

-- 适度反范式化(冗余字段,减少JOIN)
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    username VARCHAR(50) NOT NULL,  -- 冗余
    category_id INT NOT NULL,
    category_name VARCHAR(50),      -- 冗余
    -- ...
);

-- 大表修改列可能锁表数小时
-- 使用pt-online-schema-change等工具在线修改

-- 或使用gh-ost工具
gh-ost --user=root --password=xxx --host=localhost \
       --database=testdb --table=users \
       --alter="ADD COLUMN age TINYINT" \
       --execute

-- 错误:所有字符串都设为VARCHAR(255)
CREATE TABLE users (
    username VARCHAR(255),  -- 过长
    email VARCHAR(255),     -- 过长
    phone VARCHAR(255)      -- 应该是CHAR(11)
);

-- 正确:根据实际需求设置长度
CREATE TABLE users (
    username VARCHAR(50),   -- 用户名通常不超过50字符
    email VARCHAR(100),     -- 邮箱通常不超过100字符
    phone CHAR(11)          -- 手机号固定11位
);

-- 错误:大量NULL
CREATE TABLE users (
    username VARCHAR(50),     -- 允许NULL
    email VARCHAR(100),       -- 允许NULL
    phone VARCHAR(20)         -- 允许NULL
);

-- 正确:明确NOT NULL和DEFAULT
CREATE TABLE users (
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) DEFAULT '' COMMENT '默认为空字符串'
);

-- 错误:过多索引
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT,
    product_id BIGINT,
    amount DECIMAL(10,2),
    status TINYINT,
    created_at TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_product_id (product_id),
    INDEX idx_amount (amount),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_user_product (user_id, product_id),
    INDEX idx_user_status (user_id, status)
    -- ... 索引过多
);

-- 正确:合理索引
CREATE TABLE orders (
    -- ...
    
    INDEX idx_user_id (user_id),        -- 高频查询
    INDEX idx_product_id (product_id),  -- 高频查询
    INDEX idx_user_status_time (user_id, status, created_at)  -- 复合索引
);

-- 错误:使用默认字符集(可能是latin1)
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50)
);

-- 正确:明确指定字符集
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 错误:直接ALTER大表
ALTER TABLE users ADD COLUMN age TINYINT;  -- 可能锁表数小时

-- 正确:使用在线DDL工具
-- 1. 使用pt-online-schema-change
-- 2. 使用gh-ost
-- 3. 使用MySQL 8.0的即时DDL(INSTANT ADD COLUMN)

START TRANSACTION;

INSERT INTO users (username) VALUES ('tom');  -- DML,可以回滚

CREATE TABLE test_table (id INT);  -- DDL,隐式提交,无法回滚

ROLLBACK;  -- 只能回滚INSERT,CREATE TABLE已提交

-- DDL操作会锁定表
-- 在DDL执行期间,表被锁定,其他会话无法读写

-- 大表DDL可能导致长时间锁表
ALTER TABLE big_table ADD COLUMN new_col INT;  -- 可能锁表数小时

-- 使用在线DDL(MySQL 5.6+)
ALTER TABLE big_table ADD COLUMN new_col INT, ALGORITHM=INPLACE, LOCK=NONE;

-- 查看DDL进度(MySQL 8.0+)
SHOW PROCESSLIST;

-- 查看DDL进度详情
SELECT * FROM performance_schema.setup_actors;

WHERE name = 'tom'
WHERE name = 'tom' AND age = 25
WHERE name = 'tom' AND age = 25 AND city = '北京'

WHERE age = 25              -- 跳过了name
WHERE city = '北京'         -- 跳过了name和age
WHERE name = 'tom' AND city = '北京'  -- 跳过了age,只能用到name