minikube - 为什么 YugaByteDB YSQL 选择查询在使用 bytea 数据类型时会返回意外字节？

我有一个解决方案

这是我的代码：

    /*
    ===============================
    GOLANG CODE REGISTERING A USER:
   ================================
   */
    db := dbconnect()
    defer db.Close() 
    log.Println("Registration Server says: user.Photo = ", user.Photo[:100])
    usrInsert := fmt.Sprintf("INSERT INTO users (user_id, 
username, Phone, Email, Nationality, Photo, pxwrd) 
    VALUES ('%s','%s','%s','%s','%s','%x','%s')", 
    user.user_id, user.username, user.Phone, 
user.Email, user.Nationality, user.Photo, 
encriptedpassword) 
      
    _, err := db.Exec(usrInsert)
    
    if err != nil {
        fmt.Println(err.Error());
    }
/*
=============================================
OUTPUT OF 'log.Println' ABOVE WAS:
============================================= 
    Registration Server says: user.Photo = [1 0 0 0 0 0 6 
213 194 0 0 0 24 255 216 255 224 0 16 74 70 73 70 0 1 2 1 
1 44 1 44 0 0 255 225 16 115 69 120 105 102 0 0 77 77 0 42 
0 0 0 8 0 11 1 15 0 2 0 0 0 18 0 0 0 146 1 16 0 2 0 0 0 10 
0 0 0 164 1 18 0 3 0 0 0 1 0 1 0 0 1 26 0 5 0 0 0 1 0 0 0]
*/


/*
=============================
GOLANG CODE FOR USER LOGIN:  
=============================
Note: the user profile Photo is 
saved as 'bytea' data type
*/
    //select query for loging in
    selecQuery := fmt.Sprintf("SELECT * FROM users WHERE 
username='%s' AND pxwrd='%x' ", 
uname, encriptedpassword)
        queryResult, err := db.Query(selecQuery)
        if err != nil {
            fmt.Println(err.Error())  
        }  
        defer queryResult.Close() 
        userExists := false 
        for queryResult.Next() {
            userExists = true
            
            var UserX rs_utils.User
            var cnt, psw string
            err = queryResult.Scan(&cnt, &UserX.User_id, 
&UserX.Username, &UserX.Phone, &UserX.Email, 
&UserX.Nationality, &UserX.Photo, &psw)   
              
            if err != nil {
                log.Println("!! Error: ", err)
                break
            } 
             
            log.Println("UserX.Photo, psw :\n", UserX.Photo[:100], psw)
             
            //...
            /...
            
         }

        /*   
        ==============================
        OUTPUT OF 'log.Println' ABOVE:
        ==============================
        UserX.Photo, psw :
        [48 49 48 48 48 48 48 48 48 48 48 48 49 53 97 98 48 53 48 48 48
        48 48 48 50 97 56 57 53 48 52 101 52 55 48 100 48 97 49 97 48 97
        48 48 48 48 48 48 48 100 52 57 52 56 52 52 53 50 48 48 48 48 48
        52 54 99 48 48 48 48 48 50 55 102 48 56 48 54 48 48 48 48 48 48
        50 100 54 50 52 53 53 51 48 48 48 48 48 
        48 48 49] 8ae5776067290c4712fa454006c8ec6
*/

如您所见，检索到的二进制数据与插入的数据非常不同。原因：

usrInsert := fmt.Sprintf("INSERT INTO users (user_id, username, 
Phone, Email, Nationality, Photo, pxwrd) 
VALUES ('%s','%s','%s','%s','%s','%x','%s')", 
user.user_id, user.username, user.Phone, 
user.Email, user.Nationality, user.Photo, 
encriptedpassword) 
      
_, err := db.Exec(usrInsert)

//AND

selecQuery := fmt.Sprintf("SELECT * FROM users WHERE 
username='%s' AND pxwrd='%x' ", uname, encriptedpassword) 

queryResult, err := db.Query(selecQuery)
//Notice the string formatting in the queries.

解决方案： 我将上面的代码行替换为：

_, err := db.Exec(`INSERT INTO users (user_id, username, 
Phone, Email, Nationality, Photo, pxwrd) 
VALUES ($1, $2, $3, $4, $5, $6, $7)`, 
user.user_id, user.username, user.Phone, user.Email, 
user.Nationality, user.Photo, encriptedpassword)

//AND

queryResult, err := db.Query(`SELECT * FROM users WHERE 
username=$1 AND pxwrd=$2 `, uname, encriptedpassword)

分别。

minikube - 为什么 YugaByteDB YSQL 选择查询在使用 bytea 数据类型时会返回意外字节？

问题描述

解决方案

推荐阅读