SQL 增删改查：和 dao 层代码怎么对应

个人主页会编程的土豆欢迎来访作者简介后端学习者❄️个人专栏数据结构与算法数据库leetcode✨那些你一个人走过的夜路终将化作照亮未来的光系列《影院票务 GO》知识点博客 · 第 7 篇对应dao/*.go中的几乎所有 SQL读完你能讲清INSERT/SELECT/UPDATE/DELETE 各干什么、Go 里怎么写、本项目每张表落在哪写在前面第 6 篇我们认识了 MySQL 的「库、表、行、主键」。但光有表结构还不够——程序真正和数据库打交道靠的是SQL 语句。SQLStructured Query Language是和关系数据库说话的语言。日常开发里说的CRUD就是四类基本操作英文SQL中文直觉CreateINSERT新增一行ReadSELECT查询数据UpdateUPDATE修改已有行DeleteDELETE删除行在本项目里这些 SQL 几乎全部写在dao/目录下。Handler 负责「接 HTTP 请求、校验参数」dao 负责「把业务动作翻译成 SQL 并执行」。本篇把四类语句讲透并逐一对照票务项目里的真实代码同时拓展WHERE、JOIN、LIKE、参数化查询、聚合与子查询。1. 先建立分层图景SQL 在项目里住哪浏览器 / JS ↓ HTTP handlers/*.go ← 解析请求、鉴权、调 dao ↓ 函数调用 dao/*.go ← 写 SQL、Scan 进 struct ↓ database/sql MySQL (ttms 库)答辩时可以这样说「我们刻意把 SQL 收敛在 dao 层。Handler 不出现裸 SQL以后换存储或加缓存改 dao 就行。」dao 目录按业务拆分文件主要表典型操作dao/user.gousers注册 INSERT、登录 SELECTdao/movie.gomovies、movie_groups电影 CRUD、搜索、推荐dao/schedule.goschedules、halls、seats场次、影厅、座位dao/order.goorders、seats下单、支付、取消多步 UPDATEdao/comment.gocomments、ratings评论 INSERT、评分 UPSERT2. INSERT创建新行2.1 语法骨架INSERT INTO 表名(列1, 列2, ...) VALUES(值1, 值2, ...)插入成功后MySQL 会给自增主键分配一个新id。2.2 Go 里怎么写标准写法是db.DB.Execres, err : db.DB.Exec( INSERT INTO users(username,password,salt,nickname,phone,role) VALUES(?,?,?,?,?,?), u.Username, u.Password, u.Salt, u.Nickname, u.Phone, u.Role, ) if err ! nil { return err } u.ID, _ res.LastInsertId()对应项目dao/user.go的CreateUser。要点?是占位符值由驱动在发送前绑定顺序必须和 SQL 里一致。LastInsertId()取刚插入行的自增 ID写回 struct后面 Session、外键引用要用。Exec适合不返回结果集的语句INSERT/UPDATE/DELETE。2.3 项目里的 INSERT 地图场景函数SQL 目标用户注册CreateUserusers新建电影分组CreateGroupmovie_groups新建电影CreateMoviemovies新建影厅CreateHallhalls新建场次CreateScheduleschedules初始化座位InitSeatsForScheduleseats循环 INSERT创建订单CreateOrderWithSeatsorders UPDATE 座位发评论CreateCommentcomments评分UpsertRatingratings见后文 UPSERT场次 座位是一个典型组合管理员在后台保存场次 →CreateSchedule插入一行 → 立刻InitSeatsForSchedule按影厅行列批量插入座位。stmt, err : tx.Prepare(INSERT INTO seats(schedule_id,row_no,col_no,status) VALUES(?,?,?,0)) for r : 1; r hall.RowsNum; r { for c : 1; c hall.ColsNum; c { stmt.Exec(scheduleID, r, c) } }这里用了Prepare预编译同一 SQL 重复执行更高效——第 8 篇会细讲。3. SELECT查询数据查询是 dao 里出现频率最高的操作。Go 标准库提供三种入口API期望行数典型用途QueryRow0 或 1 行按用户名查用户、按 id 查电影Query0 到多行电影列表、订单列表、座位图Exec不返回行仅改数据时用3.1 QueryRow Scan查单行u : models.User{} err : db.DB.QueryRow( SELECT id,username,password,salt,nickname,phone,role,created_at FROM users WHERE username?, username, ).Scan(u.ID, u.Username, u.Password, u.Salt, u.Nickname, u.Phone, u.Role, u.CreatedAt) if errors.Is(err, sql.ErrNoRows) { return nil, nil // 查无此人不是系统错误 } return u, err关键习惯Scan的参数个数、顺序、类型必须和 SELECT 列一一对应。没有行时QueryRow返回sql.ErrNoRows要单独处理——登录「用户不存在」和「数据库挂了」是两种事。本项目约定查不到返回(nil, nil)把「无数据」和「出错」分开。3.2 Query 循环查多行rows, err : db.DB.Query(SELECT id,name,description,created_at FROM movie_groups ORDER BY id) if err ! nil { return nil, err } defer rows.Close() var list []models.MovieGroup for rows.Next() { var g models.MovieGroup if err : rows.Scan(g.ID, g.Name, g.Description, g.CreatedAt); err ! nil { return nil, err } list append(list, g) } return list, rows.Err()必须记住的三件事defer rows.Close()—— 否则连接可能泄漏。循环里每次Scan都要检查err。循环结束后检查rows.Err()—— 遍历过程中的错误会藏在这里。项目里ListGroups、ListMovies、ListOrdersByUser、ListSeats都是这个模式。scanMovies、scanOrders把重复 Scan 逻辑抽成函数避免 copy-paste。3.3 WHERE过滤条件SELECT ... FROM users WHERE username? SELECT ... FROM movies m WHERE m.status1 AND m.group_id? SELECT ... FROM schedules s WHERE s.start_time NOW()WHERE 就像筛子全表扫描太贵有索引的列主键、外键、username 唯一索引过滤更快。首页搜索dao.ListMovies会动态拼 WHEREif onlyOn { conds append(conds, m.status1) } if keyword ! { conds append(conds, (m.title LIKE ? OR m.director LIKE ? OR m.actors LIKE ?)) kw : % keyword % args append(args, kw, kw, kw) } if groupID 0 { conds append(conds, m.group_id?) args append(args, groupID) } where : if len(conds) 0 { where WHERE strings.Join(conds, AND ) } q : fmt.Sprintf(SELECT ... FROM movies m LEFT JOIN ... %s ORDER BY m.id DESC, where) rows, err : db.DB.Query(q, args...)安全边界重要拼接的是SQL 结构WHERE、AND这些固定片段。用户输入的关键词仍走?参数绑定。危险的是把用户字符串直接拼进 SQL... WHERE name name → SQL 注入。3.4 LIKE 与模糊搜索WHERE m.title LIKE %流浪%%表示任意长度任意字符。%流浪% 标题里任意位置包含「流浪」。注意前导%如%地球往往无法走普通 B 树索引数据量大时会慢。课程数据量小完全够用答辩时可说「生产环境可能用全文索引或 Elasticsearch」。3.5 JOIN多表一起查单表有时不够。电影列表要显示分组名订单列表要显示电影名、影厅名、开场时间——这些信息分散在多张表。SELECT m.id, m.title, IFNULL(g.name,), ... FROM movies m LEFT JOIN movie_groups g ON m.group_id g.idJOIN 类型含义本项目何时用INNER JOIN两边都有匹配才返回场次必须关联电影和影厅LEFT JOIN左表全保留右表无匹配则 NULL电影可以没有分组IFNULL(g.name,)分组为空时显示空字符串而不是 SQL NULL。订单查询是 JOIN 链的典型FROM orders o LEFT JOIN schedules s ON o.schedule_id s.id LEFT JOIN movies m ON s.movie_id m.id LEFT JOIN halls h ON s.hall_id h.id WHERE o.user_id? ORDER BY o.id DESC一次 SQL 取出展示所需字段避免 N1 查询查 100 个订单再循环查 100 次电影。拓展N1 问题若ListOrders只查orders表页面还要电影名有人会在循环里再GetMovie——订单多了就是「1 N 次查询」。JOIN 一次搞定是常见优化。3.6 ORDER BY / LIMITORDER BY m.rating_avg DESC, m.rating_count DESC LIMIT ?ORDER BY排序推荐列表「高分优先」。LIMIT只取前 N 条防止一次拉全库。RecommendMovies还用了子查询排除已评分电影WHERE m.status1 AND m.id NOT IN (SELECT movie_id FROM ratings WHERE user_id?)3.7 聚合AVG / COUNT / ROUND用户评分后电影表的均分和人数要更新UPDATE movies SET rating_avg(SELECT ROUND(AVG(score),1) FROM ratings WHERE movie_id?), rating_count(SELECT COUNT(*) FROM ratings WHERE movie_id?) WHERE id?子查询在 SET 里算聚合值再写回movies表——列表页直接读rating_avg不用每次 JOINratings现算。拓展MySQL 触发器也能在ratings变更时自动更新应用层更新更直观课程好讲、好调试。3.8 处理 NULLsql.NullXxx外键可空、支付时间可空时Scan 不能直接扫进int64或time.Timevar gid sql.NullInt64 var paidAt sql.NullTime row.Scan(..., gid, ..., paidAt) if gid.Valid { m.GroupID gid.Int64 } if paidAt.Valid { t : paidAt.Time o.PaidAt t }GetMovie、ListSeats、scanOrder里都有这套写法。4. UPDATE修改已有行4.1 语法UPDATE 表名 SET 列1?, 列2? WHERE 条件4.2 项目例子改电影信息db.DB.Exec( UPDATE movies SET title?,group_id?,director?,actors?,duration?,poster?,description?,status? WHERE id?, ...)锁座位下单UPDATE seats SET status?, lock_user_id?, lock_until?, order_id? WHERE id?支付成功UPDATE orders SET status?, paid_at? WHERE id? UPDATE seats SET status已售, lock_user_idNULL, lock_untilNULL WHERE order_id?取消订单 / 超时释放UPDATE orders SET status? WHERE id? UPDATE seats SET status0, lock_user_idNULL, lock_untilNULL, order_idNULL WHERE order_id?4.3 WHERE 绝不能忘没有 WHERE 的 UPDATE 会改整表——生产事故经典案例。写 UPDATE 时先问自己「这条 SQL 最多影响几行」下单锁座必须WHERE id?精确到单个座位。拓展有些团队要求 UPDATE 必须带主键或 LIMIT代码审查时强制检查。5. DELETE删除行DELETE FROM movie_groups WHERE id? DELETE FROM movies WHERE id? DELETE FROM schedules WHERE end_time ?DeleteExpiredSchedules清理过期场次若 schema 里座位对场次设了ON DELETE CASCADE删场次时关联座位自动删除。删分组时电影的group_id可能被外键设为SET NULL——电影还在只是不再属于该分组。软删除 vs 硬删除本项目场次用UPDATE status0取消软删过期场次用DELETE硬删。软删保留历史硬删节省空间。业务选型问题没有唯一正确答案。6. UPSERT有则更新、无则插入评分表(movie_id, user_id)有唯一约束同一用户对同一电影只能一条评分INSERT INTO ratings(movie_id,user_id,score) VALUES(?,?,?) ON DUPLICATE KEY UPDATE scoreVALUES(score)MySQL 专有语法。Go 里仍用tx.Exec和普通 INSERT 一样。7. 参数化查询与 SQL 注入7.1 正确做法db.DB.QueryRow(SELECT ... FROM users WHERE username?, username)驱动会把username当数据转义不会当 SQL 语法执行。7.2 错误示范q : SELECT * FROM users WHERE username username 若username是admin OR 11可能绕过校验。永远不要把用户输入用字符串拼接进 SQL。7.3 登录为什么不在 SQL 里比密码u, _ : dao.GetUserByUsername(name) if u nil || u.Password ! utils.HashPassword(pwd, u.Salt) { ... }原因密码存的是哈希盐不是明文SQL 里没法WHERE password用户输入。即使用明文也应该在应用层比较方便统一错误提示、防时序攻击等。业务逻辑放 GoSQL 只负责「按用户名取一行」。8. SELECT * 的取舍初学常写SELECT * FROM movies。本项目几乎总是显式列名SELECT m.id,m.title,m.group_id,IFNULL(g.name,),...优点表加列不会意外改变 Scan 顺序导致 bug。只取需要的列减少网络与内存。读代码的人一眼知道用了哪些字段。9. 和事务的关系预告单条 SQL 在 MySQL 里默认自动提交。多步必须原子时用事务——例如CreateOrderWithSeatsBEGIN → SELECT ... FOR UPDATE查座位并加行锁 → INSERT orders → UPDATE seats逐个锁定 COMMIT 或 ROLLBACK任一步失败整单回滚不会出现「订单建了但座位没锁」的半成品。详见第 16、17 篇。10. 和本项目代码的对应关系SQL 概念项目落点INSERTCreateUser、CreateMovie、CreateSchedule、CreateCommentSELECT 单行GetUserByUsername、GetMovie、GetSchedule、GetOrderSELECT 多行ListMovies、ListOrdersByUser、ListSeats、ListCommentsUPDATEUpdateMovie、PayOrder、锁座/释座DELETEDeleteMovie、DeleteGroup、DeleteExpiredSchedulesJOINListMovies、ListSchedules、订单/场次查询动态 WHEREListMovies、ListSchedules聚合子查询UpsertRating后更新movies.rating_avg参数?全部 dao 文件11. 常见误区「dao 就是 ORM」不对。dao 是项目里的数据访问层命名习惯我们用原生 SQL database/sql没有 ORM。「QueryRow 没结果就是 err ! nil 就报错」要区分sql.ErrNoRows正常业务用户不存在和真实错误连接断、语法错。「JOIN 越多越好」过多 JOIN 或大表 JOIN 可能慢必要时拆查询或加索引。本项目规模 JOIN 一次取展示字段是合理选择。「DELETE 和 UPDATE status0 一样」语义不同。取消场次用 UPDATE 保留记录清理过期数据用 DELETE 释放空间。「LastInsertId 可以忽略」注册、下单后常常要把新 id 写回对象或返回给前端忽略会导致后续逻辑缺 id。12. 拓展阅读12.1 索引与 WHEREWHERE username?若username有唯一索引查询是 O(log n) 级别。LIKE %关键词%往往全表扫描——数据量大要另想办法。12.2 预编译 PrepareInitSeatsForSchedule对同一 INSERT 执行「行×列」次Prepare 后重复 Exec 省解析成本。单次查询用Query/Exec即可不必凡事 Prepare。12.3 EXPLAINMySQL 的EXPLAIN SELECT ...可看是否走索引。答辩加分「若列表变慢我会 EXPLAIN 看是否全表扫描。」13. 小结可直接当口述稿SQL 四件套增删改查对应 INSERT、SELECT、UPDATE、DELETE。Go 里用 Exec 写、Query/QueryRow 读Scan 把列扫进 struct。多表展示用 JOIN搜索用 LIKE 参数绑定排序分页用 ORDER BY 和 LIMIT。用户输入必须用?占位禁止字符串拼接 SQL。多步要一致的操作放事务里——下单锁座就是典型。本项目的 SQL 都住在 dao 层Handler 只调函数不调裸 SQL。14. 思考题建议写进笔记或评论区为什么登录校验不在 SQL 里写WHERE password明文SELECT *有什么缺点本项目为什么常写列名订单列表不用 JOIN、改成先查 orders 再循环 GetSchedule/GetMovie行不行和 JOIN 方案比有何差异如何用 SQL 查出「某场次剩余空闲座位数」提示status0且schedule_id?ListComments用一次 SQL Go 内存组树和 SQL 递归 CTE 比各有什么优劣15. 下一篇预告《08 · database/sql 与连接池Ping、DSN、Open》会逐行拆db/db.gosql.Open不等于连上、Ping验证、连接池三个参数、以及为什么标准库开发仍要引入 MySQL 驱动