【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 ASSELECT * FROM posts_shard_0UNION ALLSELECT * FROM posts_shard_1UNION ALLSELECT * FROM posts_shard_2UNION ALLSELECT * 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
)
BEGINSELECT ci.content_id, ci.title, ci.slug, ci.published_at, u.username as author_name, ci.featured_imageFROM content_items ciJOIN users u ON ci.author_id = u.user_idJOIN content_categories cc ON ci.content_id = cc.content_idWHERE ci.content_type_id = p_content_type_idAND ci.status = 'published'AND cc.category_id = p_category_idORDER BY ci.published_at DESCLIMIT 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)
BEGININSERT 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_timestampFROM sensor_readingsWHERE DATE(timestamp) = p_dateGROUP BY sensor_id, DATE(timestamp)ON DUPLICATE KEY UPDATEmin_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()
BEGINDECLARE 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)
BEGINDECLARE archive_date DATE;SET archive_date = DATE_SUB(CURDATE(), INTERVAL p_months_to_keep MONTH);-- 归档数据到历史表INSERT INTO sensor_readings_archiveSELECT * FROM sensor_readingsWHERE DATE(timestamp) < archive_date;-- 删除已归档的数据DELETE FROM sensor_readingsWHERE 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)
)
BEGINDECLARE 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 SQLEXCEPTIONBEGINSET 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_currencyFROM accountsWHERE account_id = p_source_account_id AND status = 'active'FOR UPDATE;IF v_source_balance IS NULL THENSET p_success = FALSE;SET p_message = 'Source account invalid or inactive';LEAVE sp;END IF;SELECT currency_code INTO v_dest_currencyFROM accountsWHERE account_id = p_destination_account_id AND status = 'active'FOR UPDATE;IF v_dest_currency IS NULL THENSET p_success = FALSE;SET p_message = 'Destination account invalid or inactive';LEAVE sp;END IF;-- 检查余额是否足够IF v_source_balance < p_amount THENSET 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;ELSESET 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) 

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