核心-视图与触发器

清峰大约 7 分钟

核心-视图与触发器

业务场景引入

在电商平台的数据管理中，我们经常遇到这些需求：

数据安全：客服只能查看订单基本信息，不能看到支付密码等敏感数据
查询简化：复杂的多表关联查询需要简化为简单的单表查询
数据同步：商品库存变化时自动更新库存预警状态
审计日志：记录所有重要数据的变更历史

视图可以解决前两个问题，触发器可以解决后两个问题。

视图深度应用

基础视图创建

-- 订单概览视图：隐藏敏感信息
CREATE VIEW order_summary_view AS
SELECT 
    o.order_id,
    o.order_no,
    u.username,
    u.real_name,
    o.order_status,
    o.total_amount,
    o.order_date,
    o.payment_date,
    CASE 
        WHEN o.order_status = 'DELIVERED' THEN DATEDIFF(o.delivery_date, o.order_date)
        ELSE NULL 
    END AS delivery_days
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id
WHERE o.order_status != 'DELETED';

-- 使用视图查询
SELECT * FROM order_summary_view 
WHERE order_date >= '2024-01-01'
ORDER BY order_date DESC;

复杂业务视图

-- 商品销售统计视图
CREATE VIEW product_sales_stats AS
SELECT 
    p.product_id,
    p.product_name,
    p.category_id,
    c.category_name,
    p.price AS current_price,
    p.stock_quantity AS current_stock,
    
    -- 销售统计
    COALESCE(sales.total_sold, 0) AS total_sold_quantity,
    COALESCE(sales.total_revenue, 0.00) AS total_revenue,
    COALESCE(sales.order_count, 0) AS total_orders,
    
    -- 平均售价
    CASE 
        WHEN sales.total_sold > 0 THEN ROUND(sales.total_revenue / sales.total_sold, 2)
        ELSE p.price
    END AS avg_selling_price,
    
    -- 库存状态
    CASE 
        WHEN p.stock_quantity = 0 THEN 'OUT_OF_STOCK'
        WHEN p.stock_quantity <= 10 THEN 'LOW_STOCK'
        WHEN p.stock_quantity <= 50 THEN 'NORMAL'
        ELSE 'ABUNDANT'
    END AS stock_status,
    
    -- 最后销售时间
    sales.last_sale_date,
    
    -- 是否热销商品
    CASE 
        WHEN sales.total_sold >= 100 THEN 'HOT'
        WHEN sales.total_sold >= 50 THEN 'POPULAR'
        WHEN sales.total_sold >= 10 THEN 'NORMAL'
        ELSE 'SLOW'
    END AS sales_level

FROM products p
LEFT JOIN categories c ON p.category_id = c.category_id
LEFT JOIN (
    SELECT 
        oi.product_id,
        SUM(oi.quantity) AS total_sold,
        SUM(oi.subtotal) AS total_revenue,
        COUNT(DISTINCT oi.order_id) AS order_count,
        MAX(o.order_date) AS last_sale_date
    FROM order_items oi
    INNER JOIN orders o ON oi.order_id = o.order_id
    WHERE o.order_status IN ('PAID', 'SHIPPED', 'DELIVERED')
    GROUP BY oi.product_id
) sales ON p.product_id = sales.product_id
WHERE p.status = 'ACTIVE';

-- 查询热销商品
SELECT product_name, total_sold_quantity, total_revenue, sales_level
FROM product_sales_stats 
WHERE sales_level = 'HOT'
ORDER BY total_revenue DESC;

可更新视图

-- 用户基础信息视图（可更新）
CREATE VIEW user_profile_view AS
SELECT 
    user_id,
    username,
    real_name,
    email,
    phone,
    birth_date,
    gender,
    registration_date,
    is_active
FROM users
WHERE is_active = TRUE;

-- 通过视图更新数据
UPDATE user_profile_view 
SET real_name = '张三丰', phone = '13888888888'
WHERE user_id = 1001;

-- 通过视图插入数据
INSERT INTO user_profile_view (username, real_name, email)
VALUES ('new_user', '新用户', 'new@example.com');

视图权限控制

-- 创建不同权限级别的视图

-- 客服视图：基础订单信息
CREATE VIEW customer_service_orders AS
SELECT 
    order_id,
    order_no,
    user_id,
    order_status,
    total_amount,
    order_date,
    shipping_address
FROM orders
WHERE order_status != 'DELETED';

-- 财务视图：包含支付信息
CREATE VIEW finance_orders AS
SELECT 
    o.order_id,
    o.order_no,
    o.user_id,
    u.real_name,
    o.total_amount,
    o.discount_amount,
    o.final_amount,
    o.payment_method,
    o.payment_date,
    o.order_status
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id
WHERE o.payment_date IS NOT NULL;

-- 管理员视图：完整信息
CREATE VIEW admin_orders AS
SELECT 
    o.*,
    u.username,
    u.real_name,
    u.email
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id;

-- 为不同角色授权
GRANT SELECT ON customer_service_orders TO 'cs_user'@'%';
GRANT SELECT ON finance_orders TO 'finance_user'@'%';
GRANT SELECT ON admin_orders TO 'admin_user'@'%';

触发器实战应用

数据审计触发器

-- 创建审计日志表
CREATE TABLE audit_logs (
    log_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    table_name VARCHAR(64) NOT NULL,
    operation_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    primary_key_value VARCHAR(100) NOT NULL,
    old_values JSON,
    new_values JSON,
    changed_by VARCHAR(100),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_table_operation (table_name, operation_type),
    INDEX idx_changed_at (changed_at),
    INDEX idx_primary_key (table_name, primary_key_value)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 用户表审计触发器
DELIMITER $$

-- 插入触发器
CREATE TRIGGER users_audit_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO audit_logs (
        table_name, 
        operation_type, 
        primary_key_value, 
        new_values, 
        changed_by
    ) VALUES (
        'users',
        'INSERT',
        NEW.user_id,
        JSON_OBJECT(
            'username', NEW.username,
            'email', NEW.email,
            'real_name', NEW.real_name,
            'phone', NEW.phone,
            'is_active', NEW.is_active
        ),
        USER()
    );
END$$

-- 更新触发器
CREATE TRIGGER users_audit_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO audit_logs (
        table_name, 
        operation_type, 
        primary_key_value, 
        old_values,
        new_values, 
        changed_by
    ) VALUES (
        'users',
        'UPDATE',
        NEW.user_id,
        JSON_OBJECT(
            'username', OLD.username,
            'email', OLD.email,
            'real_name', OLD.real_name,
            'phone', OLD.phone,
            'is_active', OLD.is_active
        ),
        JSON_OBJECT(
            'username', NEW.username,
            'email', NEW.email,
            'real_name', NEW.real_name,
            'phone', NEW.phone,
            'is_active', NEW.is_active
        ),
        USER()
    );
END$$

-- 删除触发器
CREATE TRIGGER users_audit_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO audit_logs (
        table_name, 
        operation_type, 
        primary_key_value, 
        old_values, 
        changed_by
    ) VALUES (
        'users',
        'DELETE',
        OLD.user_id,
        JSON_OBJECT(
            'username', OLD.username,
            'email', OLD.email,
            'real_name', OLD.real_name,
            'phone', OLD.phone,
            'is_active', OLD.is_active
        ),
        USER()
    );
END$$

DELIMITER ;

业务逻辑触发器

-- 库存管理触发器
DELIMITER $$

-- 订单创建时自动扣减库存
CREATE TRIGGER order_items_stock_deduction
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    DECLARE v_current_stock INT DEFAULT 0;
    
    -- 检查当前库存
    SELECT stock_quantity INTO v_current_stock
    FROM products 
    WHERE product_id = NEW.product_id;
    
    -- 扣减库存
    UPDATE products 
    SET stock_quantity = stock_quantity - NEW.quantity,
        updated_at = NOW()
    WHERE product_id = NEW.product_id;
    
    -- 如果库存不足，插入预警记录
    IF v_current_stock - NEW.quantity <= 10 THEN
        INSERT INTO stock_alerts (product_id, alert_type, current_stock, threshold_stock, created_at)
        VALUES (NEW.product_id, 'LOW_STOCK', v_current_stock - NEW.quantity, 10, NOW())
        ON DUPLICATE KEY UPDATE 
            current_stock = VALUES(current_stock),
            updated_at = NOW();
    END IF;
END$$

-- 订单取消时恢复库存
CREATE TRIGGER order_cancel_stock_restore
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    -- 只有当订单状态从其他状态变为CANCELLED时才恢复库存
    IF OLD.order_status != 'CANCELLED' AND NEW.order_status = 'CANCELLED' THEN
        -- 恢复所有订单商品的库存
        UPDATE products p
        INNER JOIN order_items oi ON p.product_id = oi.product_id
        SET p.stock_quantity = p.stock_quantity + oi.quantity,
            p.updated_at = NOW()
        WHERE oi.order_id = NEW.order_id;
        
        -- 删除相关的库存预警
        DELETE FROM stock_alerts 
        WHERE product_id IN (
            SELECT product_id FROM order_items WHERE order_id = NEW.order_id
        ) AND alert_type = 'LOW_STOCK';
    END IF;
END$$

DELIMITER ;

数据验证触发器

DELIMITER $$

-- 价格变更验证触发器
CREATE TRIGGER products_price_validation
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    -- 价格不能为负数
    IF NEW.price < 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Product price cannot be negative';
    END IF;
    
    -- 价格变动不能超过50%
    IF OLD.price > 0 AND ABS(NEW.price - OLD.price) / OLD.price > 0.5 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Price change cannot exceed 50%';
    END IF;
    
    -- 记录价格变更历史
    IF OLD.price != NEW.price THEN
        INSERT INTO price_history (product_id, old_price, new_price, change_reason, changed_at)
        VALUES (NEW.product_id, OLD.price, NEW.price, 'MANUAL_UPDATE', NOW());
    END IF;
END$$

-- 用户注册验证触发器
CREATE TRIGGER users_registration_validation
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    -- 邮箱格式验证
    IF NEW.email NOT REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Invalid email format';
    END IF;
    
    -- 手机号格式验证（中国手机号）
    IF NEW.phone IS NOT NULL AND NEW.phone NOT REGEXP '^1[3-9][0-9]{9}$' THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Invalid phone number format';
    END IF;
    
    -- 自动设置注册时间
    SET NEW.registration_date = NOW();
    
    -- 自动生成用户编号
    IF NEW.user_code IS NULL THEN
        SET NEW.user_code = CONCAT('U', DATE_FORMAT(NOW(), '%Y%m%d'), LPAD(LAST_INSERT_ID(), 6, '0'));
    END IF;
END$$

DELIMITER ;

统计计算触发器

DELIMITER $$

-- 用户统计更新触发器
CREATE TRIGGER update_user_statistics
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    -- 更新用户统计信息
    INSERT INTO user_statistics (user_id, total_orders, total_spent, last_order_date)
    VALUES (NEW.user_id, 1, NEW.total_amount, NEW.order_date)
    ON DUPLICATE KEY UPDATE
        total_orders = total_orders + 1,
        total_spent = total_spent + NEW.total_amount,
        last_order_date = NEW.order_date,
        updated_at = NOW();
    
    -- 更新用户等级
    UPDATE users u
    INNER JOIN user_statistics us ON u.user_id = us.user_id
    SET u.user_level = CASE
        WHEN us.total_spent >= 10000 THEN 'DIAMOND'
        WHEN us.total_spent >= 5000 THEN 'GOLD'
        WHEN us.total_spent >= 1000 THEN 'SILVER'
        ELSE 'BRONZE'
    END
    WHERE u.user_id = NEW.user_id;
END$$

-- 商品评分更新触发器
CREATE TRIGGER update_product_rating
AFTER INSERT ON product_reviews
FOR EACH ROW
BEGIN
    UPDATE products p
    SET p.rating_average = (
        SELECT AVG(rating) 
        FROM product_reviews 
        WHERE product_id = NEW.product_id
    ),
    p.rating_count = (
        SELECT COUNT(*) 
        FROM product_reviews 
        WHERE product_id = NEW.product_id
    ),
    p.updated_at = NOW()
    WHERE p.product_id = NEW.product_id;
END$$

DELIMITER ;

性能优化

视图优化

-- 使用索引优化视图性能
-- 为视图涉及的表创建合适索引
CREATE INDEX idx_orders_status_date ON orders (order_status, order_date);
CREATE INDEX idx_order_items_product ON order_items (product_id);
CREATE INDEX idx_users_active ON users (is_active);

-- 避免在视图中使用函数计算
-- ❌ 性能较差的视图
CREATE VIEW slow_order_view AS
SELECT 
    order_id,
    order_no,
    YEAR(order_date) AS order_year,  -- 函数计算
    MONTH(order_date) AS order_month,  -- 函数计算
    total_amount
FROM orders;

-- ✅ 性能更好的视图
CREATE VIEW fast_order_view AS
SELECT 
    order_id,
    order_no,
    order_date,
    total_amount
FROM orders;
-- 在查询时再进行时间计算

触发器优化

-- 优化触发器性能
DELIMITER $$

-- ❌ 性能较差的触发器
CREATE TRIGGER slow_update_trigger
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    -- 避免在触发器中进行复杂查询
    UPDATE category_statistics 
    SET total_products = (
        SELECT COUNT(*) FROM products WHERE category_id = NEW.category_id
    )
    WHERE category_id = NEW.category_id;
END$$

-- ✅ 性能更好的触发器
CREATE TRIGGER fast_update_trigger
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    -- 只在必要时更新
    IF OLD.category_id != NEW.category_id THEN
        UPDATE category_statistics 
        SET total_products = total_products - 1
        WHERE category_id = OLD.category_id;
        
        UPDATE category_statistics 
        SET total_products = total_products + 1
        WHERE category_id = NEW.category_id;
    END IF;
END$$

DELIMITER ;

管理和监控

查看视图和触发器信息

-- 查看所有视图
SELECT 
    table_name AS view_name,
    view_definition,
    check_option,
    is_updatable
FROM information_schema.views 
WHERE table_schema = 'ecommerce';

-- 查看所有触发器
SELECT 
    trigger_name,
    event_manipulation,
    event_object_table,
    action_timing,
    created
FROM information_schema.triggers 
WHERE trigger_schema = 'ecommerce';

-- 查看触发器详细定义
SHOW CREATE TRIGGER users_audit_insert;

-- 删除视图和触发器
DROP VIEW IF EXISTS order_summary_view;
DROP TRIGGER IF EXISTS users_audit_insert;

性能监控

-- 监控视图查询性能
SELECT 
    object_schema,
    object_name,
    count_star AS query_count,
    sum_timer_wait/1000000000000 AS total_time_sec,
    avg_timer_wait/1000000000000 AS avg_time_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE object_schema = 'ecommerce'
  AND digest_text LIKE '%view%'
ORDER BY avg_timer_wait DESC;

总结与最佳实践

视图使用建议

适合使用视图的场景：

简化复杂查询
数据安全和权限控制
提供不同视角的数据展示
兼容性层（表结构变更时）

视图设计原则：

避免过度复杂的关联查询
为底层表创建合适的索引
考虑视图的可更新性需求
定期检查视图性能

触发器使用建议

适合使用触发器的场景：

数据完整性约束
审计日志记录
自动计算和统计
业务规则强制执行

触发器设计原则：

保持触发器逻辑简单
避免在触发器中进行复杂计算
考虑触发器的性能影响
避免触发器的无限递归

视图和触发器是MySQL数据库中强大的功能特性，合理使用可以简化应用开发，提高数据安全性和一致性。