收藏功能必须用多对多关联表建模，不可用JSON或逗号分隔存储；正确做法是创建user_favorites表，设联合主键防重、双向外键级联删除、双字段索引优化查询，并用INSERT...ON DUPLICATE KEY UPDATE或EXISTS高效实现增删与状态判断。
收藏功能必须用多对多关系建模
用户可以收藏多个商品，一个商品也能被多个用户收藏——这是典型的多对多（many-to-many）场景。直接在 users 表加 favorite_items 字段（比如存 JSON 或逗号分隔 ID）看似简单，但会导致查询慢、无法索引、事务难保证、不支持去重和时间排序等硬伤。
正确做法是单独建一张关联表：
CREATE TABLE user_favorites ( user_id BIGINT NOT NULL, item_id BIGINT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (user_id, item_id), INDEX idx_item_id (item_id), FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE );
注意点：
-
PRIMARY KEY (user_id, item_id)天然防重复，避免同一用户反复收藏同一商品 - 必须给
item_id加索引，否则「查某商品被谁收藏」会全表扫描 -
ON DELETE CASCADE确保被删的商品/用户自动清理收藏记录，避免脏数据
查用户收藏列表要带关联数据和排序
单纯查 user_favorites 表只拿到 ID，实际业务中需要商品名称、图片、价格等。必须用 JOIN 关联主表，并按收藏时间倒序——新收藏的排前面。
示例：查用户 ID 为 123 的最近 20 条收藏
SELECT u.id, u.title, u.price, u.cover_url, f.created_at FROM user_favorites f JOIN items u ON f.item_id = u.id WHERE f.user_id = 123 ORDER BY f.created_at DESC LIMIT 20;
常见错误：
- 漏加
ORDER BY：MySQL 不保证自然顺序，不显式排序结果不可靠 - 用
LEFT JOIN替代JOIN：如果items表里有已删除商品，LEFT JOIN会让结果里出现NULL字段，应先确保外键约束或加WHERE u.deleted_at IS NULL - 没限制
LIMIT：用户收藏量大时，不翻页会拖垮数据库
添加/取消收藏要用 INSERT … ON DUPLICATE KEY UPDATE 或 REPLACE
用户点击“收藏”按钮，后端不能先查再插（存在竞态条件），也不能无脑 INSERT（会因主键冲突报错 1062 Duplicate entry）。
推荐方案一（推荐）：INSERT … ON DUPLICATE KEY UPDATE，既幂等又保留原时间戳
INSERT INTO user_favorites (user_id, item_id) VALUES (123, 456) ON DUPLICATE KEY UPDATE created_at = VALUES(created_at);
推荐方案二：REPLACE INTO（本质是 DELETE + INSERT），会更新 created_at 为当前时间，适合想“刷新收藏时间”的场景
REPLACE INTO user_favorites (user_id, item_id) VALUES (123, 456);
别用 INSERT IGNORE：它静默忽略冲突，但无法区分“已存在”和“其他错误”，不利于排查
判断某商品是否已被收藏需用 EXISTS 而非 COUNT
前端常需显示「已收藏」按钮状态，即检查 user_id=123 是否收藏过 item_id=456。用 COUNT(*) > 0 是典型低效写法——MySQL 会扫完整匹配行再计数。
正确写法是 EXISTS，找到第一条就返回，极快：
SELECT EXISTS( SELECT 1 FROM user_favorites WHERE user_id = 123 AND item_id = 456 ) AS is_favorited;
返回结果是 1 或 0，可直接映射到布尔值。这个查询在有联合主键时能走索引，毫秒级响应。
容易被忽略的点：
- 别在应用层做两次查询（先查再判断），网络开销和逻辑复杂度都高
- 别把
is_favorited当成字段塞进大列表查询里——那是 N+1 查询陷阱，应该用LEFT JOIN预加载，或用IN批量查收藏状态
category（分类收藏夹）、note（备注）、is_archived（归档）。但核心原则不变——先稳住多对多结构，再按需加字段，别一开始就被扩展需求带偏基础设计。 
