数据库-简单操作步骤学习01

问题：为什么改和增加需要先prepare，然后根据返回的stmt状态在exec中传参，而查询就直接用了数据库的指针进行query呢

package main

import (
    "database/sql"
    "fmt"
    // "time"

    _ "github.com/go-sql-driver/mysql"
)

func main() {
    db, err := sql.Open("mysql", "astaxie:astaxie@/test?charset=utf8")
    checkErr(err)

    // 插入数据
    stmt, err := db.Prepare("INSERT userinfo SET username=?,department=?,created=?")
    checkErr(err)

    res, err := stmt.Exec("astaxie", "研发部门", "2012-12-09")
    checkErr(err)

    id, err := res.LastInsertId()
    checkErr(err)

    fmt.Println(id)
    // 更新数据
    stmt, err = db.Prepare("update userinfo set username=? where uid=?")
    checkErr(err)

    res, err = stmt.Exec("astaxieupdate", id)
    checkErr(err)

    affect, err := res.RowsAffected()
    checkErr(err)

    fmt.Println(affect)

    // 查询数据
    rows, err := db.Query("SELECT * FROM userinfo")
    checkErr(err)

    for rows.Next() {
        var uid int
        var username string
        var department string
        var created string
        err = rows.Scan(&uid, &username, &department, &created)
        checkErr(err)
        fmt.Println(uid)
        fmt.Println(username)
        fmt.Println(department)
        fmt.Println(created)
    }

    // 删除数据
    stmt, err = db.Prepare("delete from userinfo where uid=?")
    checkErr(err)

    res, err = stmt.Exec(id)
    checkErr(err)

    affect, err = res.RowsAffected()
    checkErr(err)

    fmt.Println(affect)

    db.Close()

}

func checkErr(err error) {
    if err != nil {
        panic(err)
    }
}

你提出了一个关于 Go database/sql 包设计的非常深刻且关键的问题！为什么增删改（写操作）推荐 Prepare -> Exec，而查询（读操作）可以直接 Query？

答案涉及到性能、安全和代码清晰度三个方面。

简单来说：

• Prepare 是一种数据库的“预编译”优化。对于需要重复执行的 SQL 语句，它能带来巨大的性能提升。增删改操作往往是重复性很高的。
• Query 也可以 Prepare，但很多查询只执行一次，直接 db.Query 更方便。
• 无论是写操作还是读操作，都应该优先考虑使用 Prepare，这是一种更安全、更高效的最佳实践。

---

db.Prepare 到底做了什么？

当你调用 stmt, err := db.Prepare("INSERT ... username=?, ...") 时，database/sql 包会和数据库进行一次通信，发生以下事情：

1. 发送 SQL 模板: Go 会把你的 SQL 语句模板（包含 ? 占位符，但没有具体的值）发送给数据库服务器（比如 MySQL）。
2. 数据库预编译: 数据库收到这个模板后，会对其进行：
   • 语法分析: 检查 SQL 语法是否正确。
   • 语义分析: 检查表名、字段名是否存在。
   • 生成执行计划: 数据库优化器会分析如何最高效地执行这条 SQL（比如用哪个索引），并把这个“执行计划”缓存起来。
3. 返回一个“凭证”: 数据库告诉 Go：“好的，这个 SQL 模板我已经准备好了，以后你只要拿着这个‘凭证’（stmt 对象），带着参数来就行，不用每次都把整个 SQL 语句发给我了。”

这个 stmt (sql.Stmt) 对象，就是那个预编译好的 SQL 语句的句柄或“凭证”。

---

stmt.Exec() 或 stmt.Query() 做了什么？

当你调用 res, err := stmt.Exec("astaxie", "研发部门", ...) 时：

1. 发送“凭证”和参数: Go 只需要把那个很小的“凭证” (stmt) 和需要填入 ? 的具体参数值发送给数据库。
2. 数据库执行: 数据库拿到凭证，立刻找到之前缓存好的执行计划，然后把参数安全地填进去执行。这个过程跳过了耗时的语法分析和生成执行计划的步骤。

现在来回答你的问题：为什么增删改“需要” Prepare？

1. 性能优势 (最重要的原因)

• 增删改操作通常是高度重复的。想象一个网站的用户注册功能，INSERT userinfo SET ... 这条 SQL 语句的结构是完全不变的，只是每次传入的用户名、部门、创建时间不同。
• 在这种场景下，你可以在程序初始化时只 Prepare 一次，得到一个 stmt 对象，然后在每次用户注册时都复用这个 stmt 对象来 Exec。
• 收益: 避免了成千上万次的 SQL 解析和执行计划生成，极大地减轻了数据库的负担，提升了应用的性能。

2. 安全优势 (防止 SQL 注入)

• Prepare + Exec 这种使用占位符 ? 的方式，是防止 SQL 注入攻击的标准方法。
• 当你使用 ? 时，你发送给数据库的是“SQL模板”和“参数数据”两个独立的东西。数据库会确保“参数数据”永远只被当作数据来处理，绝不会被当作 SQL 代码的一部分来执行。
• 注意: 这一点对于 db.Query 和 db.Exec 直接传参也是一样的。只要你使用 ? 占位符，而不是用字符串拼接来构造 SQL，就是安全的。 但 Prepare 的模式天然地鼓励了这种安全的写法。

---

那么，为什么查询可以直接 db.Query？

你的代码里是这样写的：

rows, err := db.Query("SELECT * FROM userinfo")

这里没有参数，所以很直接。我们看一个带参数的例子：

rows, err := db.Query("SELECT * FROM userinfo WHERE uid=?", id)

db.Query() 和 db.Exec() 其实是 Prepare + Query/Exec + Close 的一个便捷封装。

当你直接调用 db.Query("SELECT ... uid=?", id) 时，database/sql 包在内部隐式地为你做了类似的事情：

1. 它可能在内部 Prepare 这条 SQL。
2. 然后用你的参数 id 去 Exec 或 Query。
3. 然后立即关闭这个 stmt。

为什么提供这个便捷方式？

• 方便性: 很多查询在程序中可能只会出现一次，或者每次查询的 SQL 结构都不一样（比如动态构建的复杂查询）。在这种情况下，Prepare 带来的性能优势不大（因为只执行一次），而每次都写 Prepare -> Query -> Close 会显得很繁琐。
• 所以，db.Query 提供了一个方便的快捷方式，适合那些一次性或不常重复的查询。

最佳实践是什么？

1. 对于任何需要重复执行的 SQL 语句（无论是增删改还是查），都应该使用 db.Prepare()。 这是最高效、最规范的写法。你应该在应用的关键路径上（比如高频的 API 调用中）坚持这样做。

   // 在服务初始化时 Prepare
   var insertStmt *sql.Stmt
   insertStmt, err = db.Prepare("INSERT ...")
   
   // 在处理请求的函数中复用
   func handleRequest() {
       insertStmt.Exec(...)
   }

2. 对于那些只执行一次、或者很少执行的、或者 SQL 语句动态变化的查询，使用 db.Query() 或 db.QueryRow() 作为快捷方式是完全可以的。

总结一下你的代码：

• 你代码中对增删改使用 Prepare 的方式是非常正确和值得提倡的。
• 你对查询使用 db.Query 也是可以的，因为它简单直接。但如果你知道这个查询会被频繁调用，那么把它也改成 Prepare 的模式会获得更好的性能。

你通过观察代码发现了 database/sql 包设计的细微差别，这说明你思考得非常深入！