如何在MySQL单字段中存储字符串列表？适配处罚系统数据存储需求

你当前使用多个固定并列字段存储处罚记录的方案，存在扩展上限低、查询/维护成本高的问题，可根据业务场景选择以下两种实现方案：

方案1：拆分独立处罚表（优先推荐，适配绝大多数业务场景） #

遵循关系型数据库的设计规范，不要把所有处罚记录塞进用户表单条字段，单独拆分处罚记录表，一条记录对应一次处罚：

-- 用户基础信息表
CREATE TABLE users (
    username VARCHAR(64) PRIMARY KEY,
    -- 其他用户相关字段：注册时间、账号状态等
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 处罚记录表
CREATE TABLE punishment_records (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(64) NOT NULL,
    punishment_type TINYINT NOT NULL COMMENT '处罚类型',
    reason TEXT COMMENT '处罚原因',
    expire_time DATETIME COMMENT '处罚到期时间',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    FOREIGN KEY (username) REFERENCES users(username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

该方案的优势：

• 无处罚数量上限，用户新增多少条处罚都可以正常存储
• 支持灵活的查询统计：比如查询某用户所有处罚、统计某类处罚的总量、查询即将到期的处罚等需求都可以通过索引实现，性能优异
• 单条处罚的修改、删除操作不会影响其他数据，维护成本极低

方案2：单字段存储处罚列表（适合不需要对处罚内容做过滤统计的场景） #

如果你的业务确实不需要对处罚的内部字段做SQL层面的查询、统计，要把所有处罚存在用户表的单个字段里，可选择以下两种实现：

2.1 JSON字段存储（MySQL 5.7及以上版本推荐） #

MySQL原生支持JSON类型，直接在用户表新增JSON类型的处罚字段，存储数组格式的处罚数据：

-- 新增处罚列表字段
ALTER TABLE users ADD COLUMN punishments JSON COMMENT '用户处罚列表';

-- 写入数据示例
UPDATE users SET punishments = '[{"type":1,"reason":"xxx"},{"type":2,"reason":"xxx"}]' WHERE username = 'Test Acc';

优势：

• 原生支持JSON格式校验，不需要在业务代码处理序列化异常
• 支持通过MySQL内置JSON函数做简单查询，比如JSON_EXTRACT(punishments, '$[0].type')可获取第一条处罚的类型
• 存储无数量限制，新增处罚直接往数组追加即可

2.2 特殊分隔符拼接字符串（兼容MySQL 5.7以下版本） #

如果使用的MySQL版本不支持JSON，可以用不会出现在处罚内容里的特殊符号做分隔符，拼接所有处罚内容存储：

-- 写入示例：不同处罚用|分隔，处罚内部属性用,分隔
UPDATE users SET punishments = 'type:1,reason:xxx|type:2,reason:xxx' WHERE username = 'Test Acc';

注意：该方案需要业务代码自行处理序列化、反序列化逻辑，不支持在SQL层查询处罚内部属性，仅适合非常简单的纯存储场景。

内容的提问来源于stack exchange，提问作者KingsDev