mysql: Can't scan a null into a *string

Since this page still shows up fairly high in search results, my two cents:

You can also solve it in the part where, imo, the problem actually lies: The database abstraction level. You can work around this problem by doing the following:

  SELECT
    id,
    COALESCE(name, '') as name
  FROM users

This way, if name contains NULL, it’s substituted for an empty string when scanning. Coalesce is widely supported.

This example works for me

The old code returned an empty []byte for NULL values which made it impossible to distinguish a NULL value from an empty string. See Issue #20 for details.

The error is returned by the database/sql package because of this goal:

* Be flexible with type conversions, but be paranoid about silent
  truncation or other loss of precision.

You must use http://golang.org/pkg/database/sql/#NullString if the column may contain NULL values

Besides @Dynom great comment, I think can still be worth mentioning that the sql.NullString type comes handy if you want to address the problem in the destination of the Scan (which I see as the Go side of the database abstraction level).

Additionally, it preserves the ability to distinguish between a nil value and an empty string.

Another workaround came just to my mind: Just use []byte instead of string. Converting a nil-[]byte results in an empty string:

string([]byte(""))  // => ""
string([]byte(nil)) // => ""

http://play.golang.org/p/nivY1yBK3x

You can pass a string pointer to deal with it like this:

var txt *string
checkErr(result.Scan(&txt))
// do something with type *string

it works fine for me.

the issue with sql.NullString is all the additional boilerplate to unMarshal when interfacing with many different micro-services

dest is basically an []interface{} slice. The default value of interface{} is nil. So skip setting dest[i] has the same result as setting dest[i]=nil

I updated the branch. You can try it out for yourself if you want.

I’d also prefer the database/sql package to follow Go’s “default zero value” policy. You could still differentiate with the Null* types if you need to, but unfortunately the design decision was made this way. Maybe they change it in Go2 (+1 from me for that). For now I have no plan to add a driver option for that. Compared to PostgreSQL the protocol already is a mess (tough i assume more efficient). I don’t want to jumble the driver even more. One str = nullStr.Value more per scanned string is the lesser evil for now.

Thanks, that makes sense.

I almost want a way to configure the library to give me the old behavior. In some ways, Go’s “zero value as default” makes a lot of sense and eliminates a ton of boilerplate code. I’d kind of like to put that into one place, configurably, instead of scattering it everywhere in my code that I might encounter a NULL. Using the NullString, etc is pretty ugly code-wise.

NULL is just a nightmare and I want to get rid of NULLs. I explicitly define every column in my tables as NOT NULL, but occasionally a command like SHOW PROCESSLIST is going to have a NULL in it, and my code just doesn’t care; I want an empty string instead. I’m just going to use a NullString, and ignore its .Valid property and just get its .String, which will be empty if the column was NULL.

Being able to tell the driver to convert NULL to zero-value for the type (or actually, I guess what it’d really do is skip scanning the column into the variable) would reduce a lot of hard and error prone (and not-future-proof) work for me. Or, if I don’t want all the boilerplate code, I can take my chances that the column a) is really non-nullable b) will stay that way forever.