MySQL's bit type maps to which Go type?

Question

I used Java before, so some columns' type in database table is bit(1). But now I want to use beego to rebuild my project and I don't want to alter my database table (need do much). I use beego's orm in my project. So which Go type should I use?

Table like this and the deleted column has the question:

+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | varchar(255) | NO   | PRI | NULL    |       |
| created_time | datetime     | YES  |     | NULL    |       |
| deleted      | bit(1)       | NO   |     | NULL    |       |
| updated_time | datetime     | YES  |     | NULL    |       |
| icon_class   | varchar(255) | YES  |     | NULL    |       |
| mark         | varchar(255) | YES  |     | NULL    |       |
| name         | varchar(255) | YES  |     | NULL    |       |
| parent       | varchar(255) | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+

go struct like this:

type BaseModel struct {
    Id          string           `orm:"pk";form:"id"`
    CreatedTime time.Time        `orm:"auto_now_add;type(datetime)";form:"-"`
    UpdatedTime time.Time        `orm:"auto_now;type(datetime)";form:"-"`
    Deleted     bool `form:"-"`
}

When I use bool in my code, the error like this:

`[0]` convert to `*orm.BooleanField` failed, field: shareall-go/models.Category.BaseModel.Deleted err: strconv.ParseBool: parsing "\x00": invalid syntax

First, you should use `BOOL` in MySQL if your type corresponds to a boolean value (as it appears to, based on the name). Once you've made that change, hopefully the answer to your actual question will be obvious. — Jonathan Hall, Nov 28 '17 at 15:57
But I think I can't change the type. The old Java program is running now. I'm not sure the old program will run correctly after my change. — JICHUN, Nov 28 '17 at 16:02
If you're required to use that schema, that sucks, but go with it. But my comment should still help answer your question. — Jonathan Hall, Nov 28 '17 at 16:04

score 7 · Answer 1 · answered Sep 20 '19 at 08:08

Sqlx has created also a custom bool datatype for such situations and it works fine. Link to related code

// BitBool is an implementation of a bool for the MySQL type BIT(1).
// This type allows you to avoid wasting an entire byte for MySQL's boolean type TINYINT.
type BitBool bool

// Value implements the driver.Valuer interface,
// and turns the BitBool into a bitfield (BIT(1)) for MySQL storage.
func (b BitBool) Value() (driver.Value, error) {
    if b {
        return []byte{1}, nil
    } else {
        return []byte{0}, nil
    }
}

// Scan implements the sql.Scanner interface,
// and turns the bitfield incoming from MySQL into a BitBool
func (b *BitBool) Scan(src interface{}) error {
    v, ok := src.([]byte)
    if !ok {
        return errors.New("bad []byte type assertion")
    }
    *b = v[0] == 1
    return nil
}

The Scan func can be used as follows; var valOrg interface{} var valBB types.BitBool var val bool .... err = query.Scan(..., &valOrg,...) .... err = valBB.Scan(valOrg) val = bool(valBB) — udayanga, Jan 20 '21 at 01:21

Jonathan Hall · Answer 2 · 2019-11-08T10:07:05.243

So which Go type should I use?

Generally, this depends on how you're using the data more than how it's stored. As you eluded to, you tried using it as a Bool (which makes sense) but got an error.

The problem is that MySQL expresses a BIT differently than a BOOL, and the Go MySQL driver expects a MySQL BOOL. You can fix this by using a custom type that implements the sql.Scanner interface. Since you presumably have only two (or maybe three, if you count NULL) inputs, it should be fairly easy. Note this code is incomplete and untested. It is meant to serve as a guide, not a copy-and-paste solution.

type MyBool bool

func (b *MyBool) Scan(src interface{}) error {
    str, ok := src.(string)
    if !ok {
        return fmt.Errorf("Unexpected type for MyBool: %T", src)
    }
    switch str {
    case "\x00":
        v := false
        *b = v
    case "\x01":
        v := true
        *b = v
    }
    return nil
}

score 0 · Answer 3 · answered Oct 27 '21 at 04:47

I have no idea why Tarmo's solution doesn't work for me. But after a little bit modification, it works.

type BitBool bool

func (bb BitBool) Value() (driver.Value, error) {
    return bool(bb), nil
}

func (bb *BitBool) Scan(src interface{}) error {
    if src == nil {
        // MySql NULL value turns into false
        *bb = false
        return nil
    }
    bs, ok := src.([]byte)
    if !ok {
        return fmt.Errorf("Not byte slice!")
    }
    *bb = bs[0] == 1
    return nil
}

In this way, I can do the following

var isVip BitBool
row := db.QueryRow("SELECT is_vip FROM user WHERE user_id = '12345'")
err := row.Scan(&isVip)

var isVip BitBool = true
rows, err := db.Query("SELECT username FROM user WHERE is_vip = ?", isVip)

MySQL's bit type maps to which Go type?

3 Answers3

Linked

Related