Any way to support JSON values inserted using []byte rather than string

[dterei]

Issue description

Golang uses []byte as the underlying type for JSON values. However, if you try to insert a value into MySQL for a JSON column using []byte and interpolateParams=true then it will fail with:

unable to upsert for t: Error 3144: Cannot create a
JSON value from a string with CHARACTER SET 'binary'

This appears to be becuase MySQL doesn't support inserting binary values into JSON columns, it wants an explicit character set to be specified (see bug).

While using string is a workaround of sorts, or disabling interpolateParams, hoping you may have better advice on how to solve this?

This problem causes issues when using ORM libraries like SQLBoiler that map a JSON field in a struct to []byte before invoking the SQL interface (see bug raised with SQLBoiler). SQLBoiler folks don't want to change their mapping from string to []byte for JSON just for MySQL.

Arguably the Go SQL interface should support JSON as a valid type rather than forcing it to be mapped to []byte or string, then go-sql-driver can decide on the mapping itself.

Configuration

Driver version (or git SHA): v1.4.0

Go version: 1.10.1

Server version: MySQL 5.7.21

Server OS: Arch Linux

[methane]

This is limitation of MySQL text protocol. How about stop using interpolateParams?

[methane]

BTW, can hex decimal literal be used for JSON?
#381

[dterei]

@methane I could stop using interpolateParams, just trying to understand the issue better and see if there is a solution that would allow me to keep it on.

[methane]

I think best solution is using string type.
There are no other way to distinguish text and binary value from driver's side.

[dterei]

@methane One question, how much performance is gained or not with interpolateParams? Are we waiting for two RTT for each statement? Or are we using the X-protoocol and pipelining the PREPARE/EXECUTE so really not much difference?

[dterei]

OK, nvm, seems we aren't using XAPI, so forced into 2-RTT by the protocol.

[renthraysk]

Think this fudge works...

js, err := json.Marshal(&o)
...
_, err = db.ExecContext(context.Background(), "INSERT json(json) VALUES(CAST(CONVERT(? USING utf8) AS JSON))", js)

[methane]

I don't know about SQLBoiler and I don't want to pay my time to learn it only for this issue.
But generally speaking, you can avoid interpolateParams selectively by using explicit
DB.Prepare() instead of DB.Exec() or DB.Query().

Additionally, you can skip 2-RTT by reusing explicitly prepared statement.