数据库专题07评论与删除策略——外键、软删除和权限必须一起设计删除评论不是简单的delete from comments。用户需要看到“该评论已删除”审计需要知道谁删除文章删除时又要决定评论是否一起清理。本篇为评论增加软删除字段用 SQL 同时完成所有权判断和状态改变并讨论文章级联删除、匿名化和数据保留的取舍。上一篇练习讲解标签练习的关键是中间表复合主键和规范化标签名。删除一篇文章只应级联删除它的article_tags仍被其他文章引用的标签必须保留。标签文章列表按(created_at,id)游标分页不能把所有关联一次加载进内存。1. 评论表事实和显示状态分开createtablecomments(id bigserialprimarykey,article_idbigintnotnullreferencesarticles(id)ondeletecascade,user_idbigintnotnullreferencesusers(id),contenttextnotnull,created_at timestamptznotnulldefaultnow(),deleted_at timestamptz,deleted_bybigintreferencesusers(id),delete_reasonvarchar(200),constraintcomment_delete_statecheck((deleted_atisnullanddeleted_byisnull)or(deleted_atisnotnullanddeleted_byisnotnull)));createindexidx_comments_article_createdoncomments(article_id,created_atdesc)wheredeleted_atisnull;正文保留用于审计但公开 API 在deleted_at非空时只返回“该评论已删除”不能继续暴露原文。如果隐私政策要求彻底删除后台任务可在保留期后匿名化content审计事件只记录 id 和原因。2. 发布评论fromsqlalchemyimporttextdefadd_comment(conn,article_id:int,user_id:int,content:str)-int:只允许对已发布文章评论避免草稿被猜 id 后写入。contentcontent.strip()ifnotcontentorlen(content)2000:raiseValueError(评论需为 1~2000 个字符)rowconn.execute(text( insert into comments(article_id,user_id,content) select id,:user_id,:content from articles where id:article_id and statuspublished returning id ),{article_id:article_id,user_id:user_id,content:content}).first()ifrowisNone:raiseLookupError(文章不存在或未发布)returnrow.idinsert ... select把文章状态校验和插入放进一条语句减少并发窗口。外键仍负责用户和文章存在性。3. 作者删除与管理员删除defdelete_comment(conn,comment_id:int,current_user:dict,reason:str)-None:评论作者可删除自己评论管理员可删除任意评论。重复删除返回幂等成功。ifcurrent_user[role]admin:conditionid:id and deleted_at is nullelse:conditionid:id and user_id:user_id and deleted_at is nullresultconn.execute(text(f update comments set deleted_atnow(), deleted_by:user_id, delete_reason:reason where{condition}),{id:comment_id,user_id:current_user[id],reason:reason[:200]})ifresult.rowcount0:existingconn.execute(text(select deleted_at from comments where id:id),{id:comment_id}).first()ifexistingandexisting.deleted_at:returnraisePermissionError(评论不存在或无权删除)这里的 f-string 只拼接程序内部固定条件不含用户输入。角色来自登录会话不允许请求体传roleadmin。4. 评论列表和计数selectc.id,c.created_at,u.email,casewhenc.deleted_atisnullthenc.contentelse该评论已删除endascontentfromcomments cjoinusers uonu.idc.user_idwherec.article_id:article_idorderbyc.created_atasc,c.idasclimit:size;selectarticle_id,count(*)fromcommentswheredeleted_atisnullgroupbyarticle_id;详情时间线保留删除占位报表计数排除软删除。两种查询访问模式不同索引需要结合EXPLAIN验证。验收与故障排查草稿文章评论 - LookupError 发布文章评论 - comment_id1 其他用户删除 - PermissionError 本人删除两次 - 第二次幂等成功deleted_at 不变化 公开列表 - 只显示“该评论已删除”如果删除后计数仍增加检查所有聚合是否都包含deleted_at is null如果外键阻止删除用户需要在产品层决定禁止删用户、匿名化用户还是on delete set null不要临时禁用外键。课后练习实现评论游标分页和管理员删除审计事件增加“30 天后把已删正文替换为[removed]”的批处理并保证重复运行安全。下一篇用唯一约束实现幂等点赞。 
长河君 · 2026/9/10 23:13:16 · 阅读 1.2k 数据库专题07：评论与删除策略——外键、软删除和权限必须一起设计
数据库专题07评论与删除策略——外键、软删除和权限必须一起设计删除评论不是简单的delete from comments。用户需要看到“该评论已删除”审计需要知道谁删除文章删除时又要决定评论是否一起清理。本篇为评论增加软删除字段用 SQL 同时完成所有权判断和状态改变并讨论文章级联删除、匿名化和数据保留的取舍。上一篇练习讲解标签练习的关键是中间表复合主键和规范化标签名。删除一篇文章只应级联删除它的article_tags仍被其他文章引用的标签必须保留。标签文章列表按(created_at,id)游标分页不能把所有关联一次加载进内存。1. 评论表事实和显示状态分开createtablecomments(id bigserialprimarykey,article_idbigintnotnullreferencesarticles(id)ondeletecascade,user_idbigintnotnullreferencesusers(id),contenttextnotnull,created_at timestamptznotnulldefaultnow(),deleted_at timestamptz,deleted_bybigintreferencesusers(id),delete_reasonvarchar(200),constraintcomment_delete_statecheck((deleted_atisnullanddeleted_byisnull)or(deleted_atisnotnullanddeleted_byisnotnull)));createindexidx_comments_article_createdoncomments(article_id,created_atdesc)wheredeleted_atisnull;正文保留用于审计但公开 API 在deleted_at非空时只返回“该评论已删除”不能继续暴露原文。如果隐私政策要求彻底删除后台任务可在保留期后匿名化content审计事件只记录 id 和原因。2. 发布评论fromsqlalchemyimporttextdefadd_comment(conn,article_id:int,user_id:int,content:str)-int:只允许对已发布文章评论避免草稿被猜 id 后写入。contentcontent.strip()ifnotcontentorlen(content)2000:raiseValueError(评论需为 1~2000 个字符)rowconn.execute(text( insert into comments(article_id,user_id,content) select id,:user_id,:content from articles where id:article_id and statuspublished returning id ),{article_id:article_id,user_id:user_id,content:content}).first()ifrowisNone:raiseLookupError(文章不存在或未发布)returnrow.idinsert ... select把文章状态校验和插入放进一条语句减少并发窗口。外键仍负责用户和文章存在性。3. 作者删除与管理员删除defdelete_comment(conn,comment_id:int,current_user:dict,reason:str)-None:评论作者可删除自己评论管理员可删除任意评论。重复删除返回幂等成功。ifcurrent_user[role]admin:conditionid:id and deleted_at is nullelse:conditionid:id and user_id:user_id and deleted_at is nullresultconn.execute(text(f update comments set deleted_atnow(), deleted_by:user_id, delete_reason:reason where{condition}),{id:comment_id,user_id:current_user[id],reason:reason[:200]})ifresult.rowcount0:existingconn.execute(text(select deleted_at from comments where id:id),{id:comment_id}).first()ifexistingandexisting.deleted_at:returnraisePermissionError(评论不存在或无权删除)这里的 f-string 只拼接程序内部固定条件不含用户输入。角色来自登录会话不允许请求体传roleadmin。4. 评论列表和计数selectc.id,c.created_at,u.email,casewhenc.deleted_atisnullthenc.contentelse该评论已删除endascontentfromcomments cjoinusers uonu.idc.user_idwherec.article_id:article_idorderbyc.created_atasc,c.idasclimit:size;selectarticle_id,count(*)fromcommentswheredeleted_atisnullgroupbyarticle_id;详情时间线保留删除占位报表计数排除软删除。两种查询访问模式不同索引需要结合EXPLAIN验证。验收与故障排查草稿文章评论 - LookupError 发布文章评论 - comment_id1 其他用户删除 - PermissionError 本人删除两次 - 第二次幂等成功deleted_at 不变化 公开列表 - 只显示“该评论已删除”如果删除后计数仍增加检查所有聚合是否都包含deleted_at is null如果外键阻止删除用户需要在产品层决定禁止删用户、匿名化用户还是on delete set null不要临时禁用外键。课后练习实现评论游标分页和管理员删除审计事件增加“30 天后把已删正文替换为[removed]”的批处理并保证重复运行安全。下一篇用唯一约束实现幂等点赞。