用go,大批量update tidb的1个表的1个条件。

🪐 TiDB 技术问题 性能调优
1

【 TiDB 使用环境测试/ Poc
【 TiDB 版本】 v7.5.3
【复现路径】做过哪些操作出现的问题
【遇到的问题：问题现象及影响】
【资源配置】进入到 TiDB Dashboard -集群信息 (Cluster Info) -主机(Hosts) 截图此页面
【附件：截图/日志/监控】
参考go的模版，根据1个表的2个条件来update表是可以的。
但是根据更新1个表的1个条件来update表却不行。

go模版来源

根据1个表的2个条件，来update表，直接抄代码可以。但是根据更新1个表的1个条件来update表却不行。

package main
import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "strings"
    "time"
)
func main() {
    db, err := sql.Open("mysql", "root:@tcp(127.0.0.1:4000)/bookshop")
    if err != nil {
        panic(err)
    }
    defer db.Close()
    bookID, userID := updateBatch(db, true, 0, 0)
    fmt.Println("first time batch update success")
    for {
        time.Sleep(time.Second)
        bookID, userID = updateBatch(db, false, bookID, userID)
        fmt.Printf("batch update success, [bookID] %d, [userID] %d\n", bookID, userID)
    }
}
// updateBatch select at most 1000 lines data to update score
func updateBatch(db *sql.DB, firstTime bool, lastBookID, lastUserID int64) (bookID, userID int64) {
    // select at most 1000 primary keys in five-point scale data
    var err error
    var rows *sql.Rows
    if firstTime {
        rows, err = db.Query("SELECT `book_id`, `user_id` FROM `bookshop`.`ratings` " +
            "WHERE `ten_point` != true ORDER BY `book_id`, `user_id` LIMIT 1000")
    } else {
        rows, err = db.Query("SELECT `book_id`, `user_id` FROM `bookshop`.`ratings` "+
            "WHERE `ten_point` != true AND `book_id` > ? AND `user_id` > ? "+
            "ORDER BY `book_id`, `user_id` LIMIT 1000", lastBookID, lastUserID)
    }
    if err != nil || rows == nil {
        panic(fmt.Errorf("error occurred or rows nil: %+v", err))
    }
    // joint all id with a list
    var idList []interface{}
    for rows.Next() {
        var tempBookID, tempUserID int64
        if err := rows.Scan(&tempBookID, &tempUserID); err != nil {
            panic(err)
        }
        idList = append(idList, tempBookID, tempUserID)
        bookID, userID = tempBookID, tempUserID
    }
    bulkUpdateSql := fmt.Sprintf("UPDATE `bookshop`.`ratings` SET `ten_point` = true, "+
        "`score` = `score` * 2 WHERE (`book_id`, `user_id`) IN (%s)", placeHolder(len(idList)))
    db.Exec(bulkUpdateSql, idList...)
    return bookID, userID
}
// placeHolder format SQL place holder
func placeHolder(n int) string {
    holderList := make([]string, n/2, n/2)
    for i := range holderList {
        holderList[i] = "(?,?)"
    }
    return strings.Join(holderList, ",")
}在此处键入或粘贴代码
2

没看懂你问的是什么意思。

bulkUpdateSql := fmt.Sprintf("UPDATE `bookshop`.`ratings` SET `ten_point` = true, "+
    "`score` = `score` * 2 WHERE (`book_id`, `user_id`) IN (%s)", placeHolder(len(idList)))
db.Exec(bulkUpdateSql, idList...)

你这个代码里面写死了只能用两个条件查。用1个条件当然不行。

要用一个条件更新，这个bulkUpdateSql 要改，而且func placeHolder也要改。
从学习代码的角度来说，不如问下kimi。

3

用1个表的1个条件，更新表的时候，发现程序实际没有更新表。

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "strings"
    "time"
)

func main() {
    db, err := sql.Open("mysql", "root:xxxx@tcp(127.0.0.1:4000)/employees_dba_test")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    bookID := updateBatch(db, true, 0)
    fmt.Println("first time batch update success")
    for {
        time.Sleep(time.Second)
        bookID = updateBatch(db, false, bookID)
        fmt.Printf("batch update success, [bookID] %d\n", bookID)
    }
}

// updateBatch select at most 1000 lines data to update score
func updateBatch(db *sql.DB, firstTime bool, lastBookID int64) (bookID int64) {
    // select at most 1000 primary keys in five-point scale data
    var err error
    var rows *sql.Rows

    if firstTime {
        rows, err = db.Query("SELECT `book_id` FROM `employees_dba_test`.`ratings` " +
            "WHERE `ten_point` != true ORDER BY `book_id` LIMIT 1000")
    } else {
        rows, err = db.Query("SELECT `book_id` FROM `employees_dba_test`.`ratings` "+
            "WHERE `ten_point` != true AND `book_id` > ?  "+
            "ORDER BY `book_id` LIMIT 1000", lastBookID)
    }

    if err != nil || rows == nil {
        panic(fmt.Errorf("error occurred or rows nil: %+v", err))
    }

    // joint all id with a list
    var idList []interface{}
    for rows.Next() {
        var tempBookID int64
        if err := rows.Scan(&tempBookID); err != nil {
            panic(err)
        }
        idList = append(idList, tempBookID)
        bookID = tempBookID
    }

    bulkUpdateSql := fmt.Sprintf("UPDATE `employees_dba_test`.`ratings` SET `ten_point` = true, "+
        "`score` = `score` * 2 WHERE (`book_id`) IN (%s)", placeHolder(len(idList)))
    db.Exec(bulkUpdateSql, idList...)

    return bookID
}

// placeHolder format SQL place holder
func placeHolder(n int) string {
    holderList := make([]string, n/2)
    for i := range holderList {
        holderList[i] = "(?)"
    }
    return strings.Join(holderList, ",")
}
4

表结构是

create table ratings
    (
    book_id int,
    user_id int,
    ten_point BOOL NOT NULL DEFAULT FALSE,
    score int 
    );

insert into ratings values(1,1,false,50);
insert into ratings values(2,2,false,51);
insert into ratings values(3,3,false,54);
5

没有更新？代码写的是不是有问题，是不是需要 commit。。。。

6

打印下列表值，看能不能对上

7

怎么打印列值表？

8

不执行把sql打印出来看看，然后用打印的sql去执行下看看能不能更新到

1 个赞
9

create table ratings
(
book_id int,
user_id int,
ten_point BOOL NOT NULL DEFAULT FALSE,
score int
);

insert into ratings values(1,1,false,50);
insert into ratings values(2,2,false,51);
insert into ratings values(3,3,false,54);

UPDATE employees_dba_test.ratings SET ten_point = true, score = score * 2 WHERE (book_id) IN (1);
commit；
是可以更新数据的。

10

但是在go 程序中，不行。

11

代码是不是需要提交啊？

1 个赞