UPDATE SQL命令在GO语言中无法正常工作。

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

The UPDATE SQL command doesn't work with GO language

问题

在下面的示例中，UPDATE命令无法正常工作。为什么？

详见updateDate方法的细节。

  1. package main
  3. import (
  4. 	"database/sql"
  5. 	"fmt"
  6. 	"log"
  8. 	_ "github.com/go-sql-driver/mysql"
  9. )
  11. type ConnData struct {
  12. 	// para usar conexão via socket veja '/etc/mysql/my.cnf'
  13. 	// para usar conexão via tcp confira a porta com 'ps -ef | grep mysqld'
  14. 	dbuser, dbpasswd, dbname, tcp, socket string
  15. }
  17. var myConnData = ConnData{dbuser: "soma", dbpasswd: "secret1000",
  18. 	dbname: "test",
  19. 	tcp:    "tcp(127.0.0.1:3307)",
  20. 	socket: "unix(/tmp/mysql.sock)"}
  22. // CREATE TABLE userinfo
  23. // (
  24. //     uid serial NOT NULL,
  25. //     username character varying(20) NOT NULL,
  26. //     depto character varying(10)  NULL,
  27. //     Created date,
  28. //     CONSTRAINT userinfo_pkey PRIMARY KEY (uid)
  29. // )
  30. func main() {
  31. 	_ = ConnectToMySQLAndRunCmd(myConnData).(string)
  32. }
  33. func ConnectToMySQLAndRunCmd(connData ConnData) interface{} {
  34. 	myConnStr := connData.dbuser + ":" + connData.dbpasswd +
  35. 		"@" + connData.tcp + "/" + connData.dbname
  36. 	log.Printf("Connecting to MySQL Server using %s\n", myConnStr)
  37. 	db, err := sql.Open("mysql", myConnStr)
  39. 	defer db.Close()
  40. 	checkErr(err)
  41. 	// Open doesn't open a connection. Validate DSN data:
  42. 	err = db.Ping()
  43. 	checkErr(err)
  44. 	// Limpando a tabela
  45. 	_, err = db.Exec("DELETE FROM userinfo")
  46. 	checkErr(err)
  47. 	// Consultando
  48. 	selectAll(db)
  49. 	// Inserindo
  50. 	stmt, err := db.Prepare("INSERT INTO userinfo (username, depto, created) VALUES(?,?,?)")
  51. 	checkErr(err)
  52. 	res, err := stmt.Exec("Pedro", "TI", "2015-09-01")
  53. 	checkErr(err)
  54. 	lastId, err := res.LastInsertId()
  55. 	checkErr(err)
  56. 	rowCnt, err := res.RowsAffected()
  57. 	checkErr(err)
  58. 	log.Printf("ID = %d, affected = %d\n", lastId, rowCnt)
  59. 	// Verificando
  60. 	selectAll(db)
  61. 	// Atualizando
  62. 	updateDate(db, lastId)
  63. 	// Verificando Novamente
  64. 	selectAll(db)
  65. 	return "OK"
  66. }
  68. func selectAll(mydb *sql.DB) {
  69. 	// Consultando todos
  70. 	var (
  71. 		id    int
  72. 		name  string
  73. 		depto string
  74. 		date  string
  75. 	)
  76. 	rows, err := mydb.Query("select uid, username, depto, created from userinfo")
  77. 	checkErr(err)
  78. 	defer rows.Close()
  79. 	for rows.Next() {
  80. 		err := rows.Scan(&id, &name, &depto, &date)
  81. 		checkErr(err)
  82. 		log.Println(id, name, depto, date)
  83. 	}
  84. 	err = rows.Err()
  85. 	checkErr(err)
  86. }
  88. func updateDate(mydb *sql.DB, lastId int64) {
  89. 	// Atualizando a data
  90. 	tx, err := mydb.Begin()
  91. 	checkErr(err)
  92. 	//...
  93. 	cmd := "UPDATE userinfo SET created = ? WHERE uid = ?"
  94. 	updateDate, err := tx.Prepare(cmd)
  95. 	checkErr(err)
  96. 	//...
  97. 	log.Println("•••• Updating record", lastId)
  98. 	res, err := updateDate.Exec(lastId, "2015-09-02")
  99. 	checkErr(err)
  100. 	// log.Println("•••• >>> ", res)
  101. 	updateDate.Close()
  102. 	err = tx.Commit()
  103. 	checkErr(err)
  104. 	affect, err := res.RowsAffected()
  105. 	checkErr(err)
  107. 	fmt.Println("Records affected", affect)
  108. }
  110. func checkErr(err error) {
  111. 	if err != nil {
  112. 		log.Fatal(err)
  113. 		panic(err)
  114. 	}
  115. }

出了什么问题？

当我运行主程序时，下面是结果：

  1. $ ./main
  2. 2015/09/05 14:04:34 Connecting to MySQL Server using soma:soma@tcp(127.0.0.1:3307)/test
  3. 2015/09/05 14:04:34 ID = 3, affected = 1
  4. 2015/09/05 14:04:34 3 Pedro TI 2015-09-01
  5. 2015/09/05 14:04:34 •••• Updating record 3
  6. Records affected 0
  7. 2015/09/05 14:04:34 3 Pedro TI 2015-09-01

我使用go build -a -installsuffix cgo -o main .来构建主程序。

英文:

In this example below the UPDATE command doesn't work. Why ?

See the updateDate method for details.

  1. package main
  2. import (
  3. "database/sql"
  4. "fmt"
  5. "log"
  6. _ "github.com/go-sql-driver/mysql"
  7. )
  8. type ConnData struct {
  9. // para usar conexão via socket veja '/etc/mysql/my.cnf'
  10. // para usar conexão via tcp confira a porta com 'ps -ef | grep mysqld'
  11. dbuser, dbpasswd, dbname, tcp, socket string
  12. }
  13. var myConnData = ConnData{dbuser: "soma", dbpasswd: "secret1000",
  14. dbname: "test",
  15. tcp:    "tcp(127.0.0.1:3307)",
  16. socket: "unix(/tmp/mysql.sock)"}
  17. // CREATE TABLE userinfo
  18. // (
  19. //     uid serial NOT NULL,
  20. //     username character varying(20) NOT NULL,
  21. //     depto character varying(10)  NULL,
  22. //     Created date,
  23. //     CONSTRAINT userinfo_pkey PRIMARY KEY (uid)
  24. // )
  25. func main() {
  26. _ = ConnectToMySQLAndRunCmd(myConnData).(string)
  27. }
  28. func ConnectToMySQLAndRunCmd(connData ConnData) interface{} {
  29. myConnStr := connData.dbuser + ":" + connData.dbpasswd +
  30. "@" + connData.tcp + "/" + connData.dbname
  31. log.Printf("Connecting to MySQL Server using %s\n", myConnStr)
  32. db, err := sql.Open("mysql", myConnStr)
  33. // myConnStr := connData.dbuser + ":" + connData.dbpasswd +
  34. // 	"@" + connData.tcp + "/" + connData.dbname
  35. defer db.Close()
  36. checkErr(err)
  37. // Open doesn't open a connection. Validate DSN data:
  38. err = db.Ping()
  39. checkErr(err)
  40. // Limpando a tabela
  41. _, err = db.Exec("DELETE FROM userinfo")
  42. checkErr(err)
  43. // Consultando
  44. selectAll(db)
  45. // Inserindo
  46. stmt, err := db.Prepare("INSERT INTO userinfo (username, depto, created) VALUES(?,?,?)")
  47. // db.Prepare("INSERT INTO users(name) VALUES(?)")
  48. checkErr(err)
  49. res, err := stmt.Exec("Pedro", "TI", "2015-09-01")
  50. checkErr(err)
  51. lastId, err := res.LastInsertId()
  52. checkErr(err)
  53. rowCnt, err := res.RowsAffected()
  54. checkErr(err)
  55. log.Printf("ID = %d, affected = %d\n", lastId, rowCnt)
  56. // Verificando
  57. selectAll(db)
  58. // Atualizando
  59. updateDate(db, lastId)
  60. // Verificando Novamente
  61. selectAll(db)
  62. return "OK"
  63. }
  64. func selectAll(mydb *sql.DB) {
  65. // Consultando todos
  66. var (
  67. id    int
  68. name  string
  69. depto string
  70. date  string
  71. )
  72. rows, err := mydb.Query("select uid, username, depto, created from userinfo")
  73. checkErr(err)
  74. defer rows.Close()
  75. for rows.Next() {
  76. err := rows.Scan(&id, &name, &depto, &date)
  77. checkErr(err)
  78. log.Println(id, name, depto, date)
  79. }
  80. err = rows.Err()
  81. checkErr(err)
  82. }
  83. func updateDate(mydb *sql.DB, lastId int64) {
  84. // Atualizando a data
  85. tx, err := mydb.Begin()
  86. checkErr(err)
  87. //...
  88. cmd := "UPDATE userinfo SET created = ? WHERE uid = ?"
  89. updateDate, err := tx.Prepare(cmd)
  90. checkErr(err)
  91. //...
  92. log.Println("•••• Updating record", lastId)
  93. res, err := updateDate.Exec(lastId, "2015-09-02")
  94. checkErr(err)
  95. // log.Println("•••• >>> ", res)
  96. updateDate.Close()
  97. err = tx.Commit()
  98. checkErr(err)
  99. affect, err := res.RowsAffected()
  100. checkErr(err)
  101. fmt.Println("Records affected", affect)
  102. }
  103. func checkErr(err error) {
  104. if err != nil {
  105. log.Fatal(err)
  106. panic(err)
  107. }
  108. }

What is going wrong ?

See below the result when I run the main programa.

  1. $ ./main
  2. 2015/09/05 14:04:34 Connecting to MySQL Server using soma:soma@tcp(127.0.0.1:3307)/test
  3. 2015/09/05 14:04:34 ID = 3, affected = 1
  4. 2015/09/05 14:04:34 3 Pedro TI 2015-09-01
  5. 2015/09/05 14:04:34 •••• Updating record 3
  6. Records affected 0
  7. 2015/09/05 14:04:34 3 Pedro TI 2015-09-01

I'm using go build -a -installsuffix cgo -o main . to build the main program

答案1

得分: 7

看起来你颠倒了lastId和"2015-09-02"的顺序：

  1. cmd := "UPDATE userinfo SET created = ? WHERE uid = ?"
  2. res, err := updateDate.Exec("2015-09-02", lastId)
英文:

It seems you inverted the order of lastId and "2015-09-02":

  1. cmd := "UPDATE userinfo SET created = ? WHERE uid = ?"
  2. res, err := updateDate.Exec(lastId, "2015-09-02")

huangapple
  • 本文由 发表于 2015年9月6日 01:24:45
  • 转载请务必保留本文链接：https://go.coder-hub.com/32415803.html
  • go
  • mysql
匿名

发表评论

匿名网友

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

确定