MySQL ENUM类型：小而美的数据约束利器，你用对了吗？

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    size ENUM('S', 'M', 'L', 'XL')  -- 就像做选择题
);

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    gender ENUM('男','女','未知') DEFAULT '未知' COMMENT '用户性别：男/女/未知',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 烂设计：任由用户随便输入
-- ALTER TABLE users ADD gender VARCHAR(10);
-- 好设计：ENUM约束
-- ALTER TABLE users ADD gender ENUM('男','女','未知');
-- 明确指定性别
INSERT INTO users (username, gender) VALUES ('张三', '男');
-- 使用默认值
INSERT INTO users (username) VALUES ('李四');  -- gender将自动设为'未知'
-- 使用数字值插入（性能更优）
-- '男'=1, '女'=2, '未知'=3
INSERT INTO users (username, gender) VALUES ('王五', 2);  -- 插入'女'
-- 批量插入
INSERT INTO users (username, gender) VALUES 
('赵六', '男'),
('钱七', '女'),
('孙八', DEFAULT);
-- 查询所有男性用户
SELECT * FROM users WHERE gender = '男';
-- 查询非男性用户
SELECT * FROM users WHERE gender != '男';
-- 查询未设置性别的用户
SELECT * FROM users WHERE gender = '未知';
-- 使用数字索引查询
-- 查询女性用户（'女'=2）
SELECT * FROM users WHERE gender = 2;
-- 按ENUM定义顺序排序（男=1 < 女=2 < 未知=3）
SELECT * FROM users ORDER BY gender;
-- 修改用户性别
UPDATE users SET gender = '女' WHERE username = '张三';
-- 使用数字值更新
UPDATE users SET gender = 3 WHERE id = 5;  -- 设为'未知'
-- 将所有'未知'性别改为'女'
UPDATE users SET gender = '女' WHERE gender = '未知';
-- 基于条件的性别更新
UPDATE users 
SET gender = CASE 
    WHEN username LIKE '张%' THEN '男'
    WHEN username LIKE '李%' THEN '女'
    ELSE gender
END;

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(20) NOT NULL COMMENT '订单编号',
    user_id INT NOT NULL COMMENT '用户ID',
    amount DECIMAL(10,2) NOT NULL COMMENT '订单金额',
    status ENUM('待支付','已支付','配送中','已完成','已取消') DEFAULT '待支付' COMMENT '订单状态',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_status (status),
    INDEX idx_user (user_id)
) ENGINE=InnoDB COMMENT='订单表';
-- 插入新订单（使用默认状态）
INSERT INTO orders (order_no, user_id, amount) 
VALUES ('ORD20230001', 1001, 299.00);
-- 明确指定状态
INSERT INTO orders (order_no, user_id, amount, status)
VALUES ('ORD20230002', 1002, 599.00, '已支付');
-- ENUM索引值：'待支付'=1, '已支付'=2, '配送中'=3, '已完成'=4, '已取消'=5
INSERT INTO orders (order_no, user_id, amount, status)
VALUES ('ORD20230003', 1003, 199.00, 3);  -- 配送中
-- 批量插入
INSERT INTO orders (order_no, user_id, amount, status) VALUES
('ORD20230004', 1004, 899.00, '待支付'),
('ORD20230005', 1005, 1299.00, 2),  -- 已支付
('ORD20230006', 1006, 399.00, DEFAULT);
-- 查询特定状态的订单
SELECT * FROM orders WHERE status = '已支付';
-- 查询非完成状态的订单
SELECT * FROM orders WHERE status != '已完成';
-- 查询待支付或已支付的订单
SELECT * FROM orders WHERE status IN ('待支付', '已支付');
-- 查询配送中的订单（'配送中'=3）
SELECT * FROM orders WHERE status = 3;
-- 从待支付到已支付
UPDATE orders 
SET status = '已支付' 
WHERE id = 1 AND status = '待支付';
-- 使用数字值更新
UPDATE orders 
SET status = 4  -- 已完成
WHERE id = 2;
-- 将超时未支付订单设为已取消
UPDATE orders 
SET status = '已取消' 
WHERE status = '待支付' 
AND created_at < NOW() - INTERVAL 30 MINUTE;
-- 批量推进配送状态
UPDATE orders 
SET status = CASE 
    WHEN status = '已支付' THEN '配送中'
    WHEN status = '配送中' THEN '已完成'
    ELSE status
END
WHERE id IN (1001, 1002, 1003);
-- 删除所有已取消的订单
DELETE FROM orders WHERE status = '已取消';
-- 删除30天前的已完成订单
DELETE FROM orders 
WHERE status = '已完成' 
AND updated_at < NOW() - INTERVAL 30 DAY;

-- 尺寸编码主表
CREATE TABLE size_codes (
    code ENUM('XS','S','M','L','XL') PRIMARY KEY COMMENT '标准尺寸编码'
) ENGINE=InnoDB;


-- 尺寸翻译表
CREATE TABLE size_translations (
    code ENUM('XS','S','M','L','XL') COMMENT '关联尺寸编码',
    lang VARCHAR(10) NOT NULL COMMENT '语言代码(如zh-CN,en-US)',
    name VARCHAR(50) NOT NULL COMMENT '本地化尺寸名称',
    PRIMARY KEY (code, lang),
    FOREIGN KEY (code) REFERENCES size_codes(code)
) ENGINE=InnoDB;


-- 单条插入
INSERT INTO size_codes (code) VALUES ('XS');


-- 批量插入所有尺寸
INSERT INTO size_codes (code) VALUES 
('XS'), ('S'), ('M'), ('L'), ('XL');


-- 中文翻译
INSERT INTO size_translations (code, lang, name) VALUES
('XS', 'zh-CN', '加小号'),
('S', 'zh-CN', '小号'),
('M', 'zh-CN', '中号'),
('L', 'zh-CN', '大号'),
('XL', 'zh-CN', '加大号');


-- 英文翻译
INSERT INTO size_translations (code, lang, name) VALUES
('XS', 'en-US', 'Extra Small'),
('S', 'en-US', 'Small'),
('M', 'en-US', 'Medium'),
('L', 'en-US', 'Large'),
('XL', 'en-US', 'Extra Large');


-- 查询所有尺寸编码
SELECT * FROM size_codes;


-- 查询特定语言的翻译
SELECT * FROM size_translations WHERE lang = 'zh-CN';


-- 查询某个尺寸的所有翻译
SELECT * FROM size_translations WHERE code = 'M';


-- 获取所有尺寸的完整翻译信息
SELECT sc.code, st.lang, st.name
FROM size_codes sc
LEFT JOIN size_translations st ON sc.code = st.code
ORDER BY sc.code, st.lang;


-- 获取特定语言的尺寸显示名称
SELECT sc.code, st.name 
FROM size_codes sc
JOIN size_translations st ON sc.code = st.code
WHERE st.lang = 'en-US';


-- 'XS'=1, 'S'=2, ..., 'XL'=5
SELECT * FROM size_translations WHERE code = 3;  -- 查询M号


-- 修改中文XL的翻译
UPDATE size_translations 
SET name = '特大号' 
WHERE code = 'XL' AND lang = 'zh-CN';


-- 使用数字值更新
UPDATE size_translations
SET name = 'Extra Large (Big)'
WHERE code = 5 AND lang = 'en-US';  -- XL=5


-- 添加日语翻译
INSERT INTO size_translations (code, lang, name) VALUES
('XS', 'ja-JP', 'エクストラスモール'),
('S', 'ja-JP', 'スモール'),
('M', 'ja-JP', 'ミディアム'),
('L', 'ja-JP', 'ラージ'),
('XL', 'ja-JP', 'エクストララージ')
ON DUPLICATE KEY UPDATE name = VALUES(name);


-- 删除特定语言的翻译
DELETE FROM size_translations WHERE lang = 'ja-JP';


-- 删除特定尺寸的所有翻译
DELETE FROM size_translations WHERE code = 'XL';

-- 尺寸选项表
CREATE TABLE product_sizes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(10) NOT NULL UNIQUE COMMENT '尺寸代码(如XS/S/M)',
    description VARCHAR(100) COMMENT '详细描述',
    sort_order INT DEFAULT 0 COMMENT '排序字段',
    is_active BOOLEAN DEFAULT TRUE COMMENT '是否可用',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB COMMENT='产品尺寸选项表';


-- 产品表
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    size_id INT COMMENT '关联尺寸ID',
    price DECIMAL(10,2),
    FOREIGN KEY (size_id) REFERENCES product_sizes(id),
    INDEX idx_size (size_id)
) ENGINE=InnoDB;


-- 插入基础尺寸选项
INSERT INTO product_sizes (code, description, sort_order) VALUES
('XS', 'Extra Small', 1),
('S', 'Small', 2),
('M', 'Medium', 3),
('L', 'Large', 4),
('XL', 'Extra Large', 5);


-- 添加产品并关联尺寸
INSERT INTO products (name, size_id, price) VALUES
('纯棉T恤', 3, 99.00),  -- M号
('修身牛仔裤', 2, 199.00), -- S号
('oversize卫衣', 5, 159.00); -- XL号


-- 查询所有产品及尺寸信息
SELECT p.id, p.name, s.code AS size, p.price
FROM products p
LEFT JOIN product_sizes s ON p.size_id = s.id;


-- 查询特定尺寸的产品
SELECT p.name, p.price 
FROM products p
JOIN product_sizes s ON p.size_id = s.id
WHERE s.code = 'M';


-- 带条件的分页查询
SELECT p.name, s.code, p.price
FROM products p
JOIN product_sizes s ON p.size_id = s.id
WHERE s.is_active = TRUE
ORDER BY s.sort_order, p.name
LIMIT 10 OFFSET 0;


-- 添加新尺寸
INSERT INTO product_sizes (code, description) 
VALUES ('XXL', 'Extra Extra Large');


-- 添加新产品并关联尺寸
INSERT INTO products (name, size_id, price)
SELECT '加厚羽绒服', id, 599.00
FROM product_sizes WHERE code = 'XXL';


-- 修改产品尺寸
UPDATE products p
JOIN product_sizes s ON p.size_id = s.id
SET p.size_id = (SELECT id FROM product_sizes WHERE code = 'L')
WHERE p.name = '纯棉T恤';


-- 更新尺寸描述
UPDATE product_sizes
SET description = '特小号', updated_at = NOW()
WHERE code = 'XS';


-- 标记删除尺寸(软删除)
UPDATE product_sizes
SET is_active = FALSE
WHERE code = 'XS';


-- 硬删除尺寸(需先解除关联)
UPDATE products SET size_id = NULL WHERE size_id = 1;
DELETE FROM product_sizes WHERE id = 1;