MySQL: 综合项目：电商数据库设计与实现

CREATE DATABASE IF NOT EXISTS ecommerce
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE ecommerce;

CREATE TABLE users (
    id BIGINT 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),
    avatar VARCHAR(255) DEFAULT '/images/default_avatar.png',
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_users_phone (phone),
    INDEX idx_users_status (status)
) ENGINE=InnoDB COMMENT='用户表';

CREATE TABLE addresses (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    receiver VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    province VARCHAR(30) NOT NULL,
    city VARCHAR(30) NOT NULL,
    district VARCHAR(30) NOT NULL,
    detail VARCHAR(200) NOT NULL,
    is_default TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_addresses_user (user_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='收货地址表';

CREATE TABLE user_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,
    action VARCHAR(50) NOT NULL,
    ip_address VARCHAR(45),
    user_agent VARCHAR(255),
    detail JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_logs_user (user_id),
    INDEX idx_logs_action (action),
    INDEX idx_logs_created (created_at)
) ENGINE=InnoDB COMMENT='用户操作日志表';

INSERT INTO users (username, email, password_hash, phone)
VALUES (
    'alice',
    'alice@example.com',
    SHA2('MySecret123!', 256),
    '13800138000'
);

INSERT INTO users (username, email, password_hash, phone)
VALUES (
    'bob_dev',
    'bob@example.com',
    SHA2('BobPass456!', 256),
    '13900139000'
);

SELECT id, username, status FROM users
WHERE email = 'alice@example.com'
  AND password_hash = SHA2('MySecret123!', 256)
  AND status = 'active';

INSERT INTO addresses (user_id, receiver, phone, province, city, district, detail, is_default)
VALUES (1, 'Alice', '13800138000', 'Guangdong', 'Shenzhen', 'Nanshan', 'Tech Park Bldg 5 Room 301', 1);

INSERT INTO addresses (user_id, receiver, phone, province, city, district, detail, is_default)
VALUES (1, 'Alice', '13800138000', 'Beijing', 'Beijing', 'Haidian', 'Zhongguancun St 88', 0);

SELECT * FROM addresses WHERE user_id = 1 ORDER BY is_default DESC;

INSERT INTO user_logs (user_id, action, ip_address, user_agent, detail)
VALUES (1, 'LOGIN', '192.168.1.100', 'Chrome/120', '{"method": "email"}');

INSERT INTO user_logs (user_id, action, ip_address, detail)
VALUES (1, 'UPDATE_ADDRESS', '192.168.1.100', '{"address_id": 1}');

SELECT action, COUNT(*) AS cnt
FROM user_logs
WHERE user_id = 1
GROUP BY action
ORDER BY cnt DESC;

CREATE TABLE categories (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    parent_id BIGINT DEFAULT 0,
    sort_order INT DEFAULT 0,
    icon VARCHAR(255),
    is_visible TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_categories_parent (parent_id),
    INDEX idx_categories_sort (sort_order)
) ENGINE=InnoDB COMMENT='商品分类表';

INSERT INTO categories (id, name, parent_id, sort_order) VALUES
(1, 'Electronics', 0, 1),
(2, 'Clothing', 0, 2),
(3, 'Home & Living', 0, 3),
(4, 'Smartphones', 1, 1),
(5, 'Laptops', 1, 2),
(6, 'Men', 2, 1),
(7, 'Women', 2, 2),
(8, 'Kitchen', 3, 1);

SELECT c1.name AS category, c2.name AS sub_category
FROM categories c1
LEFT JOIN categories c2 ON c1.id = c2.parent_id
WHERE c1.parent_id = 0
ORDER BY c1.sort_order, c2.sort_order;

CREATE TABLE products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    original_price DECIMAL(10,2),
    stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
    sales INT NOT NULL DEFAULT 0,
    category_id BIGINT NOT NULL,
    status ENUM('active', 'inactive', 'sold_out') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_products_category (category_id),
    INDEX idx_products_status (status),
    INDEX idx_products_sales (sales DESC),
    INDEX idx_products_created (created_at DESC),
    FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB COMMENT='商品表';

CREATE TABLE product_images (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id BIGINT NOT NULL,
    image_url VARCHAR(255) NOT NULL,
    is_main TINYINT(1) DEFAULT 0,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_images_product (product_id),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='商品图片表';

INSERT INTO products (name, description, price, original_price, stock, category_id, status)
VALUES
('Galaxy S24 Ultra', 'Samsung flagship 2024', 1299.99, 1399.99, 200, 4, 'active'),
('MacBook Pro 16', 'Apple M3 Max laptop', 2499.00, 2499.00, 50, 5, 'active'),
('Cotton T-Shirt', 'Pure cotton casual tee', 29.99, 39.99, 500, 6, 'active'),
('Smart Rice Cooker', 'IoT enabled cooker', 89.99, 119.99, 300, 8, 'active');

INSERT INTO product_images (product_id, image_url, is_main, sort_order) VALUES
(1, '/images/galaxy_s24_1.jpg', 1, 1),
(1, '/images/galaxy_s24_2.jpg', 0, 2),
(2, '/images/macbook_pro_1.jpg', 1, 1),
(3, '/images/tshirt_white.jpg', 1, 1),
(4, '/images/cooker_main.jpg', 1, 1);

SELECT p.id, p.name, p.price, pi.image_url
FROM products p
LEFT JOIN product_images pi ON p.id = pi.product_id AND pi.is_main = 1
WHERE p.category_id = 4 AND p.status = 'active'
ORDER BY p.sales DESC
LIMIT 10;

UPDATE products SET stock = stock - 1, sales = sales + 1 WHERE id = 1;

UPDATE products SET status = 'sold_out' WHERE stock <= 0;

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL UNIQUE,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    pay_amount DECIMAL(12,2) DEFAULT NULL,
    status ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled', 'refunded')
        DEFAULT 'pending',
    receiver VARCHAR(50) NOT NULL,
    receiver_phone VARCHAR(20) NOT NULL,
    shipping_address TEXT NOT NULL,
    remark VARCHAR(500),
    paid_at DATETIME DEFAULT NULL,
    shipped_at DATETIME DEFAULT NULL,
    completed_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_orders_user (user_id),
    INDEX idx_orders_status (status),
    INDEX idx_orders_created (created_at DESC),
    INDEX idx_orders_order_no (order_no),
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB COMMENT='订单表';

CREATE TABLE order_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    price DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(12,2) GENERATED ALWAYS AS (quantity * price) STORED,
    INDEX idx_items_order (order_id),
    INDEX idx_items_product (product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB COMMENT='订单明细表';

DELIMITER //
CREATE PROCEDURE sp_create_order(
    IN p_user_id BIGINT,
    IN p_receiver VARCHAR(50),
    IN p_phone VARCHAR(20),
    IN p_address TEXT,
    IN p_remark VARCHAR(500)
)
BEGIN
    DECLARE v_order_no VARCHAR(32);
    DECLARE v_total DECIMAL(12,2) DEFAULT 0;
    DECLARE v_cart_item INT;
    DECLARE v_product_id BIGINT;
    DECLARE v_quantity INT;
    DECLARE v_price DECIMAL(10,2);
    DECLARE v_stock INT;
    DECLARE v_name VARCHAR(200);
    DECLARE done INT DEFAULT 0;
    DECLARE cart_cursor CURSOR FOR
        SELECT c.product_id, c.quantity, p.price, p.name, p.stock
        FROM cart c
        JOIN products p ON c.product_id = p.id
        WHERE c.user_id = p_user_id AND p.status = 'active'
        FOR UPDATE;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    START TRANSACTION;

    SET v_order_no = CONCAT(
        'ORD', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'),
        LPAD(FLOOR(RAND() * 10000), 4, '0')
    );

    INSERT INTO orders (order_no, user_id, receiver, receiver_phone, shipping_address, remark)
    VALUES (v_order_no, p_user_id, p_receiver, p_phone, p_address, p_remark);

    OPEN cart_cursor;
    read_loop: LOOP
        FETCH cart_cursor INTO v_product_id, v_quantity, v_price, v_name, v_stock;
        IF done THEN LEAVE read_loop; END IF;

        IF v_stock < v_quantity THEN
            ROLLBACK;
            SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = CONCAT('Insufficient stock for: ', v_name);
        END IF;

        INSERT INTO order_items (order_id, product_id, product_name, quantity, price)
        VALUES (LAST_INSERT_ID(), v_product_id, v_name, v_quantity, v_price);

        UPDATE products
        SET stock = stock - v_quantity, sales = sales + v_quantity
        WHERE id = v_product_id;

        SET v_total = v_total + v_quantity * v_price;
    END LOOP;
    CLOSE cart_cursor;

    UPDATE orders SET total_amount = v_total WHERE order_no = v_order_no;
    DELETE FROM cart WHERE user_id = p_user_id;

    COMMIT;
END //
DELIMITER ;

CALL sp_create_order(
    1,
    'Alice',
    '13800138000',
    'Tech Park Bldg 5 Room 301, Nanshan, Shenzhen, Guangdong',
    'Please deliver before noon'
);

UPDATE orders SET status = 'paid', pay_amount = total_amount, paid_at = NOW()
WHERE id = 1 AND status = 'pending';

UPDATE orders SET status = 'shipped', shipped_at = NOW()
WHERE id = 1 AND status = 'paid';

UPDATE orders SET status = 'completed', completed_at = NOW()
WHERE id = 1 AND status = 'shipped';

CREATE TABLE cart (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL DEFAULT 1 CHECK (quantity > 0),
    selected TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_cart_user_product (user_id, product_id),
    INDEX idx_cart_user (user_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='购物车表';

INSERT INTO cart (user_id, product_id, quantity) VALUES (1, 1, 2);

INSERT INTO cart (user_id, product_id, quantity) VALUES (1, 2, 1)
ON DUPLICATE KEY UPDATE quantity = quantity + 1;

UPDATE cart SET quantity = 3 WHERE user_id = 1 AND product_id = 1;

DELETE FROM cart WHERE user_id = 1 AND product_id = 2;

SELECT c.product_id, p.name, p.price, c.quantity,
       p.price * c.quantity AS subtotal
FROM cart c
JOIN products p ON c.product_id = p.id
WHERE c.user_id = 1 AND c.selected = 1;

DELIMITER //
CREATE PROCEDURE sp_merge_cart(
    IN p_source_user_id BIGINT,
    IN p_target_user_id BIGINT
)
BEGIN
    INSERT INTO cart (user_id, product_id, quantity, selected)
    SELECT p_target_user_id, product_id, quantity, selected
    FROM cart
    WHERE user_id = p_source_user_id
    ON DUPLICATE KEY UPDATE
        quantity = cart.quantity + VALUES(quantity),
        selected = 1;

    DELETE FROM cart WHERE user_id = p_source_user_id;
END //
DELIMITER ;

CALL sp_merge_cart(2, 1);

CREATE TABLE payments (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    payment_no VARCHAR(64) NOT NULL UNIQUE,
    amount DECIMAL(12,2) NOT NULL,
    method ENUM('credit_card', 'debit_card', 'paypal', 'bank_transfer') NOT NULL,
    status ENUM('pending', 'success', 'failed', 'refunded') DEFAULT 'pending',
    paid_at DATETIME DEFAULT NULL,
    refund_no VARCHAR(64) DEFAULT NULL,
    refund_amount DECIMAL(12,2) DEFAULT NULL,
    refund_reason VARCHAR(500) DEFAULT NULL,
    refunded_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_payments_order (order_id),
    INDEX idx_payments_no (payment_no),
    INDEX idx_payments_status (status),
    FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB COMMENT='支付记录表';

INSERT INTO payments (order_id, payment_no, amount, method, status, paid_at)
VALUES (1, CONCAT('PAY', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), LPAD(FLOOR(RAND()*10000),4,'0')),
        3899.97, 'credit_card', 'success', NOW());

UPDATE orders SET status = 'paid', pay_amount = 3899.97, paid_at = NOW()
WHERE id = 1 AND status = 'pending';

SELECT p.payment_no, p.amount, p.method, p.status, p.paid_at
FROM payments p
WHERE p.order_id = 1;

UPDATE payments
SET status = 'refunded',
    refund_no = CONCAT('REF', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), LPAD(FLOOR(RAND()*10000),4,'0')),
    refund_amount = 3899.97,
    refund_reason = 'Defective product',
    refunded_at = NOW()
WHERE order_id = 1 AND status = 'success';

UPDATE orders SET status = 'refunded' WHERE id = 1;

CREATE OR REPLACE VIEW v_hot_products AS
SELECT
    p.id,
    p.name,
    p.price,
    c.name AS category,
    p.sales,
    IFNULL(SUM(oi.quantity), 0) AS order_sold,
    IFNULL(SUM(oi.quantity * oi.price), 0) AS revenue
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.status = 'active'
GROUP BY p.id, p.name, p.price, c.name, p.sales
ORDER BY p.sales DESC;

SELECT * FROM v_hot_products LIMIT 10;

CREATE OR REPLACE VIEW v_user_ranking AS
SELECT
    u.id,
    u.username,
    COUNT(DISTINCT o.id) AS total_orders,
    IFNULL(SUM(o.total_amount), 0) AS total_spent,
    IFNULL(AVG(o.total_amount), 0) AS avg_order_amount,
    MAX(o.created_at) AS last_order_time
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status != 'cancelled'
GROUP BY u.id, u.username
ORDER BY total_spent DESC;

SELECT * FROM v_user_ranking LIMIT 20;

CREATE OR REPLACE VIEW v_monthly_sales AS
SELECT
    DATE_FORMAT(o.created_at, '%Y-%m') AS month,
    COUNT(o.id) AS order_count,
    SUM(o.total_amount) AS revenue,
    SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END) AS completed,
    SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
    SUM(CASE WHEN o.status = 'refunded' THEN 1 ELSE 0 END) AS refunded,
    ROUND(
        SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END) / COUNT(o.id) * 100,
        2
    ) AS completion_rate
FROM orders o
GROUP BY DATE_FORMAT(o.created_at, '%Y-%m')
ORDER BY month DESC;

SELECT * FROM v_monthly_sales;

CREATE USER 'ecom_readonly'@'%' IDENTIFIED BY 'ReadOnly2024!';
GRANT SELECT ON ecommerce.* TO 'ecom_readonly'@'%';

CREATE USER 'ecom_app'@'%' IDENTIFIED BY 'AppUser2024!';
GRANT SELECT, INSERT, UPDATE ON ecommerce.* TO 'ecom_app'@'%';
GRANT EXECUTE ON ecommerce.* TO 'ecom_app'@'%';

CREATE USER 'ecom_admin'@'127.0.0.1' IDENTIFIED BY 'Admin2024!Secure';
GRANT ALL PRIVILEGES ON ecommerce.* TO 'ecom_admin'@'127.0.0.1';

FLUSH PRIVILEGES;

SHOW GRANTS FOR 'ecom_app'@'%';

#!/bin/bash
BACKUP_DIR="/backup/mysql"
DB_NAME="ecommerce"
DB_USER="ecom_admin"
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p $BACKUP_DIR

mysqldump -u $DB_USER -pAdmin2024!Secure \
    --single-transaction \
    --routines \
    --triggers \
    --views \
    $DB_NAME | gzip > ${BACKUP_DIR}/${DB_NAME}_full_${DATE}.sql.gz

mysqldump -u $DB_USER -pAdmin2024!Secure \
    --no-data \
    $DB_NAME | gzip > ${BACKUP_DIR}/${DB_NAME}_schema_${DATE}.sql.gz

find $BACKUP_DIR -name "${DB_NAME}_full_*.sql.gz" -mtime +30 -delete
find $BACKUP_DIR -name "${DB_NAME}_schema_*.sql.gz" -mtime +90 -delete

echo "Backup completed: ${DATE}"

gunzip < /backup/mysql/ecommerce_full_20240115_020000.sql.gz | mysql -u ecom_admin -p ecommerce