视图、函数和过程 - MySQL与PostgreSQL实现对比

引言

数据库编程是现代应用开发的重要组成部分。视图、函数和存储过程等数据库对象，能帮我们封装复杂逻辑、提升效率、增强安全性，并简化应用代码。本文将详细介绍这些对象的创建与使用，对比MySQL和PostgreSQL的实现差异。

1. 视图（Views）

1.1 什么是视图？

视图是一种虚拟表，其内容由查询定义。与真实表不同，视图不存储数据，而是在引用时动态生成结果集。

1.2 MySQL视图

基础视图创建：

-- 简单视图
CREATE VIEW active_employees AS
SELECT employee_id, first_name, last_name, email, salary
FROM employees
WHERE status = 'active';

-- 复杂视图
CREATE VIEW department_summary AS
SELECT 
    d.department_id,
    d.department_name,
    COUNT(e.employee_id) as employee_count,
    AVG(e.salary) as avg_salary,
    MIN(e.hire_date) as earliest_hire,
    MAX(e.salary) as max_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

-- 带检查选项的视图
CREATE VIEW high_earners AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 75000
WITH CHECK OPTION;  -- 确保通过视图修改的数据满足WHERE条件

MySQL视图特性：

-- 查看视图定义
SHOW CREATE VIEW active_employees;

-- 修改视图
CREATE OR REPLACE VIEW active_employees AS
SELECT employee_id, first_name, last_name, email, salary, hire_date
FROM employees
WHERE status = 'active';

-- 删除视图
DROP VIEW IF EXISTS active_employees;

-- 指定视图算法
CREATE ALGORITHM=MERGE VIEW emp_dept_view AS
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

1.3 PostgreSQL视图

PostgreSQL视图创建：

-- 简单视图
CREATE VIEW active_employees AS
SELECT employee_id, first_name, last_name, email, salary
FROM employees
WHERE status = 'active';

-- 复杂视图
CREATE VIEW department_summary AS
SELECT 
    d.department_id,
    d.department_name,
    COUNT(e.employee_id) as employee_count,
    AVG(e.salary) as avg_salary,
    MIN(e.hire_date) as earliest_hire,
    MAX(e.salary) as max_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

-- 带检查选项的视图
CREATE VIEW high_earners AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 75000
WITH CHECK OPTION;

PostgreSQL高级视图特性：

-- 物化视图（PostgreSQL特有）
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT 
    DATE_TRUNC('month', sale_date) as month,
    SUM(amount) as total_sales,
    COUNT(*) as transaction_count
FROM sales
GROUP BY DATE_TRUNC('month', sale_date);

-- 刷新物化视图
REFRESH MATERIALIZED VIEW monthly_sales_summary;

-- 并发刷新（不锁定表）
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;

1.4 视图最佳实践

-- 安全性视图（隐藏敏感信息）
CREATE VIEW employee_public_view AS
SELECT 
    employee_id,
    first_name,
    last_name,
    department_id,
    hire_date
FROM employees
WHERE status = 'active';

-- 使用视图进行数据抽象
CREATE VIEW customer_order_summary AS
SELECT 
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) as total_orders,
    SUM(o.total_amount) as total_spent,
    MAX(o.order_date) as last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

2. 存储函数（Functions）

2.1 什么是存储函数？

存储函数是数据库中的预编译代码块，可接收参数、执行操作并返回单个值。

2.2 MySQL存储函数

-- 简单函数
DELIMITER //
CREATE FUNCTION calculate_bonus(salary DECIMAL(10,2), bonus_rate DECIMAL(3,2))
RETURNS DECIMAL(10,2)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE bonus_amount DECIMAL(10,2);
    SET bonus_amount = salary * bonus_rate;
    RETURN bonus_amount;
END //
DELIMITER ;

-- 使用函数
SELECT employee_id, first_name, last_name, 
       salary, 
       calculate_bonus(salary, 0.1) as bonus
FROM employees;

2.3 PostgreSQL存储函数

-- 使用PL/pgSQL语言的函数
CREATE OR REPLACE FUNCTION calculate_bonus(salary NUMERIC, bonus_rate NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN salary * bonus_rate;
END;
$$ LANGUAGE plpgsql;

-- 使用函数
SELECT employee_id, first_name, last_name, 
       salary, 
       calculate_bonus(salary, 0.1) as bonus
FROM employees;

-- 返回表类型的函数
CREATE OR REPLACE FUNCTION get_high_earners(min_salary NUMERIC)
RETURNS TABLE (
    employee_id INTEGER,
    first_name TEXT,
    last_name TEXT,
    salary NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT e.employee_id, e.first_name, e.last_name, e.salary
    FROM employees e
    WHERE e.salary >= min_salary
    ORDER BY e.salary DESC;
END;
$$ LANGUAGE plpgsql;

-- 使用返回表的函数
SELECT * FROM get_high_earners(80000);

3. 存储过程（Stored Procedures）

3.1 什么是存储过程？

存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程名并给出参数来执行它。

3.2 MySQL存储过程

-- 简单存储过程
DELIMITER //
CREATE PROCEDURE GetEmployeeById(IN emp_id INT)
BEGIN
    SELECT employee_id, first_name, last_name, email, salary
    FROM employees
    WHERE employee_id = emp_id;
END //
DELIMITER ;

-- 调用存储过程
CALL GetEmployeeById(123);

3.3 PostgreSQL存储过程

-- PostgreSQL 11+开始支持真正的存储过程
CREATE OR REPLACE PROCEDURE update_employee_salary(
    emp_id INTEGER,
    new_salary NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    current_salary NUMERIC;
BEGIN
    -- 检查员工是否存在
    SELECT salary INTO current_salary
    FROM employees
    WHERE employee_id = emp_id;
    
    IF current_salary IS NULL THEN
        RAISE NOTICE 'Employee not found';
        RETURN;
    END IF;
    
    -- 更新薪资
    UPDATE employees
    SET salary = new_salary,
        updated_at = NOW()
    WHERE employee_id = emp_id;
    
    RAISE NOTICE 'Salary updated from % to %', current_salary, new_salary;
END;
$$;

-- 调用存储过程
CALL update_employee_salary(123, 75000);

4. 触发器（Triggers）

4.1 MySQL触发器

-- 创建审计触发器
DELIMITER //
CREATE TRIGGER employee_audit_trigger
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (
        employee_id,
        old_salary,
        new_salary,
        change_date,
        changed_by
    ) VALUES (
        NEW.employee_id,
        OLD.salary,
        NEW.salary,
        NOW(),
        USER()
    );
END //
DELIMITER ;

4.2 PostgreSQL触发器

-- 创建触发器函数
CREATE OR REPLACE FUNCTION audit_employee_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        INSERT INTO employee_audit (
            employee_id,
            old_salary,
            new_salary,
            change_date,
            changed_by
        ) VALUES (
            NEW.employee_id,
            OLD.salary,
            NEW.salary,
            CURRENT_TIMESTAMP,
            CURRENT_USER
        );
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER employee_audit_trigger
    AFTER UPDATE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION audit_employee_changes();

5. MySQL与PostgreSQL实现差异总结

特性MySQLPostgreSQL
函数返回值必须有RETURN语句多种返回方式
存储过程8.0+完整支持11+支持，之前用函数模拟
触发器直接绑定逻辑需要先创建函数
语言支持主要是SQL和CPL/pgSQL, Python, Perl等
物化视图不支持原生支持

相关教程

使用视图来抽象复杂查询，使用函数来封装计算逻辑，使用存储过程来处理业务事务。确保适当的错误处理和安全措施。

总结

视图、函数和存储过程是数据库编程的重要组成部分：

  • 视图：提供数据抽象和安全层，简化复杂查询
  • 函数：封装计算逻辑，返回单一值
  • 存储过程：执行复杂业务逻辑，支持事务控制
  • 触发器：自动执行特定操作，用于审计和数据完整性

在MySQL和PostgreSQL中，这些功能都有很好的支持，但实现细节有所不同。选择使用哪种数据库对象应基于具体的业务需求、性能要求和安全考虑。