如何使用Golang将值插入十进制（65,0）并从数据库中检索它们？

Question

I am relatively new to Go. I am using github.com/go-sql-driver/mysql as my driver I have a table with many columns with data types of decimal(65,0). I am trying to use big.Int to handle these values in my application which seems reasonable to me. However I cannot find a way to insert the value in to database using Stmt.Exec. It says

"sql: converting argument $2 type: unsupported type big.Int, a struct"

Also I also could not find a way to serialize it back from mysql to my Go struct.

Answer 1

Use string values for SQL statements. Convert to and from *big.Int values. For example,

package main

import (
    "fmt"
    "log"
    "math/big"

    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)

func printN(rows *sql.Rows) {
    for rows.Next() {
        var n *big.Int
        var s sql.NullString
        err := rows.Scan(&s)
        if err != nil {
            log.Print(err)
        }
        n, _ = new(big.Int).SetString(s.String, 10)
        fmt.Printf("n: %v
", n.String())
    }
    err := rows.Err()
    if err != nil {
        log.Print(err)
    }
}

func main() {
    db, err := sql.Open("mysql", "peter:@/so")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    query := `SELECT n from so.dec65;`
    rows, err := db.Query(query)
    if err != nil {
        log.Fatal(err)
    }
    printN(rows)

    query = `SELECT n from so.dec65 WHERE n < ?;`
    arg1, ok := new(big.Int).SetString("12345678901234567891234567", 10)
    if ok {
        rows, err = db.Query(query, arg1.String())
        if err != nil {
            log.Fatal(err)
        }
        printN(rows)
    }
}

Database:

mysql> DESCRIBE dec65;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| n     | decimal(65,0) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
mysql> SELECT n FROM dec65;
+-----------------------------+
| n                           |
+-----------------------------+
|  12345678901234567891234567 |
| -12345678901234567891234567 |
|                        NULL |

+-----------------------------+

Output:

n: 12345678901234567891234567
n: -12345678901234567891234567
n: <nil>
n: -12345678901234567891234567