MySQL 常用业务 SQL

这篇更贴近“日常写业务”的 SQL不讲理论强调能直接拿去改。一、按时间范围的常用查询-- 最近 7 天订单SELECT*FROMordersWHEREcreate_timeNOW()-INTERVAL7DAY;-- 指定日期SELECT*FROMordersWHEREcreate_time2026-04-01 00:00:00ANDcreate_time2026-04-02 00:00:00;二、状态机更新防止重复更新-- 只允许从 UNPAID - PAIDUPDATEordersSETstatusPAID,pay_timeNOW()WHEREid?ANDstatusUNPAID;三、库存扣减防止超卖UPDATEstockSETquantityquantity-1WHEREsku_id?ANDquantity0;配套判断受影响行数为 0 就表示库存不足。四、批量更新避免一条条改UPDATEuserSETstatusDISABLEDWHERElast_loginNOW()-INTERVAL180DAY;五、存在即更新UPSERTINSERTINTOuser_profile(user_id,nickname,avatar)VALUES(?,?,?)ONDUPLICATEKEYUPDATEnicknameVALUES(nickname),avatarVALUES(avatar);六、批量插入提高吞吐INSERTINTOaudit_log(user_id,action,create_time)VALUES(101,LOGIN,NOW()),(102,LOGIN,NOW()),(103,LOGIN,NOW());七、分页查询业务常规-- 传统分页页数不大可用SELECTid,user_id,status,create_timeFROMordersORDERBYcreate_timeDESCLIMIT0,20;-- 游标分页更稳SELECTid,user_id,status,create_timeFROMordersWHERE(create_time?OR(create_time?ANDid?))ORDERBYcreate_timeDESC,idDESCLIMIT20;八、模糊搜索建议加前缀-- 前缀匹配可用索引SELECT*FROMuserWHEREnicknameLIKE张%;%张%这种全模糊通常走不到索引。九、查重业务常用SELECTemail,COUNT(*)AScntFROMuserGROUPBYemailHAVINGcnt1;十、取某用户最新一条记录SELECT*FROMlogin_logWHEREuser_id?ORDERBYcreate_timeDESCLIMIT1;十一、报表类统计按天聚合SELECTDATE(create_time)ASday,COUNT(*)AScntFROMordersWHEREcreate_timeNOW()-INTERVAL30DAYGROUPBYdayORDERBYday;十二、快速排除无效数据SELECT*FROMordersWHEREstatusIN(PAID,SHIPPED)ANDdeleted0;最后总结这类业务 SQL 的核心是三点写法清晰、能命中索引更新条件有约束避免重复或越界批量操作优先减少往返成本