<p>I will start with a example:</p>
<p>Table <code>location</code>:</p>
<pre><code>+----------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | <null> | auto_increment |
| name | varchar(255) | NO | | <null> | |
+----------+------------------+------+-----+-------------------+-----------------------------+
</code></pre>
<p>Table <code>organization</code> which has foreign key relationship with user, but is not required:</p>
<pre><code>+------------------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | <null> | auto_increment |
| location_id | int(10) unsigned | YES | MUL | <null> | |
+------------------+------------------+------+-----+-------------------+-----------------------------+
</code></pre>
<p>Structs representing these tables (how it should be):</p>
<pre><code>type Location struct {
ID uint
Name string
}
type Organization struct {
ID uint
LocationID sql.NullUint32
}
</code></pre>
<p>But there is no NullUint32, only NullInt64, which in its maximum <em>positive</em> value would overflow the MySQL's maximum UNSIGNED INT, which is 32b field with ranges 0 - 4294967295, compared to Go's NullInt64 (-9223372036854775808 to 9223372036854775807)</p>
<p>Does anyone know why is there no sql.NullUint32 (or NullInt32) variants? </p>
<p>Is ther a better way to express these relationships?
Of course, I could just leave NullInt64 and leave it be, but I'd rather have the types describe my DB layout correctly.</p>
<p>Thank you.</p>
<hr/>**评论：**<br/><br/>0xjnml: <pre><p>Write your own NullUint64 type implementing the database/sql.Scanner interface.</p></pre>lunemec: <pre><p>Yes, that is a option, I was looking at the current implementation in the sql package, and it is rather complicated, and I don't want to copy&paste the entire sql package. My question is why is there no built-in type in the sql package, from looking at their source code, it looks to me a simple matter of writing 10 lines per type (with their current code)...</p></pre>williamwaack: <pre><p>Maybe in their evaluation, most programmers would not use the nullable types so much and that justifies casting your (db stored) int32, uint32 into a higher capacity NullInt64. So, if you need this optimization, as <a href="/u/0xjnml" rel="nofollow">/u/0xjnml</a> said, you could use the interfaces and write your own.</p></pre>0xjnml: <pre><p>Looks trivial to me: <a href="https://github.com/golang/go/blob/5476967b1a3d29fef4061999c00cadbec19ac0e3/src/database/sql/sql.go#L184-L208" rel="nofollow">https://github.com/golang/go/blob/5476967b1a3d29fef4061999c00cadbec19ac0e3/src/database/sql/sql.go#L184-L208</a></p></pre>lunemec: <pre><p>Yes, it did to me too if not for function convertAssign, look here: <a href="https://github.com/golang/go/blob/ff4ee8816226b6c84690a56fb3b16c9210e68431/src/database/sql/convert.go#L209-L425" rel="nofollow">https://github.com/golang/go/blob/ff4ee8816226b6c84690a56fb3b16c9210e68431/src/database/sql/convert.go#L209-L425</a></p></pre>neoasterisk: <pre><p>Check out <a href="https://husobee.github.io/golang/database/2015/06/12/scanner-valuer.html" rel="nofollow">this article</a>. It has helped me before when I had to create custom db types.</p></pre>Veonik: <pre><p>I have to say, that <code>convertAssign</code> function is a terrible, awful thing. You shouldn't need anything like that, since you know exactly what you're receiving from MySQL and exactly what you're trying to put it into.</p>
<p>Here's a full implementation for a type that simply unmarshals json into a struct and then marshals back into json to be stored as a byte array: <a href="https://github.com/motki/motki/blob/master/model/user.go#L324-L336" rel="nofollow">https://github.com/motki/motki/blob/master/model/user.go#L324-L336</a></p></pre>0xjnml: <pre><p>Not tested: <a href="https://play.golang.org/p/kfecNFJ1SO" rel="nofollow">https://play.golang.org/p/kfecNFJ1SO</a></p></pre>LadyDascalie: <pre><p>This is easy to do, write your own type, and satisfy a few interfaces:</p>
<p>Like so: <a href="https://goplay.space/#cW_1BGPcje" rel="nofollow">https://goplay.space/#cW_1BGPcje</a></p></pre>
