mysql如何实现消息已读未读_mysql状态设计实战

is_read字段需建联合索引(user_id, is_read, created_at)，禁用enum/字符串存状态，采用泛化引用ref_type+ref_id替代外键，content用json类型存储扁平结构，时间字段统一用timestamp并启用自动更新。

用 is_read 字段最直接，但别只建个 TINYINT 就完事

绝大多数人第一反应是加个 is_read 字段，TINYINT(1) 存 0/1 —— 这没错，但错在只停在这一步。真实业务里，“未读数”不是查一次就完的事：首页角标要实时显示、下拉刷新要精准加载未读、批量已读操作要原子性、后台补发推送还要靠它捞数据。如果没提前设计好索引和状态流转逻辑，等用户量上万、消息日均百万，select count(*) FROM notifications WHERE user_id = ? AND is_read = 0 就会开始慢得肉眼可见。

• is_read 必须单独建索引，尤其当你要做「未读总数统计」或「按未读优先排序」时
• 更推荐建联合索引 (user_id, is_read, created_at)：覆盖「查某人所有未读消息并倒序排」这个最高频查询，避免回表
• 别用 ENUM 或字符串存状态（比如 'read'/'unread'），mysql 对字符串比较比整型慢，且无法利用索引前缀匹配

别把 sender_id 和业务主键当外键硬关联

看到订单通知，很多人顺手加 order_id 字段并设为外键。问题来了：订单删了，通知还得留着——用户点进去提示“订单不存在”，体验崩坏；更糟的是，外键约束会让删除订单变慢，甚至锁表。这不是设计，是埋雷。

• 改用泛化引用：ref_type VARCHAR(32)（如 'order'、'comment'） + ref_id BIGINT（如 12345）
• ref_id 不加外键，但业务层写入时必须校验存在性（比如发通知前查一遍订单是否有效）
• 系统通知可让 sender_id 为 NULL 或固定值（如 0），别强行塞“系统管理员”ID

JSON 存内容比纯文本更可控，但别滥用

content 字段如果只存“您有新订单”，那确实 VARCHAR 就够了。但一旦要支持多语言文案、按钮跳转、富文本摘要、甚至前端渲染模板，纯文本就扛不住了。这时候 JSON 不是炫技，是刚需。

• 用 JSON 类型（MySQL 5.7+）而非 TEXT：数据库能校验格式、支持路径查询（如 content->>'$.title'）
• 结构建议扁平化：{"title": "支付成功", "body": "订单 #123 已完成", "action": "/order/123"}，别嵌套过深
• 别把大图 Base64、长 HTML 片段塞进 JSON——这些该存在对象存储，JSON 里只放 URL

时间字段必须用 TIMESTAMP，且带自动更新

created_at 用 DATETIME 还是 TIMESTAMP？选后者。不是因为“看起来高级”，而是它真能省事：自动时区转换、占 4 字节更省空间、配合 ON UPDATE CURRENT_TIMESTAMP 能让 updated_at 精准记录“谁点开这条消息”的那一刻。

• created_at TIMESTAMP default CURRENT_TIMESTAMP
• updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
• 别手动在应用层写 updated_at = NOW() —— 容易漏、不一致、并发时可能被覆盖
• 如果需要保留原始创建时区（比如审计要求），再额外加 created_at_utc 字段，但日常展示全用 created_at

实际最难的从来不是字段怎么命名，而是想清楚：这条通知，三年后用户翻历史记录时，还能不能点开跳转？点击行为会不会触发其他状态变更？未读数不准时，排查路径是不是三行 SQL 就能定位？这些才是压在表结构底下的真实重量。