【MySQL】项目实践

个人主页：Guiat
归属专栏：MySQL

正文

1. 项目实践概述

MySQL在实际项目中发挥着至关重要的作用，良好的数据库设计是项目成功的基础。本文将展示MySQL在各类项目中的应用实践与最佳实践。

1.1 项目实践的重要性

• 理论与实践相结合的必要性
• 数据库设计决定了应用性能上限
• 预先规划避免后期重构成本
• 从真实案例中学习最佳实践

1.2 项目中MySQL的典型应用场景

2. 数据库设计流程

2.1 需求分析与规划

• 识别系统的实体和关系
• 确定数据流和访问模式
• 评估数据量和增长预期
• 定义业务规则和约束条件

2.2 设计过程示例

2.3 数据库设计工具

• MySQL Workbench
• dbdiagram.io
• ERDPlus
• Lucidchart
• Vertabelo
• Visual Paradigm

3. 电子商务平台实践案例

3.1 系统架构

3.2 数据库Schema设计

-- 用户及账户相关表
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(20) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    INDEX idx_email (email),
    INDEX idx_username (username)
);

CREATE TABLE user_addresses (
    address_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    address_type ENUM('shipping', 'billing', 'both') DEFAULT 'both',
    recipient_name VARCHAR(100) NOT NULL,
    street_address VARCHAR(255) NOT NULL,
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100) NOT NULL,
    postal_code VARCHAR(20) NOT NULL,
    country VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    is_default BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id)
);

-- 产品相关表
CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    parent_id INT NULL,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    image_url VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    display_order INT DEFAULT 0,
    FOREIGN KEY (parent_id) REFERENCES categories(category_id) ON DELETE SET NULL,
    INDEX idx_parent_id (parent_id),
    INDEX idx_slug (slug)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    short_description VARCHAR(500),
    regular_price DECIMAL(10, 2) NOT NULL,
    sale_price DECIMAL(10, 2),
    cost DECIMAL(10, 2),
    stock_quantity INT NOT NULL DEFAULT 0,
    is_featured BOOLEAN DEFAULT FALSE,
    is_digital BOOLEAN DEFAULT FALSE,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_sku (sku),
    INDEX idx_slug (slug),
    INDEX idx_status (status),
    INDEX idx_is_featured (is_featured)
);

CREATE TABLE product_categories (
    product_id INT NOT NULL,
    category_id INT NOT NULL,
    PRIMARY KEY (product_id, category_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE CASCADE
);

CREATE TABLE product_images (
    image_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    image_url VARCHAR(255) NOT NULL,
    alt_text VARCHAR(255),
    is_primary BOOLEAN DEFAULT FALSE,
    display_order INT DEFAULT 0,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    INDEX idx_product_id (product_id)
);

CREATE TABLE product_attributes (
    attribute_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    display_name VARCHAR(100) NOT NULL,
    type ENUM('text', 'number', 'boolean', 'select') NOT NULL DEFAULT 'text'
);

CREATE TABLE product_attribute_values (
    value_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    attribute_id INT NOT NULL,
    value TEXT NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    FOREIGN KEY (attribute_id) REFERENCES product_attributes(attribute_id) ON DELETE CASCADE,
    UNIQUE KEY (product_id, attribute_id),
    INDEX idx_product_id (product_id),
    INDEX idx_attribute_id (attribute_id)
);

-- 订单相关表
CREATE TABLE carts (
    cart_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NULL,
    session_id VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_session_id (session_id)
);

CREATE TABLE cart_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    cart_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (cart_id) REFERENCES carts(cart_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    UNIQUE KEY (cart_id, product_id),
    INDEX idx_cart_id (cart_id)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_number VARCHAR(50) NOT NULL UNIQUE,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled', 'refunded') DEFAULT 'pending',
    total_amount DECIMAL(10, 2) NOT NULL,
    tax_amount DECIMAL(10, 2) NOT NULL DEFAULT 0,
    shipping_amount DECIMAL(10, 2) NOT NULL DEFAULT 0,
    discount_amount DECIMAL(10, 2) NOT NULL DEFAULT 0,
    shipping_address_id INT NOT NULL,
    billing_address_id INT NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    shipping_method VARCHAR(50) NOT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE RESTRICT,
    FOREIGN KEY (shipping_address_id) REFERENCES user_addresses(address_id) ON DELETE RESTRICT,
    FOREIGN KEY (billing_address_id) REFERENCES user_addresses(address_id) ON DELETE RESTRICT,
    INDEX idx_user_id (user_id),
    INDEX idx_order_number (order_number),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

CREATE TABLE order_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    subtotal DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT,
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id)
);

CREATE TABLE payments (
    payment_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    transaction_id VARCHAR(100) UNIQUE,
    status ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    INDEX idx_order_id (order_id),
    INDEX idx_transaction_id (transaction_id)
);

3.3 数据访问模式分析

3.4 性能优化策略

4. 社交媒体平台实践案例

4.1 数据模型设计

-- 用户系统
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    bio TEXT,
    profile_image VARCHAR(255),
    cover_image VARCHAR(255),
    website VARCHAR(255),
    location VARCHAR(100),
    is_verified BOOLEAN DEFAULT FALSE,
    is_private BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_active TIMESTAMP,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_status (status)
);

-- 关注关系
CREATE TABLE follows (
    follow_id INT PRIMARY KEY AUTO_INCREMENT,
    follower_id INT NOT NULL,
    following_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending', 'accepted', 'rejected', 'blocked') DEFAULT 'accepted',
    FOREIGN KEY (follower_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (following_id) REFERENCES users(user_id) ON DELETE CASCADE,
    UNIQUE KEY (follower_id, following_id),
    INDEX idx_follower_id (follower_id),
    INDEX idx_following_id (following_id),
    INDEX idx_status (status)
);

-- 内容发布
CREATE TABLE posts (
    post_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    content TEXT,
    has_media BOOLEAN DEFAULT FALSE,
    location VARCHAR(255),
    privacy ENUM('public', 'followers', 'private') DEFAULT 'public',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_edited BOOLEAN DEFAULT FALSE,
    is_archived BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at),
    INDEX idx_privacy (privacy)
);

CREATE TABLE post_media (
    media_id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT NOT NULL,
    media_type ENUM('image', 'video', 'audio', 'document') NOT NULL,
    media_url VARCHAR(255) NOT NULL,
    thumbnail_url VARCHAR(255),
    alt_text VARCHAR(255),
    width INT,
    height INT,
    duration INT,
    display_order INT DEFAULT 0,
    FOREIGN KEY (post_id) REFERENCES posts(post_id) ON DELETE CASCADE,
    INDEX idx_post_id (post_id),
    INDEX idx_media_type (media_type)
);

-- 互动功能
CREATE TABLE comments (
    comment_id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    parent_comment_id INT,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_edited BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (post_id) REFERENCES posts(post_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (parent_comment_id) REFERENCES comments(comment_id) ON DELETE SET NULL,
    INDEX idx_post_id (post_id),
    INDEX idx_user_id (user_id),
    INDEX idx_parent_comment_id (parent_comment_id)
);

CREATE TABLE likes (
    like_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    likeable_type ENUM('post', 'comment') NOT NULL,
    likeable_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    UNIQUE KEY (user_id, likeable_type, likeable_id),
    INDEX idx_user_id (user_id),
    INDEX idx_likeable (likeable_type, likeable_id)
);

-- 消息系统
CREATE TABLE conversations (
    conversation_id INT PRIMARY KEY AUTO_INCREMENT,
    type ENUM('direct', 'group') NOT NULL DEFAULT 'direct',
    title VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_type (type)
);

CREATE TABLE conversation_participants (
    participant_id INT PRIMARY KEY AUTO_INCREMENT,
    conversation_id INT NOT NULL,
    user_id INT NOT NULL,
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_admin BOOLEAN DEFAULT FALSE,
    last_read_at TIMESTAMP,
    status ENUM('active', 'left', 'removed') DEFAULT 'active',
    FOREIGN KEY (conversation_id) REFERENCES conversations(conversation_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    UNIQUE KEY (conversation_id, user_id),
    INDEX idx_conversation_id (conversation_id),
    INDEX idx_user_id (user_id)
);

CREATE TABLE messages (
    message_id INT PRIMARY KEY AUTO_INCREMENT,
    conversation_id INT NOT NULL,
    sender_id INT NOT NULL,
    message_type ENUM('text', 'image', 'video', 'audio', 'file', 'location', 'system') NOT NULL DEFAULT 'text',
    content TEXT,
    media_url VARCHAR(255),
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_edited BOOLEAN DEFAULT FALSE,
    edited_at TIMESTAMP,
    FOREIGN KEY (conversation_id) REFERENCES conversations(conversation_id) ON DELETE CASCADE,
    FOREIGN KEY (sender_id) REFERENCES users(user_id) ON DELETE CASCADE,
    INDEX idx_conversation_id (conversation_id),
    INDEX idx_sender_id (sender_id),
    INDEX idx_sent_at (sent_at)
);

4.2 分区策略

4.3 高并发处理方案

4.4 数据库扩展方案

-- 分区表示例 - 按创建时间分区的消息表
CREATE TABLE messages_partitioned (
    message_id INT NOT NULL,
    conversation_id INT NOT NULL,
    sender_id INT NOT NULL,
    message_type ENUM('text', 'image', 'video', 'audio', 'file', 'location', 'system') NOT NULL DEFAULT 'text',
    content TEXT,
    media_url VARCHAR(255),
    sent_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_edited BOOLEAN DEFAULT FALSE,
    edited_at TIMESTAMP,
    PRIMARY KEY (message_id, sent_at),
    INDEX idx_conversation_id (conversation_id),
    INDEX idx_sender_id (sender_id)
) PARTITION BY RANGE (UNIX_TIMESTAMP(sent_at)) (
    PARTITION p_2022_01 VALUES LESS THAN (UNIX_TIMESTAMP('2022-02-01 00:00:00')),
    PARTITION p_2022_02 VALUES LESS THAN (UNIX_TIMESTAMP('2022-03-01 00:00:00')),
    PARTITION p_2022_03 VALUES LESS THAN (UNIX_TIMESTAMP('2022-04-01 00:00:00')),
    -- ... 更多分区
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 分区维护 - 添加新分区
ALTER TABLE messages_partitioned ADD PARTITION (
    PARTITION p_2022_04 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-01 00:00:00'))
);

-- 水平分表 - 按用户ID Hash分片的示例
-- 在用户表中增加分片键
ALTER TABLE users ADD COLUMN shard_key INT GENERATED ALWAYS AS (user_id % 4) STORED;

-- 创建分片表
CREATE TABLE posts_shard_0 LIKE posts;
CREATE TABLE posts_shard_1 LIKE posts;
CREATE TABLE posts_shard_2 LIKE posts;
CREATE TABLE posts_shard_3 LIKE posts;

-- 分片路由视图
CREATE VIEW posts_view AS
    SELECT * FROM posts_shard_0
    UNION ALL
    SELECT * FROM posts_shard_1
    UNION ALL
    SELECT * FROM posts_shard_2
    UNION ALL
    SELECT * FROM posts_shard_3;

5. 内容管理系统实践案例

5.1 数据库Schema设计

-- 用户和权限系统
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_status (status)
);

CREATE TABLE roles (
    role_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE permissions (
    permission_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE role_permissions (
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(role_id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(permission_id) ON DELETE CASCADE
);

CREATE TABLE user_roles (
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(role_id) ON DELETE CASCADE
);

-- 内容结构
CREATE TABLE content_types (
    content_type_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    slug VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE content_fields (
    field_id INT PRIMARY KEY AUTO_INCREMENT,
    content_type_id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    field_type ENUM('text', 'longtext', 'number', 'date', 'boolean', 'image', 'file', 'relation') NOT NULL,
    is_required BOOLEAN DEFAULT FALSE,
    default_value TEXT,
    display_order INT DEFAULT 0,
    FOREIGN KEY (content_type_id) REFERENCES content_types(content_type_id) ON DELETE CASCADE,
    UNIQUE KEY (content_type_id, name),
    INDEX idx_content_type_id (content_type_id)
);

CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    parent_id INT NULL,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (parent_id) REFERENCES categories(category_id) ON DELETE SET NULL,
    INDEX idx_parent_id (parent_id),
    INDEX idx_slug (slug)
);

CREATE TABLE tags (
    tag_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    slug VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    INDEX idx_slug (slug)
);

-- 内容项
CREATE TABLE content_items (
    content_id INT PRIMARY KEY AUTO_INCREMENT,
    content_type_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    author_id INT NOT NULL,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    published_at TIMESTAMP NULL,
    featured_image VARCHAR(255),
    excerpt TEXT,
    FOREIGN KEY (content_type_id) REFERENCES content_types(content_type_id) ON DELETE RESTRICT,
    FOREIGN KEY (author_id) REFERENCES users(user_id) ON DELETE RESTRICT,
    INDEX idx_content_type_id (content_type_id),
    INDEX idx_author_id (author_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_published_at (published_at),
    INDEX idx_slug (slug)
);

CREATE TABLE content_fields_data (
    data_id INT PRIMARY KEY AUTO_INCREMENT,
    content_id INT NOT NULL,
    field_id INT NOT NULL,
    field_value TEXT,
    FOREIGN KEY (content_id) REFERENCES content_items(content_id) ON DELETE CASCADE,
    FOREIGN KEY (field_id) REFERENCES content_fields(field_id) ON DELETE CASCADE,
    UNIQUE KEY (content_id, field_id),
    INDEX idx_content_id (content_id),
    INDEX idx_field_id (field_id)
);

CREATE TABLE content_categories (
    content_id INT NOT NULL,
    category_id INT NOT NULL,
    PRIMARY KEY (content_id, category_id),
    FOREIGN KEY (content_id) REFERENCES content_items(content_id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE CASCADE,
    INDEX idx_content_id (content_id),
    INDEX idx_category_id (category_id)
);

CREATE TABLE content_tags (
    content_id INT NOT NULL,
    tag_id INT NOT NULL,
    PRIMARY KEY (content_id, tag_id),
    FOREIGN KEY (content_id) REFERENCES content_items(content_id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(tag_id) ON DELETE CASCADE,
    INDEX idx_content_id (content_id),
    INDEX idx_tag_id (tag_id)
);

-- 评论系统
CREATE TABLE comments (
    comment_id INT PRIMARY KEY AUTO_INCREMENT,
    content_id INT NOT NULL,
    parent_id INT NULL,
    author_id INT NULL,
    author_name VARCHAR(100),
    author_email VARCHAR(100),
    author_ip VARCHAR(45),
    comment_text TEXT NOT NULL,
    status ENUM('pending', 'approved', 'spam', 'trash') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (content_id) REFERENCES content_items(content_id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES comments(comment_id) ON DELETE SET NULL,
    FOREIGN KEY (author_id) REFERENCES users(user_id) ON DELETE SET NULL,
    INDEX idx_content_id (content_id),
    INDEX idx_parent_id (parent_id),
    INDEX idx_author_id (author_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

-- 媒体库
CREATE TABLE media (
    media_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    file_type VARCHAR(100) NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    file_size INT NOT NULL,
    width INT,
    height INT,
    alt_text VARCHAR(255),
    caption TEXT,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE RESTRICT,
    INDEX idx_user_id (user_id),
    INDEX idx_file_type (file_type),
    INDEX idx_uploaded_at (uploaded_at)
);

-- 版本控制
CREATE TABLE content_revisions (
    revision_id INT PRIMARY KEY AUTO_INCREMENT,
    content_id INT NOT NULL,
    user_id INT NOT NULL,
    revision_data JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    revision_note VARCHAR(255),
    FOREIGN KEY (content_id) REFERENCES content_items(content_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE RESTRICT,
    INDEX idx_content_id (content_id),
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at)
);

-- 网站设置
CREATE TABLE settings (
    setting_id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    is_system BOOLEAN DEFAULT FALSE,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updated_by INT,
    FOREIGN KEY (updated_by) REFERENCES users(user_id) ON DELETE SET NULL,
    INDEX idx_setting_key (setting_key)
);

-- 菜单管理
CREATE TABLE menus (
    menu_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE menu_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    menu_id INT NOT NULL,
    parent_id INT NULL,
    title VARCHAR(100) NOT NULL,
    url VARCHAR(255),
    target VARCHAR(20) DEFAULT '_self',
    icon VARCHAR(50),
    content_id INT NULL,
    order_position INT DEFAULT 0,
    FOREIGN KEY (menu_id) REFERENCES menus(menu_id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES menu_items(item_id) ON DELETE SET NULL,
    FOREIGN KEY (content_id) REFERENCES content_items(content_id) ON DELETE SET NULL,
    INDEX idx_menu_id (menu_id),
    INDEX idx_parent_id (parent_id)
);

5.2 内容类型与字段设计

5.3 内容权限管理

5.4 查询优化与索引策略

-- 优化内容检索的复合索引
ALTER TABLE content_items
ADD INDEX idx_type_status_date (content_type_id, status, published_at);

-- 优化标签搜索的索引
ALTER TABLE content_tags
ADD INDEX idx_tag_content (tag_id, content_id);

-- 全文搜索索引
ALTER TABLE content_items
ADD FULLTEXT INDEX ft_content (title, excerpt);

-- 分析常见查询模式并优化
EXPLAIN SELECT ci.content_id, ci.title, ci.slug, ci.published_at, u.username
FROM content_items ci
JOIN users u ON ci.author_id = u.user_id
JOIN content_categories cc ON ci.content_id = cc.content_id
WHERE ci.content_type_id = 1 
AND ci.status = 'published'
AND cc.category_id = 5
ORDER BY ci.published_at DESC
LIMIT 10;

-- 查询优化后的存储过程示例
DELIMITER //
CREATE PROCEDURE get_latest_content_by_category(
    IN p_category_id INT,
    IN p_content_type_id INT,
    IN p_limit INT
)
BEGIN
    SELECT ci.content_id, ci.title, ci.slug, ci.published_at, 
           u.username as author_name, ci.featured_image
    FROM content_items ci
    JOIN users u ON ci.author_id = u.user_id
    JOIN content_categories cc ON ci.content_id = cc.content_id
    WHERE ci.content_type_id = p_content_type_id
    AND ci.status = 'published'
    AND cc.category_id = p_category_id
    ORDER BY ci.published_at DESC
    LIMIT p_limit;
END //
DELIMITER ;

6. 物联网数据管理实践案例

6.1 系统架构

6.2 数据库Schema设计

-- 设备管理
CREATE TABLE devices (
    device_id INT PRIMARY KEY AUTO_INCREMENT,
    device_uuid VARCHAR(36) NOT NULL UNIQUE,
    device_type_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    firmware_version VARCHAR(50),
    hardware_version VARCHAR(50),
    ip_address VARCHAR(45),
    mac_address VARCHAR(17),
    location_id INT,
    status ENUM('active', 'inactive', 'maintenance', 'error') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_online TIMESTAMP NULL,
    INDEX idx_device_uuid (device_uuid),
    INDEX idx_device_type_id (device_type_id),
    INDEX idx_status (status),
    INDEX idx_location_id (location_id),
    INDEX idx_last_online (last_online)
);

CREATE TABLE device_types (
    device_type_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    properties JSON
);

CREATE TABLE locations (
    location_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    address VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(100),
    country VARCHAR(100),
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    parent_location_id INT NULL,
    FOREIGN KEY (parent_location_id) REFERENCES locations(location_id) ON DELETE SET NULL,
    INDEX idx_parent_location_id (parent_location_id),
    INDEX idx_coordinates (latitude, longitude)
);

-- 传感器数据模型
CREATE TABLE sensors (
    sensor_id INT PRIMARY KEY AUTO_INCREMENT,
    device_id INT NOT NULL,
    sensor_type VARCHAR(50) NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    unit VARCHAR(20),
    min_value DECIMAL(10, 2),
    max_value DECIMAL(10, 2),
    precision_digits INT DEFAULT 2,
    status ENUM('active', 'inactive', 'error') DEFAULT 'active',
    FOREIGN KEY (device_id) REFERENCES devices(device_id) ON DELETE CASCADE,
    INDEX idx_device_id (device_id),
    INDEX idx_sensor_type (sensor_type)
);

-- 采用分区表存储时序数据
CREATE TABLE sensor_readings (
    reading_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    sensor_id INT NOT NULL,
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    value DECIMAL(15, 5) NOT NULL,
    quality TINYINT DEFAULT 100,
    FOREIGN KEY (sensor_id) REFERENCES sensors(sensor_id) ON DELETE CASCADE,
    INDEX idx_sensor_timestamp (sensor_id, timestamp)
) PARTITION BY RANGE (UNIX_TIMESTAMP(timestamp)) (
    PARTITION p_history VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-01 00:00:00')),
    PARTITION p_2023_q1 VALUES LESS THAN (UNIX_TIMESTAMP('2023-04-01 00:00:00')),
    PARTITION p_2023_q2 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-01 00:00:00')),
    PARTITION p_2023_q3 VALUES LESS THAN (UNIX_TIMESTAMP('2023-10-01 00:00:00')),
    PARTITION p_2023_q4 VALUES LESS THAN (UNIX_TIMESTAMP('2024-01-01 00:00:00')),
    PARTITION p_current VALUES LESS THAN MAXVALUE
);

-- 创建每日聚合数据表
CREATE TABLE sensor_daily_aggregations (
    aggregation_id INT PRIMARY KEY AUTO_INCREMENT,
    sensor_id INT NOT NULL,
    date DATE NOT NULL,
    min_value DECIMAL(15, 5),
    max_value DECIMAL(15, 5),
    avg_value DECIMAL(15, 5),
    sum_value DECIMAL(20, 5),
    count_readings INT,
    first_reading_timestamp TIMESTAMP,
    last_reading_timestamp TIMESTAMP,
    FOREIGN KEY (sensor_id) REFERENCES sensors(sensor_id) ON DELETE CASCADE,
    UNIQUE KEY (sensor_id, date),
    INDEX idx_date (date)
);

-- 警报配置和历史
CREATE TABLE alert_rules (
    rule_id INT PRIMARY KEY AUTO_INCREMENT,
    sensor_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    condition_type ENUM('threshold_high', 'threshold_low', 'rate_of_change', 'no_data', 'custom') NOT NULL,
    threshold_value DECIMAL(15, 5),
    comparison_operator VARCHAR(10),
    duration_seconds INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    severity ENUM('info', 'warning', 'error', 'critical') DEFAULT 'warning',
    notification_channels JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (sensor_id) REFERENCES sensors(sensor_id) ON DELETE CASCADE,
    INDEX idx_sensor_id (sensor_id),
    INDEX idx_is_active (is_active)
);

CREATE TABLE alert_history (
    alert_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    rule_id INT NOT NULL,
    sensor_id INT NOT NULL,
    triggered_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP NULL,
    duration_seconds INT NULL,
    trigger_value DECIMAL(15, 5),
    message TEXT,
    acknowledged BOOLEAN DEFAULT FALSE,
    acknowledged_by INT NULL,
    acknowledged_at TIMESTAMP NULL,
    FOREIGN KEY (rule_id) REFERENCES alert_rules(rule_id) ON DELETE CASCADE,
    FOREIGN KEY (sensor_id) REFERENCES sensors(sensor_id) ON DELETE CASCADE,
    INDEX idx_rule_id (rule_id),
    INDEX idx_sensor_id (sensor_id),
    INDEX idx_triggered_at (triggered_at),
    INDEX idx_resolved_at (resolved_at),
    INDEX idx_acknowledged (acknowledged)
) PARTITION BY RANGE (UNIX_TIMESTAMP(triggered_at)) (
    PARTITION p_2023_h1 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-01 00:00:00')),
    PARTITION p_2023_h2 VALUES LESS THAN (UNIX_TIMESTAMP('2024-01-01 00:00:00')),
    PARTITION p_current VALUES LESS THAN MAXVALUE
);

6.3 时序数据处理

6.4 数据聚合与压缩策略

-- 创建自动聚合存储过程
DELIMITER //
CREATE PROCEDURE aggregate_daily_sensor_data(IN p_date DATE)
BEGIN
    INSERT INTO sensor_daily_aggregations
        (sensor_id, date, min_value, max_value, avg_value, sum_value, 
         count_readings, first_reading_timestamp, last_reading_timestamp)
    SELECT 
        sensor_id,
        DATE(timestamp) AS reading_date,
        MIN(value) AS min_value,
        MAX(value) AS max_value,
        AVG(value) AS avg_value,
        SUM(value) AS sum_value,
        COUNT(*) AS count_readings,
        MIN(timestamp) AS first_reading_timestamp,
        MAX(timestamp) AS last_reading_timestamp
    FROM sensor_readings
    WHERE DATE(timestamp) = p_date
    GROUP BY sensor_id, DATE(timestamp)
    ON DUPLICATE KEY UPDATE
        min_value = VALUES(min_value),
        max_value = VALUES(max_value),
        avg_value = VALUES(avg_value),
        sum_value = VALUES(sum_value),
        count_readings = VALUES(count_readings),
        first_reading_timestamp = VALUES(first_reading_timestamp),
        last_reading_timestamp = VALUES(last_reading_timestamp);
END //
DELIMITER ;

-- 创建动态分区管理存储过程
DELIMITER //
CREATE PROCEDURE manage_sensor_readings_partitions()
BEGIN
    DECLARE next_quarter_start DATE;
    DECLARE partition_name VARCHAR(100);
    DECLARE partition_timestamp BIGINT;
    
    -- 确定下一个季度开始时间
    SET next_quarter_start = DATE_ADD(
        DATE(CONCAT(YEAR(CURDATE()), '-', 
              CASE 
                  WHEN MONTH(CURDATE()) BETWEEN 1 AND 3 THEN '04-01'
                  WHEN MONTH(CURDATE()) BETWEEN 4 AND 6 THEN '07-01'
                  WHEN MONTH(CURDATE()) BETWEEN 7 AND 9 THEN '10-01'
                  ELSE CONCAT(YEAR(CURDATE())+1, '-01-01')
              END)), 
        INTERVAL 3 MONTH);
        
    -- 检查是否已有下下个季度的分区
    SET partition_name = CONCAT('p_', YEAR(next_quarter_start), '_q', 
                               CEILING(MONTH(next_quarter_start) / 3));
    SET partition_timestamp = UNIX_TIMESTAMP(next_quarter_start);
    
    -- 添加新分区
    SET @sql = CONCAT('ALTER TABLE sensor_readings REORGANIZE PARTITION p_current INTO (',
                      'PARTITION ', partition_name, ' VALUES LESS THAN (', partition_timestamp, '),',
                      'PARTITION p_current VALUES LESS THAN MAXVALUE)');
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- 清理旧数据或归档旧分区
    -- 实际项目中可能将数据归档到其他表或存储系统
END //
DELIMITER ;

-- 创建数据清理或归档处理存储过程
DELIMITER //
CREATE PROCEDURE archive_sensor_data(IN p_months_to_keep INT)
BEGIN
    DECLARE archive_date DATE;
    SET archive_date = DATE_SUB(CURDATE(), INTERVAL p_months_to_keep MONTH);
    
    -- 归档数据到历史表
    INSERT INTO sensor_readings_archive
    SELECT * FROM sensor_readings
    WHERE DATE(timestamp) < archive_date;
    
    -- 删除已归档的数据
    DELETE FROM sensor_readings
    WHERE DATE(timestamp) < archive_date;
    
    -- 通过事件调度器定期执行
END //
DELIMITER ;

7. 金融系统实践案例

7.1 交易数据模型

-- 账户管理
CREATE TABLE accounts (
    account_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    account_number VARCHAR(20) NOT NULL UNIQUE,
    account_type ENUM('checking', 'savings', 'credit', 'investment') NOT NULL,
    currency_code CHAR(3) NOT NULL DEFAULT 'USD',
    current_balance DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    available_balance DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    status ENUM('active', 'inactive', 'frozen', 'closed') DEFAULT 'active',
    opened_date DATE NOT NULL,
    closed_date DATE NULL,
    last_activity_date TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_account_number (account_number),
    INDEX idx_status (status),
    CHECK (current_balance >= available_balance OR account_type = 'credit')
);

-- 事务使用分区表管理大量交易记录
CREATE TABLE transactions (
    transaction_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    transaction_reference VARCHAR(36) NOT NULL UNIQUE,
    account_id INT NOT NULL,
    transaction_type ENUM('deposit', 'withdrawal', 'transfer_in', 'transfer_out', 'payment', 'fee', 'interest', 'adjustment') NOT NULL,
    amount DECIMAL(15, 2) NOT NULL,
    running_balance DECIMAL(15, 2) NOT NULL,
    status ENUM('pending', 'completed', 'failed', 'reversed') DEFAULT 'pending',
    description VARCHAR(255) NOT NULL,
    metadata JSON,
    transaction_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    cleared_date TIMESTAMP NULL,
    created_by VARCHAR(50) NOT NULL,
    FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE RESTRICT,
    INDEX idx_account_id (account_id),
    INDEX idx_transaction_reference (transaction_reference),
    INDEX idx_transaction_date (transaction_date),
    INDEX idx_status (status)
) PARTITION BY RANGE (YEAR(transaction_date) * 100 + MONTH(transaction_date)) (
    PARTITION p_history VALUES LESS THAN (202301),
    PARTITION p_2023_01 VALUES LESS THAN (202302),
    PARTITION p_2023_02 VALUES LESS THAN (202303),
    PARTITION p_2023_03 VALUES LESS THAN (202304),
    -- 更多月份分区
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 转账交易关联表
CREATE TABLE transfers (
    transfer_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    source_transaction_id BIGINT NOT NULL,
    destination_transaction_id BIGINT NOT NULL,
    amount DECIMAL(15, 2) NOT NULL,
    fee_amount DECIMAL(15, 2) DEFAULT 0.00,
    exchange_rate DECIMAL(15, 6) DEFAULT 1.000000,
    transfer_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending', 'completed', 'failed', 'cancelled') DEFAULT 'pending',
    FOREIGN KEY (source_transaction_id) REFERENCES transactions(transaction_id) ON DELETE RESTRICT,
    FOREIGN KEY (destination_transaction_id) REFERENCES transactions(transaction_id) ON DELETE RESTRICT,
    INDEX idx_source (source_transaction_id),
    INDEX idx_destination (destination_transaction_id),
    INDEX idx_transfer_date (transfer_date),
    INDEX idx_status (status)
);

-- 余额变更审计表
CREATE TABLE balance_audit (
    audit_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    account_id INT NOT NULL,
    transaction_id BIGINT NULL,
    previous_balance DECIMAL(15, 2) NOT NULL,
    new_balance DECIMAL(15, 2) NOT NULL,
    change_amount DECIMAL(15, 2) NOT NULL,
    change_type VARCHAR(50) NOT NULL,
    changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    changed_by VARCHAR(50) NOT NULL,
    FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE RESTRICT,
    FOREIGN KEY (transaction_id) REFERENCES transactions(transaction_id) ON DELETE SET NULL,
    INDEX idx_account_id (account_id),
    INDEX idx_transaction_id (transaction_id),
    INDEX idx_changed_at (changed_at)
);

7.2 事务一致性实现

7.3 账户转账实现示例

-- 转账存储过程示例
DELIMITER //
CREATE PROCEDURE transfer_funds(
    IN p_source_account_id INT,
    IN p_destination_account_id INT,
    IN p_amount DECIMAL(15, 2),
    IN p_description VARCHAR(255),
    IN p_user VARCHAR(50),
    OUT p_transfer_id BIGINT,
    OUT p_success BOOLEAN,
    OUT p_message VARCHAR(255)
)
BEGIN
    DECLARE v_source_balance DECIMAL(15, 2);
    DECLARE v_source_currency CHAR(3);
    DECLARE v_dest_currency CHAR(3);
    DECLARE v_exchange_rate DECIMAL(15, 6) DEFAULT 1.000000;
    DECLARE v_converted_amount DECIMAL(15, 2);
    DECLARE v_source_transaction_id BIGINT;
    DECLARE v_dest_transaction_id BIGINT;
    DECLARE v_source_reference VARCHAR(36);
    DECLARE v_dest_reference VARCHAR(36);
    DECLARE v_exit_handler BOOLEAN DEFAULT FALSE;
    
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        SET v_exit_handler = TRUE;
        SET p_success = FALSE;
        SET p_message = 'Database error occurred during transfer';
        ROLLBACK;
    END;
    
    -- 生成唯一交易引用
    SET v_source_reference = UUID();
    SET v_dest_reference = UUID();
    
    -- 验证账户存在且状态正常
    SELECT current_balance, currency_code INTO v_source_balance, v_source_currency
    FROM accounts
    WHERE account_id = p_source_account_id AND status = 'active'
    FOR UPDATE;
    
    IF v_source_balance IS NULL THEN
        SET p_success = FALSE;
        SET p_message = 'Source account invalid or inactive';
        LEAVE sp;
    END IF;
    
    SELECT currency_code INTO v_dest_currency
    FROM accounts
    WHERE account_id = p_destination_account_id AND status = 'active'
    FOR UPDATE;
    
    IF v_dest_currency IS NULL THEN
        SET p_success = FALSE;
        SET p_message = 'Destination account invalid or inactive';
        LEAVE sp;
    END IF;
    
    -- 检查余额是否足够
    IF v_source_balance < p_amount THEN
        SET p_success = FALSE;
        SET p_message = 'Insufficient funds';
        LEAVE sp;
    END IF;
    
    -- 货币转换（如果需要）
    IF v_source_currency != v_dest_currency THEN
        -- 在实际应用中，应该查询实时汇率或汇率表
        -- 这里简化处理
        SET v_exchange_rate = 1.1; -- 示例汇率
        SET v_converted_amount = p_amount * v_exchange_rate;
    ELSE
        SET v_converted_amount = p_amount;
    END IF;
    
    START TRANSACTION;
    
    -- 添加出账交易记录
    INSERT INTO transactions (
        transaction_reference, account_id, transaction_type, 
        amount, running_balance, status, description, created_by
    ) VALUES (
        v_source_reference, p_source_account_id, 'transfer_out',
        -p_amount, v_source_balance - p_amount, 'completed',
        p_description, p_user
    );
    
    SET v_source_transaction_id = LAST_INSERT_ID();
    
    -- 添加入账交易记录
    INSERT INTO transactions (
        transaction_reference, account_id, transaction_type, 
        amount, running_balance, status, description, created_by
    ) 

结语
感谢您的阅读！期待您的一键三连！欢迎指正！