MySQL `FLOAT` actually stores `DOUBLE`?-CodePudding

I have the following table in MySQL:

create table testfloat (f float unsigned);
insert into testfloat values (70.99);

So this should store 32-bit float equivalent of 70.99 into the table.

I have the following code to read the value from DB:

package main

import (
    "database/sql"
    "fmt"
    "strconv"

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

func main() {
    db, err := sql.Open("mysql", "root@(localhost)/test")
    if err != nil {
        panic(err)
    }

    rows, err := db.Query("select f from testfloat;")
    if err != nil {
        panic(err)
    }

    fmt.Printf("32-bit 70.99: %s\n", strconv.FormatFloat(70.99, 'f', 50, 32))
    fmt.Printf("64-bit 70.99: %s\n", strconv.FormatFloat(70.99, 'f', 50, 64))
    fmt.Printf("64-bit 70.99 cast from 32-bit 70.99: %s\n", strconv.FormatFloat(float64(float32(70.99)), 'f', 50, 64))

    var f float64
    for rows.Next() {
        if err := rows.Scan(&f); err != nil {
            panic(err)
        }
        fmt.Printf("DB 70.99: %.50f\n", f)
    }
}

The output is:

32-bit 70.99: 70.98999786376953125000000000000000000000000000000000
64-bit 70.99: 70.98999999999999488409230252727866172790527343750000
64-bit 70.99 cast from 32-bit 70.99: 70.98999786376953125000000000000000000000000000000000
DB 70.99: 70.98999999999999488409230252727866172790527343750000

If the DB was storing the float as 32 bits, then the DB output should be equal to the third line. But instead it's equal to the 2nd line. So it seems that it's storing a 64-bit float even though I specified float as the type. Why is this the case?

CodePudding user response：

create table test (f float unsigned, d double unsigned);
insert into test values (70.99, 70.99);
SELECT f, d, f=d FROM test;

f	d	f=d
70.99	70.99	0

fiddle

As you can see float and double columns stores the same value visually but they are not equal.

The effect which you see in your code is produced by Go, not by MySQL. MySQL returns the output rowset (and numeric values in it) in textual format (really - as binary stream), not as binary numbers - you may test this with a sniffer.