Golang：Mysql准备好的插入语句无法将行添加到数据库表中。

huangapple
go评论116阅读模式
英文:

Golang: Mysql Prepare Insert statements do not add rows into db table

问题

所以我正在尝试使用一个MySQL驱动程序将数据插入数据库。

具体来说，我正在使用这个驱动程序：

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

这是我的代码

  1. func main() {
  3.     db, err := sql.Open("mysql", "psanker:123@/education_data")
  4.     err = db.Ping()
  5.     
  6.     if err != nil {
  7.         fmt.Println("Failed to prepare connection to database")
  8.         log.Fatal("Error:", err.Error())
  9.     }
  11.     defer db.Close()
  12.         
  13.     content, err := ioutil.ReadFile("activities.csv")
  15.     lines := strings.Split(string(content), "\r")
  17.     //only work so long as I have one district
  18.     rows, err := db.Query("SELECT id FROM districts")
  19.     var districtId int
  20.     defer rows.Close()
  22.     for rows.Next() {
  23.         err := rows.Scan(&districtId)
  24.         check(err)
  25.     }
  28.     for i, line := range lines {
  29.         if i > 1 {
  30.             splitStr := strings.Split(line, ",")
  31.             var activityCode string
  33.             if strings.Contains(splitStr[0], "-") {
  34.                 //this is an activity
  35.                 activityCode = splitStr[0]
  36.                 stmt1, _ := db.Prepare("INSERT INTO activities(code) VALUES(?)")
  37.                 stmt2, _ := db.Prepare("INSERT INTO activities(name) VALUES(?)")
  38.                 res, _ := stmt1.Exec(splitStr[0])
  39.                 stmt2.Exec(splitStr[1])
  40.             } else {
  41.                 //this is a sub activity
  42.                 stmt1, _ := db.Prepare("INSERT INTO sub_activities(code) VALUES(?)")
  43.                 stmt2, _ := db.Prepare("INSERT INTO sub_activities(name) VALUES(?)")
  44.                 stmt1.Exec(splitStr[0])
  45.                 stmt2.Exec(splitStr[1])
  46.                 if activityCode != "" {
  47.                     rows, _ := db.Query("SELECT id from activities where code = ?", activityCode)
  48.                     var activityId int
  49.                     for rows.Next() {
  50.                         err := rows.Scan(&activityId)
  51.                         check(err)
  52.                         stmt3, err := db.Prepare("INSERT INTO sub_activities(activity_id) VALUES(?)")
  53.                         stmt3.Exec(activityId)
  54.                     }
  55.                 }
  56.                 rows, _ := db.Query("SELECT id from sub_activities where code= ?", splitStr[0])
  57.                 var sub_activityId int
  58.                 for rows.Next() {
  59.                     err := rows.Scan(&sub_activityId)
  60.                     check(err)
  61.                     stmt5, _ := db.Prepare("INSERT INTO sub_activity_expenditure(district_id) VALUES(?)")
  62.                     stmt6, _ := db.Prepare("INSERT INTO sub_activity_expenditure(sub_activity_id) VALUES(?)")
  63.                     stmt7, _ := db.Prepare("INSERT INTO sub_activity_expenditure(expenditure) VALUES(?)")
  64.                     stmt5.Exec(districtId)
  65.                     stmt6.Exec(sub_activityId)
  66.                     stmt7.Exec(splitStr[2])
  67.                 }
  68.             }
  69.         }
  70.     }
  71. }

当我检查MySQL数据库时，表中没有插入任何行。我认为我正在访问正确的数据库，因为从districts获取id的初始查询返回1，这是正确的。

发生了什么？

编辑

我通过执行以下操作确认我在正确的数据库中

  1. rows, err = db.Query("SELECT DATABASE();")
  2. var test string
  4. for rows.Next() {
  5.     rows.Scan(&test)
  6.     fmt.Println(test)
  7. }

它打印出 education_data

英文:

So I'm trying to use a mysql driver to insert data into a database.

Specifically, I'm using this one:

  1. "github.com/go-sql-driver/mysql"

This is my code

  1. func main() {
  2. db, err := sql.Open("mysql", "psanker:123@/education_data")
  3. err = db.Ping()
  4. if err != nil {
  5. fmt.Println("Failed to prepare connection to database")
  6. log.Fatal("Error:", err.Error())
  7. }
  8. defer db.Close()
  9. content, err := ioutil.ReadFile("activities.csv")
  10. lines := strings.Split(string(content), "\r")
  11. //only work so long as I have one district
  12. rows, err := db.Query("SELECT id FROM districts")
  13. var districtId int
  14. defer rows.Close()
  15. for rows.Next() {
  16. err := rows.Scan(&districtId)
  17. check(err)
  18. }
  19. for i, line := range lines {
  20. if i > 1 {
  21. splitStr := strings.Split(line, ",")
  22. var activityCode string
  23. if strings.Contains(splitStr[0], "-") {
  24. //this is an activity
  25. activityCode = splitStr[0]
  26. stmt1, _ := db.Prepare("INSERT INTO activities(code) VALUES(?)")
  27. stmt2, _ := db.Prepare("INSERT INTO activities(name) VALUES(?)")
  28. res, _ := stmt1.Exec(splitStr[0])
  29. stmt2.Exec(splitStr[1])
  30. } else {
  31. //this is a sub activity
  32. stmt1, _ := db.Prepare("INSERT INTO sub_activities(code) VALUES(?)")
  33. stmt2, _ := db.Prepare("INSERT INTO sub_activities(name) VALUES(?)")
  34. stmt1.Exec(splitStr[0])
  35. stmt2.Exec(splitStr[1])
  36. if activityCode != "" {
  37. rows, _ := db.Query("SELECT id from activities where code = ?", activityCode)
  38. var activityId int
  39. for rows.Next() {
  40. err := rows.Scan(&activityId)
  41. check(err)
  42. stmt3, err := db.Prepare("INSERT INTO sub_activities(activity_id) VALUES(?)")
  43. stmt3.Exec(activityId)
  44. }
  45. }
  46. rows, _ := db.Query("SELECT id from sub_activities where code= ?", splitStr[0])
  47. var sub_activityId int
  48. for rows.Next() {
  49. err := rows.Scan(&sub_activityId)
  50. check(err)
  51. stmt5, _ := db.Prepare("INSERT INTO sub_activity_expenditure(district_id) VALUES(?)")
  52. stmt6, _ := db.Prepare("INSERT INTO sub_activity_expenditure(sub_activity_id) VALUES(?)")
  53. stmt7, _ := db.Prepare("INSERT INTO sub_activity_expenditure(expenditure) VALUES(?)")
  54. stmt5.Exec(districtId)
  55. stmt6.Exec(sub_activityId)
  56. stmt7.Exec(splitStr[2])
  57. }
  58. }
  59. }
  60. }
  61. }

When I check Mysql database, there are no rows inserted into the tables. I think I'm accessing the right database because the initial query that gets id from districts is returning 1, which is correct.

What's going on?

EDIT

I have confirmed I'm in the right db by doing this

  1. rows, err = db.Query("SELECT DATABASE();")
  2. var test string
  3. for rows.Next() {
  4. rows.Scan(&test)
  5. fmt.Println(test)
  6. }

Which prints out education_data

答案1

得分: 1

由于您尝试插入表格时，我在聊天中提醒您，您的列是非空的，而您实际上是要创建一行，但却创建了两行。stmt1和stmt2失败是因为没有为非空列提供参数，以便插入成功所需的所有数据。

请专注于为stmt1和stmt2创建一行（将它们合并为一个带有2个参数的单个stmt1来执行）。

确保字符串用单引号括起来。

英文:

As you are trying to insert into a table, I was reminded (in chat at least) that your columns are non-null and you really meant to create one row when you were in fact creating two. It failed on stmt1 and stmt2 because non-nullable columns were not given parameters to replace for all necessary data for insertion to succeed.

Focus on creating one row for stmt1 and stmt2 (combine them into a single stmt1 to execute with 2 parameters).

Make sure strings are wrapped with a single quote.

huangapple
  • 本文由 发表于 2015年6月5日 04:25:51
  • 转载请务必保留本文链接：https://go.coder-hub.com/30653478.html
  • go
  • mysql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定