CODEWITHSUNDEEP
mysqlgoormbeego
JICHUN
JICHUN

Reputation: 113

MySQL's bit type maps to which Go type?

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

Upvotes: 2

Views: 8310

Answers (3)

Yan
Yan

Reputation: 450

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)

Upvotes: 0

Jonathan Hall
Jonathan Hall

Reputation: 79594

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
}

Upvotes: 4

Tarmo
Tarmo

Reputation: 1266

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
}

Upvotes: 8

Related Questions