Question 191181 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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?

go 1.16

import (
    "database/sql"
    "errors"
    "fmt"
    "io"
    "log"
    "net/http"
    "strconv"
    "strings"
    "time"

    "github.com/aws/aws-lambda-go/events"
    "github.com/aws/aws-lambda-go/lambda"

    // "github.com/leekchan/timeutil"
    _ "github.com/go-sql-driver/mysql" //v1.7.1
)

func SelectQuery(action string, schema string) string {
    if action == "del" {
        switch schema {
        case "schema1":
            return "DELETE FROM `schema1` WHERE `id` IN (?)"
        default:
            panic("bad request")
        }
    } 
}

func DelAction(db *sql.DB, schema string, list []int) int64 {
    strList := []string{} 
    for _, v := range list { // this is id list ex.[1, 2, 3]
        strList = append(strList, strconv.Itoa(v))
    }
    delQeury := SelectQuery("del", schema) // "DELETE FROM `schema1` WHERE `id` IN (?)"
    param := strings.Join(strList, ",") // param: 1,2,3
    delRes, err := db.Exec(delQeury, param)
    delCnt, err := delRes.RowsAffected()
    return delCnt // delete only 1 records. even if param have 3 values
}

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] 

res, err := db.Query(query) // select query

    list := []int{}
    for res.Next() {
        u := &Logs{} // type logs is id int 
        if err := res.Scan(&u.id); err != nil {
            fmt.Println("scan error")
            panic(err.Error())
        } else {
            list = append(list, u.id)
        }
    }
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

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

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

  2. 0

    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:

    CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (1),(2),(3);
DELETE FROM t1 WHERE a in ("1,2,3");
Query OK, 1 row affected, 1 warning (0,009 sec)
SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: '1,2,3' |
+---------+------+--------------------------------------------+
1 row in set (0,001 sec)
SELECT a FROM t1;
+------+
| a    |
+------+
|    2 |
|    3 |
+------+
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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search