Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: tidb 查询问题
|
username: zhangyan0328
【TiDB Usage Environment】Production environment or Test environment or POC
POC
【TiDB Version】
4.0.14 and 5.4.1
【Problem Encountered】
The SQL query results are the same after adding parentheses to different WHERE conditions.
【Reproduction Path】What operations were performed to encounter the problem
package main
import (
"encoding/json"
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
"github.com/pkg/errors"
)
/*
CREATE TABLE `tidb_demo` (
`id` INT UNSIGNED AUTO_INCREMENT COMMENT 'Primary key id',
`num` INT NOT NULL DEFAULT '0' COMMENT 'Quantity',
`img` VARCHAR(256) NOT NULL DEFAULT 'Image link',
PRIMARY KEY (`id`),
UNIQUE INDEX de(`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'demo table';
INSERT INTO tidb_demo(`id`,`num`,`img`) VALUES(1,1,'http://demo.com/demo1.png'),(2,2,'http://demo.com/demo2.png');
*/
// TidbDemo demo table
type TidbDemo struct {
Id int64 `db:"id" json:"id"` // Primary key id
Num int64 `db:"num" json:"num"` // Quantity
Img string `db:"img" json:"img"` // Image link
}
type Config struct {
DbName string
Host string
Port string
User string
Password string
Charset string
MaxIdleCons int
MaxOpenCons int
}
func main() {
// Create database connection
// TODO: Fill in database connection information
dbCfg := &Config{}
dbCfg.DbName = "test"
dbCfg.Host = "" //541
//dbCfg.Host = "" // 4014
dbCfg.Port = ""
dbCfg.User = ""
//dbCfg.User = "root"
dbCfg.Password = ""
//dbCfg.Password = ""
dbCfg.Charset = "utf8mb4"
db := newSqlInstance(dbCfg)
// First SQL execution
const querySql = "SELECT * FROM tidb_demo WHERE (id = ?)"
res1 := &TidbDemo{}
if err := db.Get(res1, querySql, 1); err != nil {
fmt.Println(errors.WithStack(err))
return
}
fmt.Println("1 time:", unsafeJsonToStr(res1))
// Second SQL execution
res2 := &TidbDemo{}
if err := db.Get(res2, querySql, 2); err != nil {
fmt.Println(errors.WithStack(err))
return
}
fmt.Println("2 time:", unsafeJsonToStr(res2))
fmt.Println()
// Non-primary key field
const querySql2 = "SELECT * FROM tidb_demo WHERE (num = ?)"
res3 := &TidbDemo{}
if err := db.Get(res3, querySql2, 1); err != nil {
fmt.Println(errors.WithStack(err))
return
}
fmt.Println("Non-primary key field 1 time", unsafeJsonToStr(res3))
// Second SQL execution
res4 := &TidbDemo{}
if err := db.Get(res4, querySql2, 2); err != nil {
fmt.Println(errors.WithStack(err))
return
}
fmt.Println("Non-primary key field 2 time", unsafeJsonToStr(res4))
}
func newSqlInstance(conf *Config) *sqlx.DB {
charset := conf.Charset
if charset == "" {
charset = "utf8,utf8mb4"
}
dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=%s", conf.User, conf.Password, conf.Host, conf.Port, conf.DbName, conf.Charset)
fmt.Println("dsn", dsn)
db := sqlx.MustConnect("mysql", dsn)
if conf.MaxIdleCons != 0 {
db.SetMaxIdleConns(conf.MaxIdleCons)
}
if conf.MaxOpenCons != 0 {
db.SetMaxOpenConns(conf.MaxOpenCons)
}
return db.Unsafe()
}
func unsafeJsonToStr(data interface{}) string {
raw, _ := json.Marshal(data)
return string(raw)
}
【Problem Phenomenon and Impact】
- Connecting to the TiDB cluster through proxysql 2.3.2 causes this issue.
- Direct connection to TiDB does not have this issue.
【Attachments】
Please provide the version information of each component, such as cdc/tikv, which can be obtained by executing cdc version/tikv-server --version.
|
username: 
|
username: 
|
username: 
|
username: