Go / GoLang在MySQL中存储二进制数据

Question

I'm using the MySQL driver from http://github.com/go-sql-driver/mysql

I need to store the binary representation of an IP address in MySQL in a BINARY(4) column.

To do this, I've tried:

    startSlice := net.ParseIP(rangeStart).To4()
    var startBytes [4]byte
    copy(startSlice[:], startBytes[0:4])

    endSlice := net.ParseIP(rangeEnd).To4()
    var endBytes [4]byte
    copy(endSlice[:], endBytes[0:4])

    r, e := db.Exec("UPDATE AIPRangesBlocks SET BinRangeStart = ?, BinRangeEnd = ? WHERE IPGRID = ?", startBytes, endBytes, id)
    fmt.Println("result of update:", r)
    if e != nil {
        fmt.Println(e)
    }

Note that I used the copy command to convert from a []byte slice to simply a [4]byte array, but I get this error:

sql: converting Exec argument #0's type: unsupported type [4]uint8, a array

If I do it directly as net.ParseIP("some_ip").To4(), I get this error:

sql: converting Exec argument #0's type: unsupported type net.IP, a slice

How do I send the binary data?

EDIT

OK, if I use a hexadecimal string, it will execute the query, but I'm not getting the right values on retrieval.

I tried hex.EncodeToString() and "0x" + hex.EncodeToString() and neither are working properly.

Here's an example:

66.182.64.0 becomes 42b64000

If I store "42b64000" in my MySQL column, I get back:

52 50 98 54

If I store "0x42b64000" in my MySQL column, I get back:

48 120 52 50

How do I fix this?

Answer 1

If you store "42b64000" in my MySQL column, you get back:

52 50 98 54

Decoded into ASCII this is the start of the string which you gave it, eg

"42b6"

Which suggest that passing the IP address slice as a string will work, eg

startSlice := net.ParseIP(rangeStart).To4()
endSlice := net.ParseIP(rangeEnd).To4()
r, e := db.Exec("UPDATE AIPRangesBlocks SET BinRangeStart = ?, BinRangeEnd = ? WHERE IPGRID = ?", string(startSlice), string(endSlice), id)
fmt.Println("result of update:", r)
if e != nil {
    fmt.Println(e)
}

In go strings are essentially a read only version of []byte and you can cast between them with no problems (other than a bit of memory copying). Hopefully the quoting in the MySQL driver can deal with NULs in the string.