为什么使用MySQL的IN条件时，这个删除操作不能正常工作？

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

golang mysql why this delete not working properly with IN condition of mysql

问题

我使用golang mysql执行删除语句，删除的目标是使用IN条件，但只删除了一条记录。这是什么原因？

  1. go 1.16
  1. import (
  2. 	"database/sql"
  3. 	"errors"
  4. 	"fmt"
  5. 	"io"
  6. 	"log"
  7. 	"net/http"
  8. 	"strconv"
  9. 	"strings"
  10. 	"time"
  12. 	"github.com/aws/aws-lambda-go/events"
  13. 	"github.com/aws/aws-lambda-go/lambda"
  15. 	// "github.com/leekchan/timeutil"
  16. 	_ "github.com/go-sql-driver/mysql" //v1.7.1
  17. )
  1. func SelectQuery(action string, schema string) string {
  2. 	if action == "del" {
  3. 		switch schema {
  4. 		case "schema1":
  5. 			return "DELETE FROM `schema1` WHERE `id` IN (?)"
  6. 		default:
  7. 			panic("bad request")
  8. 		}
  9. 	} 
  10. }
  1. func DelAction(db *sql.DB, schema string, list []int) int64 {
  2. 	strList := []string{} 
  3. 	for _, v := range list { // this is id list ex.[1, 2, 3]
  4. 		strList = append(strList, strconv.Itoa(v))
  5. 	}
  6. 	delQeury := SelectQuery("del", schema) // "DELETE FROM `schema1` WHERE `id` IN (?)"
  7. 	param := strings.Join(strList, ",") // param: 1,2,3
  8. 	delRes, err := db.Exec(delQeury, param)
  9. 	delCnt, err := delRes.RowsAffected()
  10. 	return delCnt // delete only 1 records. even if param have 3 values
  11. }

例如，我想删除所有3条记录，但只删除了1条记录。

添加信息：
创建一个要删除的ID int切片数组。
例如：[1, 2, 3]

  1. res, err := db.Query(query) // select query
  3. list := []int{}
  4. for res.Next() {
  5. 	u := &Logs{} // 类型logs是id int
  6. 	if err := res.Scan(&u.id); err != nil {
  7. 		fmt.Println("scan error")
  8. 		panic(err.Error())
  9. 	} else {
  10. 		list = append(list, u.id)
  11. 	}
  12. }
英文:

I use golang mysql to execute the delete statement,

the target of the delete is using IN condition, but only one record is deleted. What is causing this?

  1. go 1.16
  1. import (
  2. 	"database/sql"
  3. 	"errors"
  4. 	"fmt"
  5. 	"io"
  6. 	"log"
  7. 	"net/http"
  8. 	"strconv"
  9. 	"strings"
  10. 	"time"
  12. 	"github.com/aws/aws-lambda-go/events"
  13. 	"github.com/aws/aws-lambda-go/lambda"
  15. 	// "github.com/leekchan/timeutil"
  16. 	_ "github.com/go-sql-driver/mysql" //v1.7.1
  17. )
  1. func SelectQuery(action string, schema string) string {
  2. 	if action == "del" {
  3. 		switch schema {
  4. 		case "schema1":
  5. 			return "DELETE FROM `schema1` WHERE `id` IN (?)"
  6. 		default:
  7. 			panic("bad request")
  8. 		}
  9. 	} 
  10. }
  1. func DelAction(db *sql.DB, schema string, list []int) int64 {
  2. 	strList := []string{} 
  3. 	for _, v := range list { // this is id list ex.[1, 2, 3]
  4. 		strList = append(strList, strconv.Itoa(v))
  5. 	}
  6. 	delQeury := SelectQuery("del", schema) // "DELETE FROM `schema1` WHERE `id` IN (?)"
  7. 	param := strings.Join(strList, ",") // param: 1,2,3
  8. 	delRes, err := db.Exec(delQeury, param)
  9. 	delCnt, err := delRes.RowsAffected()
  10. 	return delCnt // delete only 1 records. even if param have 3 values
  11. }

example. I want to delete all 3 records but only 1 record is deleted

add info
Create an array of ID int slices to be deleted.
ex. [1, 2, 3]

  1. res, err := db.Query(query) // select query
  3. 	list := []int{}
  4. 	for res.Next() {
  5. 		u := &Logs{} // type logs is id int 
  6. 		if err := res.Scan(&u.id); err != nil {
  7. 			fmt.Println("scan error")
  8. 			panic(err.Error())
  9. 		} else {
  10. 			list = append(list, u.id)
  11. 		}
  12. 	}

答案1

得分: 1

这解决了问题。
我不知道这是否是最好的方法，但它可以这样工作。

  1. args := []interface{}{}
  2. for _, x := range list {
  3.     args = append(args, x)
  4. }
  5. stmt := `DELETE FROM schema WHERE id IN (?` + strings.Repeat(", ?", len(list)-1) + `)`
  6. db.Exec(stmt, args...)
英文:

This solved it.
I don't know if this is the best way, but it works this way.

  1. args := []interface{}{}
  2. 	for _, x := range list {
  3. 		args = append(args, x)
  4. 	}
  5. 	stmt := `DELETE FROM schema WHERE id IN (?` + strings.Repeat(", ?", len(list)-1) + `)`
  6. db.Exec(stmt, args...)

答案2

得分: 0

一个问号表示一个参数，而不是参数列表。传递的参数列表将被转换为其字符串表示形式，然后被截断。

以下是纯SQL的等效写法：

  1. CREATE TABLE t1 (a int);
  2. INSERT INTO t1 VALUES (1),(2),(3);
  3. DELETE FROM t1 WHERE a in ("1,2,3");
  4. Query OK, 1 row affected, 1 warning (0.009 sec)
  5. SHOW WARNINGS;
  6. +---------+------+--------------------------------------------+
  7. | Level   | Code | Message                                    |
  8. +---------+------+--------------------------------------------+
  9. | Warning | 1292 | Truncated incorrect DECIMAL value: '1,2,3' |
  10. +---------+------+--------------------------------------------+
  11. 1 row in set (0.001 sec)
  12. SELECT a FROM t1;
  13. +------+
  14. | a    |
  15. +------+
  16. |    2 |
  17. |    3 |
  18. +------+
  19. 2 rows in set (0.001 sec)

我建议在Go中构建整个SQL语句而不使用参数。在Python中，你可以使用execute_many方法，我不知道Go是否提供类似的方法。

英文:

A question mark represents exact one parameter not a list of parameters. The passed parameter list will be converted to it's string representation and then truncated.

Here is the equivalent in pure SQL:

  1. CREATE TABLE t1 (a int);
  2. INSERT INTO t1 VALUES (1),(2),(3);
  3. DELETE FROM t1 WHERE a in ("1,2,3");
  4. Query OK, 1 row affected, 1 warning (0,009 sec)
  5. SHOW WARNINGS;
  6. +---------+------+--------------------------------------------+
  7. | Level   | Code | Message                                    |
  8. +---------+------+--------------------------------------------+
  9. | Warning | 1292 | Truncated incorrect DECIMAL value: '1,2,3' |
  10. +---------+------+--------------------------------------------+
  11. 1 row in set (0,001 sec)
  12. SELECT a FROM t1;
  13. +------+
  14. | a    |
  15. +------+
  16. |    2 |
  17. |    3 |
  18. +------+
  19. 2 rows in set (0,001 sec)

I would suggest to build the entire SQL statement in go without parameters. In Python you would use execute_many method(), I don't know if go provides something similiar.

huangapple
  • 本文由 发表于 2023年6月22日 00:16:05
  • 转载请务必保留本文链接：https://go.coder-hub.com/76525287.html
  • go
  • mysql
匿名

发表评论

匿名网友

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

确定