全栈进阶指南

皮肤切换

第 12 章：项目实战 - 构建完整应用数据库 ​

经过前面章节的学习，我们已经掌握了 MySQL 的核心概念和高级特性。现在是时候将这些知识综合运用，构建一个完整的应用程序数据库了。在本章中，我们将设计并实现一个图书管理系统数据库，展示如何将各种 MySQL 特性整合到实际项目中。

本章内容概要 ​

  • 需求分析与数据库设计
  • 表结构实现
  • 数据初始化
  • 常用查询语句编写
  • 性能优化
  • 权限配置
  • 备份策略
  • 文档编写

项目需求分析 ​

我们将创建一个图书管理系统，具备以下功能：

核心功能 ​

  1. 图书管理（添加、编辑、删除、查询图书）
  2. 用户管理（读者、图书管理员）
  3. 借阅管理（借书、还书、续借）
  4. 库存管理（图书采购、报废）
  5. 统计报表（借阅统计、热门图书等）

用户角色 ​

  1. 系统管理员 - 管理用户权限、系统配置
  2. 图书管理员 - 管理图书、处理借还书
  3. 普通读者 - 查询图书、借阅图书

业务规则 ​

  1. 每位读者最多可借阅5本书
  2. 每本书最多可借阅30天
  3. 逾期需缴纳滞纳金（每天0.1元）
  4. 同一图书可有多册副本
  5. 图书可按分类、作者、出版社等维度查询

数据库设计 ​

实体关系分析 ​

主要实体包括：

  • 用户（Users）
  • 图书（Books）
  • 图书副本（Book Copies）
  • 图书分类（Categories）
  • 作者（Authors）
  • 出版社（Publishers）
  • 借阅记录（Borrowing Records）
  • 罚款记录（Fine Records）

ER 图设计 ​

[用户] ----<借阅>---- [图书副本] ----<属于>---- [图书] ----<分类>---- [图书分类]
  |                     |                         |
  |                     |                         +----<作者>---- [作者]
  |                     |
  |                     +----<出版社>---- [出版社]
  |
  +----<罚款>---- [罚款记录]

表结构实现 ​

让我们逐步创建数据库和表结构：

sql
-- 创建图书管理系统数据库
CREATE DATABASE library_management 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE library_management;

-- 1. 用户表
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    real_name VARCHAR(50) NOT NULL,
    id_card VARCHAR(18) UNIQUE,
    user_type ENUM('reader', 'librarian', 'admin') NOT NULL DEFAULT 'reader',
    status ENUM('active', 'inactive', 'banned') NOT NULL DEFAULT 'active',
    max_borrow_count TINYINT NOT NULL DEFAULT 5,
    register_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 2. 图书分类表
CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    parent_id INT DEFAULT NULL,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES categories(category_id)
);

-- 3. 作者表
CREATE TABLE authors (
    author_id INT AUTO_INCREMENT PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL,
    nationality VARCHAR(50),
    birth_date DATE,
    biography TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 4. 出版社表
CREATE TABLE publishers (
    publisher_id INT AUTO_INCREMENT PRIMARY KEY,
    publisher_name VARCHAR(100) NOT NULL,
    address TEXT,
    contact_info VARCHAR(200),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 5. 图书表
CREATE TABLE books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    isbn VARCHAR(20) UNIQUE,
    title VARCHAR(200) NOT NULL,
    subtitle VARCHAR(200),
    category_id INT NOT NULL,
    publisher_id INT,
    publish_date DATE,
    pages INT,
    price DECIMAL(10,2),
    description TEXT,
    cover_image_url VARCHAR(300),
    tags JSON,
    status ENUM('available', 'unavailable') NOT NULL DEFAULT 'available',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(category_id),
    FOREIGN KEY (publisher_id) REFERENCES publishers(publisher_id)
);

-- 6. 图书作者关联表（多对多关系）
CREATE TABLE book_authors (
    book_id INT,
    author_id INT,
    PRIMARY KEY (book_id, author_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id) ON DELETE CASCADE,
    FOREIGN KEY (author_id) REFERENCES authors(author_id) ON DELETE CASCADE
);

-- 7. 图书副本表
CREATE TABLE book_copies (
    copy_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT NOT NULL,
    copy_number VARCHAR(20) NOT NULL,
    location VARCHAR(100),
    acquisition_date DATE,
    condition_status ENUM('new', 'good', 'fair', 'poor') DEFAULT 'good',
    status ENUM('available', 'borrowed', 'reserved', 'maintenance', 'retired') NOT NULL DEFAULT 'available',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

-- 8. 借阅记录表
CREATE TABLE borrowing_records (
    record_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    copy_id INT NOT NULL,
    borrow_date DATE NOT NULL,
    due_date DATE NOT NULL,
    return_date DATE NULL,
    renewed_count TINYINT NOT NULL DEFAULT 0,
    status ENUM('borrowed', 'returned', 'overdue') NOT NULL DEFAULT 'borrowed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (copy_id) REFERENCES book_copies(copy_id)
);

-- 9. 罚款记录表
CREATE TABLE fine_records (
    fine_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    record_id INT,
    fine_amount DECIMAL(10,2) NOT NULL,
    fine_reason VARCHAR(200),
    status ENUM('pending', 'paid', 'waived') NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    paid_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (record_id) REFERENCES borrowing_records(record_id)
);

-- 10. 系统日志表（分区表）
CREATE TABLE system_logs (
    log_id BIGINT AUTO_INCREMENT,
    user_id INT,
    operation VARCHAR(100) NOT NULL,
    table_name VARCHAR(50),
    record_id INT,
    details JSON,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (log_id, created_at)
)
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    PARTITION p202404 VALUES LESS THAN (TO_DAYS('2024-05-01')),
    PARTITION p202405 VALUES LESS THAN (TO_DAYS('2024-06-01')),
    PARTITION p202406 VALUES LESS THAN (TO_DAYS('2024-07-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

创建索引 ​

sql
-- 为常用查询字段创建索引
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_books_title ON books(title);
CREATE INDEX idx_books_isbn ON books(isbn);
CREATE INDEX idx_books_category ON books(category_id);
CREATE INDEX idx_book_copies_status ON book_copies(status);
CREATE INDEX idx_borrowing_user ON borrowing_records(user_id);
CREATE INDEX idx_borrowing_copy ON borrowing_records(copy_id);
CREATE INDEX idx_borrowing_status ON borrowing_records(status);
CREATE INDEX idx_borrowing_dates ON borrowing_records(borrow_date, due_date);
CREATE INDEX idx_fines_user ON fine_records(user_id);
CREATE INDEX idx_fines_status ON fine_records(status);

数据初始化 ​

sql
-- 插入基础数据
-- 1. 插入用户角色
INSERT INTO users (username, password_hash, email, real_name, user_type, register_date) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@library.com', '系统管理员', 'admin', CURDATE()),
('librarian1', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'lib1@library.com', '张管理员', 'librarian', CURDATE()),
('reader1', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'reader1@example.com', '李读者', 'reader', CURDATE());

-- 2. 插入图书分类
INSERT INTO categories (category_name, description) VALUES
('计算机科学', '计算机相关书籍'),
('文学', '文学作品'),
('历史', '历史类书籍'),
('艺术', '艺术类书籍'),
('科学', '科学类书籍'),
('小说', '小说类作品');

-- 3. 插入作者
INSERT INTO authors (author_name, nationality, birth_date) VALUES
('Robert C. Martin', '美国', '1952-12-05'),
('Donald E. Knuth', '美国', '1938-01-10'),
('村上春树', '日本', '1949-01-12'),
('余华', '中国', '1960-04-03');

-- 4. 插入出版社
INSERT INTO publishers (publisher_name, address) VALUES
('人民邮电出版社', '北京市丰台区成寿寺路11号'),
('机械工业出版社', '北京市西城区百万庄大街22号'),
('上海译文出版社', '上海市静安区陕西北路457号');

-- 5. 插入图书
INSERT INTO books (isbn, title, category_id, publisher_id, publish_date, pages, price, description) VALUES
('9787115255352', '代码整洁之道', 1, 1, '2010-01-01', 211, 49.00, '软件工程经典著作'),
('9787111369947', '计算机程序设计艺术', 1, 2, '2012-01-01', 600, 198.00, '算法领域的经典之作'),
('9787532757106', '挪威的森林', 6, 3, '2012-01-01', 380, 28.00, '村上春树代表作'),
('9787506365437', '活着', 6, 1, '2012-01-01', 191, 20.00, '余华代表作');

-- 6. 关联图书和作者
INSERT INTO book_authors (book_id, author_id) VALUES
(1, 1),
(2, 2),
(3, 3),
(4, 4);

-- 7. 插入图书副本
INSERT INTO book_copies (book_id, copy_number, location, acquisition_date) VALUES
(1, 'CS001-001', '计算机区A架1层', '2023-01-15'),
(1, 'CS001-002', '计算机区A架1层', '2023-01-15'),
(2, 'CS002-001', '计算机区B架2层', '2023-02-20'),
(3, 'LIT001-001', '文学区C架3层', '2023-03-10'),
(4, 'LIT002-001', '文学区C架3层', '2023-03-15');

常用查询语句 ​

1. 图书查询 ​

sql
-- 查询所有可用图书及其副本数量
SELECT 
    b.book_id,
    b.title,
    b.isbn,
    c.category_name,
    COUNT(bc.copy_id) AS total_copies,
    COUNT(CASE WHEN bc.status = 'available' THEN 1 END) AS available_copies
FROM books b
JOIN categories c ON b.category_id = c.category_id
LEFT JOIN book_copies bc ON b.book_id = bc.book_id
WHERE b.status = 'available'
GROUP BY b.book_id, b.title, b.isbn, c.category_name
ORDER BY b.title;

-- 根据标题搜索图书
SELECT 
    b.book_id,
    b.title,
    b.isbn,
    GROUP_CONCAT(a.author_name) AS authors,
    p.publisher_name,
    b.publish_date,
    b.price
FROM books b
LEFT JOIN book_authors ba ON b.book_id = ba.book_id
LEFT JOIN authors a ON ba.author_id = a.author_id
LEFT JOIN publishers p ON b.publisher_id = p.publisher_id
WHERE b.title LIKE '%代码%'
GROUP BY b.book_id, b.title, b.isbn, p.publisher_name, b.publish_date, b.price;

2. 借阅管理 ​

sql
-- 查询用户的借阅记录
SELECT 
    br.record_id,
    b.title,
    bc.copy_number,
    br.borrow_date,
    br.due_date,
    br.return_date,
    CASE 
        WHEN br.status = 'overdue' THEN '已逾期'
        WHEN br.status = 'returned' THEN '已归还'
        ELSE '借阅中'
    END AS status_text,
    DATEDIFF(CURDATE(), br.due_date) AS overdue_days
FROM borrowing_records br
JOIN book_copies bc ON br.copy_id = bc.copy_id
JOIN books b ON bc.book_id = b.book_id
WHERE br.user_id = 3
ORDER BY br.borrow_date DESC;

-- 查询当前借阅中的图书
SELECT 
    u.real_name,
    b.title,
    bc.copy_number,
    br.borrow_date,
    br.due_date,
    DATEDIFF(br.due_date, CURDATE()) AS days_remaining
FROM borrowing_records br
JOIN users u ON br.user_id = u.user_id
JOIN book_copies bc ON br.copy_id = bc.copy_id
JOIN books b ON bc.book_id = b.book_id
WHERE br.status = 'borrowed'
ORDER BY br.due_date;

3. 统计报表 ​

sql
-- 图书借阅排行榜
SELECT 
    b.title,
    COUNT(br.record_id) AS borrow_count
FROM books b
JOIN book_copies bc ON b.book_id = bc.book_id
JOIN borrowing_records br ON bc.copy_id = br.copy_id
WHERE br.borrow_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY b.book_id, b.title
ORDER BY borrow_count DESC
LIMIT 10;

-- 用户借阅统计
SELECT 
    u.real_name,
    COUNT(br.record_id) AS total_borrows,
    COUNT(CASE WHEN br.status = 'returned' THEN 1 END) AS returned_count,
    COUNT(CASE WHEN br.status = 'overdue' THEN 1 END) AS overdue_count
FROM users u
LEFT JOIN borrowing_records br ON u.user_id = br.user_id
WHERE u.user_type = 'reader'
GROUP BY u.user_id, u.real_name
ORDER BY total_borrows DESC;

存储过程和函数 ​

1. 借书存储过程 ​

sql
DELIMITER //

CREATE PROCEDURE sp_borrow_book(
    IN p_user_id INT,
    IN p_copy_id INT,
    OUT p_result VARCHAR(100)
)
BEGIN
    DECLARE v_user_status VARCHAR(20);
    DECLARE v_copy_status VARCHAR(20);
    DECLARE v_borrow_count INT;
    DECLARE v_max_borrow_count INT;
    DECLARE v_due_date DATE;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result = '借书失败：系统错误';
    END;
    
    START TRANSACTION;
    
    -- 检查用户状态
    SELECT status, max_borrow_count INTO v_user_status, v_max_borrow_count
    FROM users WHERE user_id = p_user_id;
    
    IF v_user_status != 'active' THEN
        SET p_result = '借书失败：用户状态异常';
        ROLLBACK;
        LEAVE proc;
    END IF;
    
    -- 检查用户借阅数量
    SELECT COUNT(*) INTO v_borrow_count
    FROM borrowing_records
    WHERE user_id = p_user_id AND status = 'borrowed';
    
    IF v_borrow_count >= v_max_borrow_count THEN
        SET p_result = '借书失败：已达最大借阅数量';
        ROLLBACK;
        LEAVE proc;
    END IF;
    
    -- 检查副本状态
    SELECT status INTO v_copy_status
    FROM book_copies WHERE copy_id = p_copy_id;
    
    IF v_copy_status != 'available' THEN
        SET p_result = '借书失败：图书副本不可借阅';
        ROLLBACK;
        LEAVE proc;
    END IF;
    
    -- 计算应还日期（30天后）
    SET v_due_date = DATE_ADD(CURDATE(), INTERVAL 30 DAY);
    
    -- 插入借阅记录
    INSERT INTO borrowing_records (user_id, copy_id, borrow_date, due_date)
    VALUES (p_user_id, p_copy_id, CURDATE(), v_due_date);
    
    -- 更新副本状态
    UPDATE book_copies 
    SET status = 'borrowed' 
    WHERE copy_id = p_copy_id;
    
    -- 记录日志
    INSERT INTO system_logs (user_id, operation, table_name, record_id, details)
    VALUES (p_user_id, 'borrow_book', 'borrowing_records', LAST_INSERT_ID(), 
            JSON_OBJECT('copy_id', p_copy_id, 'due_date', v_due_date));
    
    COMMIT;
    SET p_result = '借书成功';
    
    proc: BEGIN END;
END //

DELIMITER ;

2. 还书存储过程 ​

sql
DELIMITER //

CREATE PROCEDURE sp_return_book(
    IN p_record_id INT,
    OUT p_result VARCHAR(100),
    OUT p_fine_amount DECIMAL(10,2)
)
BEGIN
    DECLARE v_user_id INT;
    DECLARE v_copy_id INT;
    DECLARE v_due_date DATE;
    DECLARE v_borrow_date DATE;
    DECLARE v_overdue_days INT;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result = '还书失败：系统错误';
    END;
    
    START TRANSACTION;
    
    -- 获取借阅记录信息
    SELECT user_id, copy_id, due_date, borrow_date 
    INTO v_user_id, v_copy_id, v_due_date, v_borrow_date
    FROM borrowing_records 
    WHERE record_id = p_record_id AND status = 'borrowed';
    
    IF v_user_id IS NULL THEN
        SET p_result = '还书失败：无效的借阅记录';
        ROLLBACK;
        LEAVE proc;
    END IF;
    
    -- 更新借阅记录
    UPDATE borrowing_records 
    SET return_date = CURDATE(), 
        status = 'returned'
    WHERE record_id = p_record_id;
    
    -- 更新副本状态
    UPDATE book_copies 
    SET status = 'available' 
    WHERE copy_id = v_copy_id;
    
    -- 计算逾期天数和罚款
    SET v_overdue_days = DATEDIFF(CURDATE(), v_due_date);
    SET p_fine_amount = 0;
    
    IF v_overdue_days > 0 THEN
        SET p_fine_amount = v_overdue_days * 0.1;
        
        -- 插入罚款记录
        INSERT INTO fine_records (user_id, record_id, fine_amount, fine_reason)
        VALUES (v_user_id, p_record_id, p_fine_amount, CONCAT('逾期', v_overdue_days, '天'));
    END IF;
    
    -- 记录日志
    INSERT INTO system_logs (user_id, operation, table_name, record_id, details)
    VALUES (v_user_id, 'return_book', 'borrowing_records', p_record_id,
            JSON_OBJECT('copy_id', v_copy_id, 'overdue_days', v_overdue_days, 'fine_amount', p_fine_amount));
    
    COMMIT;
    SET p_result = '还书成功';
    
    proc: BEGIN END;
END //

DELIMITER ;

3. 统计函数 ​

sql
-- 计算用户未缴罚款总额
DELIMITER //

CREATE FUNCTION fn_user_unpaid_fines(p_user_id INT)
RETURNS DECIMAL(10,2)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE total_fines DECIMAL(10,2) DEFAULT 0;
    
    SELECT COALESCE(SUM(fine_amount), 0) INTO total_fines
    FROM fine_records
    WHERE user_id = p_user_id AND status = 'pending';
    
    RETURN total_fines;
END //

DELIMITER ;

-- 获取图书可借阅副本数
DELIMITER //

CREATE FUNCTION fn_available_copies(p_book_id INT)
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE available_count INT DEFAULT 0;
    
    SELECT COUNT(*) INTO available_count
    FROM book_copies
    WHERE book_id = p_book_id AND status = 'available';
    
    RETURN available_count;
END //

DELIMITER ;

视图创建 ​

sql
-- 创建常用视图
-- 1. 图书详细信息视图
CREATE VIEW v_book_details AS
SELECT 
    b.book_id,
    b.isbn,
    b.title,
    b.subtitle,
    GROUP_CONCAT(DISTINCT a.author_name) AS authors,
    p.publisher_name,
    c.category_name,
    b.publish_date,
    b.pages,
    b.price,
    b.description,
    COUNT(bc.copy_id) AS total_copies,
    COUNT(CASE WHEN bc.status = 'available' THEN 1 END) AS available_copies
FROM books b
LEFT JOIN book_authors ba ON b.book_id = ba.book_id
LEFT JOIN authors a ON ba.author_id = a.author_id
LEFT JOIN publishers p ON b.publisher_id = p.publisher_id
LEFT JOIN categories c ON b.category_id = c.category_id
LEFT JOIN book_copies bc ON b.book_id = bc.book_id
GROUP BY b.book_id, b.isbn, b.title, b.subtitle, p.publisher_name, 
         c.category_name, b.publish_date, b.pages, b.price, b.description;

-- 2. 用户借阅信息视图
CREATE VIEW v_user_borrowings AS
SELECT 
    u.user_id,
    u.real_name,
    u.username,
    b.title,
    bc.copy_number,
    br.borrow_date,
    br.due_date,
    br.return_date,
    br.status,
    DATEDIFF(CURDATE(), br.due_date) AS overdue_days
FROM users u
JOIN borrowing_records br ON u.user_id = br.user_id
JOIN book_copies bc ON br.copy_id = bc.copy_id
JOIN books b ON bc.book_id = b.book_id;

-- 3. 图书借阅统计视图
CREATE VIEW v_book_borrowing_stats AS
SELECT 
    b.book_id,
    b.title,
    COUNT(br.record_id) AS total_borrows,
    COUNT(CASE WHEN br.status = 'returned' THEN 1 END) AS returned_count,
    COUNT(CASE WHEN br.status = 'borrowed' THEN 1 END) AS current_borrows,
    COUNT(CASE WHEN br.status = 'overdue' THEN 1 END) AS overdue_count,
    AVG(DATEDIFF(br.return_date, br.borrow_date)) AS avg_borrow_days
FROM books b
LEFT JOIN book_copies bc ON b.book_id = bc.book_id
LEFT JOIN borrowing_records br ON bc.copy_id = br.copy_id
GROUP BY b.book_id, b.title;

性能优化 ​

1. 查询优化 ​

sql
-- 分析查询性能
EXPLAIN SELECT * FROM v_book_details WHERE title LIKE '%代码%';

-- 为视图创建物化表以提高性能
CREATE TABLE book_details_cache AS SELECT * FROM v_book_details;

-- 添加索引到缓存表
CREATE INDEX idx_cache_title ON book_details_cache(title);
CREATE INDEX idx_cache_category ON book_details_cache(category_name);

2. 分区维护 ​

sql
-- 定期添加新的分区
ALTER TABLE system_logs 
ADD PARTITION (PARTITION p202407 VALUES LESS THAN (TO_DAYS('2024-08-01')));

-- 删除旧分区（保留最近6个月的数据）
-- 注意：这会删除分区中的所有数据
ALTER TABLE system_logs 
DROP PARTITION p202401;

权限配置 ​

sql
-- 创建不同角色的用户
CREATE USER 'lib_admin'@'%' IDENTIFIED BY 'admin_password_123';
CREATE USER 'lib_staff'@'%' IDENTIFIED BY 'staff_password_123';
CREATE USER 'lib_reader'@'%' IDENTIFIED BY 'reader_password_123';

-- 授予权限
-- 管理员权限
GRANT ALL PRIVILEGES ON library_management.* TO 'lib_admin'@'%';

-- 图书管理员权限
GRANT SELECT, INSERT, UPDATE, DELETE ON library_management.users TO 'lib_staff'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON library_management.books TO 'lib_staff'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON library_management.book_copies TO 'lib_staff'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON library_management.borrowing_records TO 'lib_staff'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON library_management.fine_records TO 'lib_staff'@'%';
GRANT SELECT ON library_management.v_book_details TO 'lib_staff'@'%';
GRANT SELECT ON library_management.v_user_borrowings TO 'lib_staff'@'%';
GRANT EXECUTE ON PROCEDURE library_management.sp_borrow_book TO 'lib_staff'@'%';
GRANT EXECUTE ON PROCEDURE library_management.sp_return_book TO 'lib_staff'@'%';

-- 读者权限
GRANT SELECT ON library_management.books TO 'lib_reader'@'%';
GRANT SELECT ON library_management.v_book_details TO 'lib_reader'@'%';
GRANT SELECT ON library_management.borrowing_records TO 'lib_reader'@'%';
GRANT EXECUTE ON FUNCTION library_management.fn_user_unpaid_fines TO 'lib_reader'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

备份策略 ​

sql
-- 创建备份脚本（Linux shell 脚本示例）
/*
#!/bin/bash
# 数据库备份脚本

BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="library_management"

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
mysqldump -u root -p$MYSQL_ROOT_PASSWORD --single-transaction --routines --triggers $DB_NAME > $BACKUP_DIR/${DB_NAME}_${DATE}.sql

# 压缩备份文件
gzip $BACKUP_DIR/${DB_NAME}_${DATE}.sql

# 删除7天前的备份
find $BACKUP_DIR -name "${DB_NAME}_*.sql.gz" -mtime +7 -delete

echo "Backup completed: ${DB_NAME}_${DATE}.sql.gz"
*/

-- 创建事件调度器定期备份（需要启用事件调度器）
SET GLOBAL event_scheduler = ON;

DELIMITER //

CREATE EVENT evt_daily_backup
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
BEGIN
    -- 这里可以调用系统命令进行备份
    -- 注意：在实际应用中需要更复杂的实现
    INSERT INTO system_logs (operation, details) 
    VALUES ('backup', JSON_OBJECT('status', 'scheduled', 'time', NOW()));
END //

DELIMITER ;

文档编写 ​

1. 数据字典 ​

sql
-- 创建数据字典视图
CREATE VIEW v_data_dictionary AS
SELECT 
    TABLE_NAME as table_name,
    COLUMN_NAME as column_name,
    COLUMN_TYPE as data_type,
    IS_NULLABLE as nullable,
    COLUMN_DEFAULT as default_value,
    COLUMN_COMMENT as description
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'library_management'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

2. 常用操作文档 ​

sql
-- 创建操作手册表
CREATE TABLE operation_manual (
    id INT AUTO_INCREMENT PRIMARY KEY,
    operation_name VARCHAR(100) NOT NULL,
    description TEXT,
    sql_example TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入操作手册内容
INSERT INTO operation_manual (operation_name, description, sql_example) VALUES
('借书操作', '处理读者借书请求', 'CALL sp_borrow_book(用户ID, 副本ID, @result)'),
('还书操作', '处理读者还书请求', 'CALL sp_return_book(记录ID, @result, @fine)'),
('查询图书', '根据条件查询图书信息', 'SELECT * FROM v_book_details WHERE title LIKE "%关键词%"'),
('用户罚款查询', '查询用户未缴罚款', 'SELECT fn_user_unpaid_fines(用户ID) AS unpaid_fines');

测试和验证 ​

sql
-- 测试借书功能
CALL sp_borrow_book(3, 1, @result);
SELECT @result;

-- 查看借阅记录
SELECT * FROM borrowing_records WHERE user_id = 3 ORDER BY borrow_date DESC;

-- 测试还书功能
CALL sp_return_book(1, @result, @fine);
SELECT @result, @fine;

-- 查看罚款记录
SELECT * FROM fine_records WHERE user_id = 3;

-- 测试视图
SELECT * FROM v_book_details WHERE title LIKE '%代码%';
SELECT * FROM v_user_borrowings WHERE user_id = 3;

-- 测试函数
SELECT fn_user_unpaid_fines(3) AS unpaid_fines;
SELECT fn_available_copies(1) AS available_copies;

小结 ​

在本章中，我们完成了一个完整的图书管理系统数据库项目，涵盖了：

  1. 需求分析 - 明确系统功能和业务规则
  2. 数据库设计 - 实体关系分析和表结构设计
  3. 表结构实现 - 创建所有必要的表和约束
  4. 数据初始化 - 插入测试数据
  5. 查询优化 - 编写常用查询语句和视图
  6. 存储过程 - 实现核心业务逻辑
  7. 权限管理 - 配置不同用户角色的权限
  8. 备份策略 - 制定数据备份方案
  9. 文档编写 - 创建数据字典和操作手册

通过这个完整的项目实战，我们综合运用了前面章节学到的所有知识：

  • 基本的表创建和数据操作
  • 索引和性能优化
  • 视图和存储过程
  • 事务和并发控制
  • 用户权限管理
  • 分区表
  • JSON 数据类型
  • 外键约束

这个项目展示了如何将理论知识应用到实际开发中，为构建更复杂的企业级应用打下了坚实的基础。恭喜你完成了 MySQL 基础教程的所有章节！

Copyright © 2025 梁欢的前后端技术笔记 鲁ICP备2025190626号