枚举与映射

枚举和映射是数据标准化和代码转换的重要手段。

📊 支持情况

1. MySQL 枚举类型

创建枚举表

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') NOT NULL,
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium'
);

枚举查询

-- 按枚举顺序排序
SELECT * FROM orders ORDER BY status;

-- 枚举值比较
SELECT * FROM orders WHERE status = 'pending';

2. 映射转换

CASE WHEN 映射

SELECT
    order_id,
    status,
    CASE status
        WHEN 'pending' THEN '待处理'
        WHEN 'processing' THEN '处理中'
        WHEN 'shipped' THEN '已发货'
        WHEN 'delivered' THEN '已送达'
        WHEN 'cancelled' THEN '已取消'
        ELSE '未知'
    END AS status_name
FROM orders;

复杂条件映射

SELECT
    user_id,
    amount,
    CASE
        WHEN amount < 100 THEN '小额'
        WHEN amount < 500 THEN '中等'
        WHEN amount < 1000 THEN '大额'
        ELSE '超大额'
    END AS amount_level,
    CASE
        WHEN status = 'pending' AND amount > 500 THEN '高优先级待处理'
        WHEN status = 'cancelled' THEN '已取消'
        ELSE '普通'
    END AS handling_type
FROM orders;

3. Oracle DECODE

-- DECODE语法
SELECT
    order_id,
    status,
    DECODE(status,
        'pending', '待处理',
        'processing', '处理中',
        'shipped', '已发货',
        '已取消'
    ) AS status_name
FROM orders;

-- 数字映射
SELECT
    emp_id,
    department_id,
    DECODE(department_id,
        10, '销售部',
        20, '技术部',
        30, '财务部',
        '其他部门'
    ) AS dept_name
FROM employees;

4. 映射表转换

使用JOIN映射表

-- 映射表
CREATE TABLE status_mapping (
    status_code VARCHAR(20) PRIMARY KEY,
    status_name VARCHAR(50),
    status_desc TEXT
);

-- 查询时转换
SELECT
    o.order_id,
    o.status,
    m.status_name,
    m.status_desc
FROM orders o
LEFT JOIN status_mapping m ON o.status = m.status_code;

维度表关联

SELECT
    o.order_id,
    o.status,
    d.dict_name AS status_display,
    d.dict_value
FROM orders o
JOIN dim_dict d ON o.status = d.dict_code
WHERE d.dict_type = 'order_status';

5. 实战案例

场景1：订单状态流转

SELECT
    order_id,
    status,
    CASE status
        WHEN 'pending' THEN 1
        WHEN 'processing' THEN 2
        WHEN 'shipped' THEN 3
        WHEN 'delivered' THEN 4
        WHEN 'cancelled' THEN 5
    END AS status_order,
    CASE status
        WHEN 'pending' THEN 'bg-yellow'
        WHEN 'processing' THEN 'bg-blue'
        WHEN 'shipped' THEN 'bg-purple'
        WHEN 'delivered' THEN 'bg-green'
        WHEN 'cancelled' THEN 'bg-gray'
    END AS status_class
FROM orders;

场景2：性别/等级映射

SELECT
    user_id,
    gender,
    CASE gender
        WHEN 'M' THEN '男'
        WHEN 'F' THEN '女'
        ELSE '未知'
    END AS gender_name,
    user_level,
    CASE user_level
        WHEN 1 THEN '普通会员'
        WHEN 2 THEN '银牌会员'
        WHEN 3 THEN '金牌会员'
        WHEN 4 THEN '钻石会员'
        ELSE '未知'
    END AS level_name
FROM users;

场景3：日期区间映射

SELECT
    order_id,
    order_date,
    CASE
        WHEN order_date >= CURDATE() THEN '今日'
        WHEN order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN '近7天'
        WHEN order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN '近30天'
        ELSE '更早'
    END AS order_period,
    CASE
        WHEN HOUR(order_time) < 6 THEN '凌晨'
        WHEN HOUR(order_time) < 12 THEN '上午'
        WHEN HOUR(order_time) < 18 THEN '下午'
        ELSE '晚上'
    END AS time_period
FROM orders;

场景4：多值映射

-- MySQL SET类型
CREATE TABLE user_tags (
    user_id INT,
    tags SET('vip', 'active', 'new', 'premium')
);

-- 查询
SELECT
    user_id,
    tags,
    CASE
        WHEN FIND_IN_SET('vip', tags) THEN 'VIP用户'
        ELSE '普通用户'
    END AS user_type,
    CASE
        WHEN tags LIKE '%new%' THEN '新用户'
        ELSE '老用户'
    END AS user_category
FROM user_tags;

6. 动态映射

预处理语句

-- MySQL - 动态状态转换
SET @status = 'processing';

SELECT
    CASE @status
        WHEN 'pending' THEN '待处理'
        WHEN 'processing' THEN '处理中'
        ELSE '其他'
    END AS status_name;

-- Oracle
SELECT DECODE(:status,
    'pending', '待处理',
    'processing', '处理中',
    '其他') AS status_name
FROM dual;

映射表驱动

-- 动态生成映射SQL
SELECT
    CONCAT('WHEN ''', source_value, ''' THEN ''', target_value, '''')
FROM mapping_rules
WHERE mapping_type = 'status';

⚠️ 注意事项

1. 默认值

-- 枚举默认值
INSERT INTO orders (order_id) VALUES (1);  -- status使用默认值

-- 如果NOT NULL且无默认值会报错

2. 顺序影响

-- CASE WHEN的顺序很重要
SELECT
    amount,
    CASE
        WHEN amount < 100 THEN '小额'
        WHEN amount < 500 THEN '中额'  -- 金额<500会先匹配上面
        WHEN amount < 1000 THEN '大额'
    END AS level
FROM orders;

3. 性能

-- 映射表JOIN vs CASE WHEN
-- 小数据量：CASE WHEN更快
-- 大数据量/复杂映射：映射表更易维护

📝 练习题

-- 建表
CREATE TABLE orders_enum (
    order_id INT,
    status STRING,
    payment_type STRING,
    amount DECIMAL(10,2)
);

INSERT INTO orders_enum VALUES
(1, 'pending', 'alipay', 100),
(2, 'processing', 'wechat', 200),
(3, 'shipped', 'card', 300),
(4, 'delivered', 'alipay', 400),
(5, 'cancelled', 'wechat', 500);

要求：

1. 将订单状态映射为中文
2. 将支付方式映射为中文
3. 根据金额分级（<200/200-400/>400）
4. 统计各状态订单数

-- 参考答案（MySQL）
-- 1. 状态映射
SELECT
    order_id,
    status,
    CASE status
        WHEN 'pending' THEN '待处理'
        WHEN 'processing' THEN '处理中'
        WHEN 'shipped' THEN '已发货'
        WHEN 'delivered' THEN '已送达'
        WHEN 'cancelled' THEN '已取消'
        ELSE '未知'
    END AS status_name
FROM orders_enum;

-- 2. 支付方式映射
SELECT
    order_id,
    payment_type,
    CASE payment_type
        WHEN 'alipay' THEN '支付宝'
        WHEN 'wechat' THEN '微信'
        WHEN 'card' THEN '银行卡'
        ELSE '其他'
    END AS payment_name
FROM orders_enum;

-- 3. 金额分级
SELECT
    order_id,
    amount,
    CASE
        WHEN amount < 200 THEN '小额订单'
        WHEN amount < 400 THEN '中额订单'
        ELSE '大额订单'
    END AS amount_level
FROM orders_enum;

-- 4. 状态统计
SELECT
    CASE status
        WHEN 'pending' THEN '待处理'
        WHEN 'processing' THEN '处理中'
        WHEN 'shipped' THEN '已发货'
        WHEN 'delivered' THEN '已送达'
        WHEN 'cancelled' THEN '已取消'
    END AS status_name,
    COUNT(*) AS order_count
FROM orders_enum
GROUP BY status;