#MySQL与PostgreSQL新特性对比 - 2024年最新功能分析

-- JSON数据类型与基本操作
CREATE TABLE user_profiles (
    id INT PRIMARY KEY,
    profile JSON
);

INSERT INTO user_profiles VALUES (
    1, 
    JSON_OBJECT(
        'name', 'John Doe',
        'skills', JSON_ARRAY('Python', 'JavaScript')
    )
);

-- JSON查询与索引（通过虚拟列实现）
SELECT id, JSON_EXTRACT(profile, '$.name') AS name
FROM user_profiles
WHERE JSON_EXTRACT(profile, '$.age') > 25;

ALTER TABLE user_profiles 
ADD COLUMN skill_index VARCHAR(50) 
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.skills[0]'))) STORED;
CREATE INDEX idx_primary_skill ON user_profiles(skill_index);

-- JSONB数据类型（推荐，二进制存储性能更好）
CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    profile_jsonb JSONB
);

INSERT INTO user_profiles (profile_jsonb) VALUES (
    '{
        "name": "John Doe",
        "skills": ["Python", "JavaScript"]
    }'::jsonb
);

-- 丰富的查询操作符
SELECT 
    id,
    profile_jsonb->>'name' AS name,
    profile_jsonb->'skills' AS skills
FROM user_profiles
WHERE profile_jsonb @> '{"skills": ["Python"]}'::jsonb;

-- 原生GIN索引支持
CREATE INDEX idx_profile_gin ON user_profiles USING GIN (profile_jsonb);

-- 窗口函数示例
SELECT 
    employee_id,
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

-- 递归CTE
WITH RECURSIVE org_hierarchy AS (
    SELECT employee_id, manager_id, 1 as level FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, oh.level + 1 
    FROM employees e JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT * FROM org_hierarchy;

-- 更丰富的窗口函数
SELECT 
    employee_id,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) AS salary_percentile,
    NTILE(4) OVER (ORDER BY salary) AS salary_quartile
FROM employees;

-- 支持MATERIALIZED选项
WITH large_dataset AS MATERIALIZED (
    SELECT * FROM very_large_table WHERE condition = 'value'
)
SELECT * FROM large_dataset WHERE other_condition = 'other_value';

-- 范围分区
CREATE TABLE sales (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 声明式分区
CREATE TABLE sales (
    id SERIAL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (sale_date);

-- 创建分区
CREATE TABLE sales_2022 PARTITION OF sales
    FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

-- 自动索引传播
CREATE INDEX idx_sales_amount ON sales (amount);

-- PostgreSQL并行查询示例
EXPLAIN (ANALYZE, BUFFERS) 
SELECT department_id, COUNT(*), AVG(salary)
FROM employees 
GROUP BY department_id;

-- 密码强度插件与账户管理
INSTALL COMPONENT 'file://component_validate_password';
SET GLOBAL validate_password.policy = MEDIUM;

CREATE USER 'app_user'@'%' 
IDENTIFIED BY 'StrongPassword123!'
PASSWORD EXPIRE INTERVAL 90 DAY
FAILED_LOGIN_ATTEMPTS 3;

-- 行级安全（RLS）
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

CREATE POLICY employee_isolation_policy ON employees
    FOR ALL
    TO app_user
    USING (department_id = current_setting('app.current_dept')::integer);

-- 不可见索引
CREATE INDEX idx_invisible ON employees (hire_date) INVISIBLE;

-- 直方图统计
ANALYZE TABLE employees UPDATE HISTOGRAM ON salary, department_id;

-- 部分索引
CREATE INDEX idx_active_employees ON employees (hire_date) 
WHERE status = 'active';

-- 覆盖索引
CREATE INDEX idx_covering ON employees (department_id, salary) 
INCLUDE (first_name, last_name);

-- PostgreSQL常用扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;  -- 查询监控
CREATE EXTENSION IF NOT EXISTS pg_trgm;           -- 文本相似度
CREATE EXTENSION IF NOT EXISTS postgis;            -- 地理信息