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

-- 创建包含 JSON 列的表
CREATE TABLE user_profiles (
    id INT PRIMARY KEY,
    profile JSON
);

-- 插入 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;

-- 索引实现：通过生成列构建 B-Tree 索引
ALTER TABLE user_profiles 
ADD COLUMN primary_skill VARCHAR(50) 
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.skills[0]'))) STORED;
CREATE INDEX idx_primary_skill ON user_profiles(primary_skill);

-- 创建表并选择 jsonb 类型
CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    profile_jsonb 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       -- 提取 JSON 数组
FROM user_profiles
WHERE profile_jsonb @> '{"skills": ["Python"]}'::jsonb;  -- 包含查询

-- 原生 GIN 索引，直接加速 JSON 内部元素检索
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;

-- 物化CTE：将子查询结果暂时固化，避免重复计算
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 TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2024-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;

-- 开启行级安全
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;               -- 地理空间数据支持