在使用`sql.DB.Exec`时,对于自定义类型可能会感到困惑。

huangapple go评论97阅读模式
英文:

Confused about custom types in SQL when sql.DB.Exec

问题

具有以下表结构:

CREATE TABLE `tableName` (
    `Id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `Status` enum('pending','rejected','sent','invalid') NOT NULL,
    `Body` varchar(255) NULL
) ENGINE='MyISAM' COLLATE 'utf8_general_ci';

我有这段(不完整的)代码运行良好

type StatusEnum string

const (
    STATUS_PENDING  StatusEnum = "pending"
    STATUS_REJECTED StatusEnum = "rejected"
    STATUS_SENT     StatusEnum = "sent"
    STATUS_INVALID  StatusEnum = "invalid"
)

func (s *StatusEnum) Scan(src interface{}) error {
    if src == nil {
        return errors.New("This field cannot be NULL")
    }

    if stringStatus, ok := src.([]byte); ok {
        *s = StatusEnum(string(stringStatus[:]))

        return nil
    }

    return errors.New("Cannot convert enum to string")
}

func (s *StatusEnum) Value() (driver.Value, error) {
    return []byte(*s), nil
}

type EmailQueue struct {
    Id        uint64
    Status    StatusEnum
    Body      sql.NullString
}

func Save (db *sql.DB) error {
    _, err = db.Exec(
        "UPDATE `tableName` SET `Status` = ?, `Body` = ? WHERE `id` = ?",
        &eqi.Status,
        eqi.Body,
        eqi.Id,
    )

    return err
}

所以我的问题是:为什么在db.Exec中需要使用指针引用(&eqi.Status)?

既然sql.NullString和我自定义的StatusEnum都没有在github.com/go-sql-driver/mysql中实现,为什么会有差异?

如果我不使用指针引用(eqi.Status),我会得到以下错误(在database/sql/convert.go中抛出):

sql: converting Exec argument #0's type: unsupported type emailqueue.StatusEnum, a string

我尝试实现了一些其他接口,但没有成功。

我猜测可能是结构体和类型继承的区别,但是我找不到任何线索... 在使用`sql.DB.Exec`时,对于自定义类型可能会感到困惑。

请帮助我理解发生了什么。谢谢! 在使用`sql.DB.Exec`时,对于自定义类型可能会感到困惑。

英文:

Having this table structure:

CREATE TABLE `tableName` (
    `Id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `Status` enum('pending','rejected','sent','invalid') NOT NULL,
    `Body` varchar(255) NULL
) ENGINE='MyISAM' COLLATE 'utf8_general_ci';

I have this (not complete) code working fine:

type StatusEnum string

const (
    STATUS_PENDING  StatusEnum = "pending"
    STATUS_REJECTED StatusEnum = "rejected"
    STATUS_SENT     StatusEnum = "sent"
    STATUS_INVALID  StatusEnum = "invalid"
)

func (s *StatusEnum) Scan(src interface{}) error {
    if src == nil {
	    return errors.New("This field cannot be NULL")
    }

    if stringStatus, ok := src.([]byte); ok {
	    *s = StatusEnum(string(stringStatus[:]))

	    return nil
    }

    return errors.New("Cannot convert enum to string")
}

func (s *StatusEnum) Value() (driver.Value, error) {
    return []byte(*s), nil
}

type EmailQueue struct {
    Id        uint64
    Status    StatusEnum
    Body      sql.NullString
}

func Save (db *sql.DB) error {
    _, err = db.Exec(
	    "UPDATE `tableName` SET `Status` = ?, `Body` = ? WHERE `id` = ?",
	    &eqi.Status,
	    eqi.Body,
	    eqi.Id,
    )

    return err
}

So my question is: Why do I need to use pointer reference (&eqi.Status) on db.Exec?

Both sql.NullString and my custom StatusEnum are not implemented in github.com/go-sql-driver/mysql, so why the difference?

If I don't use the pointer reference (eqi.Status), I'm getting this error (throwing in database/sql/convert.go):

sql: converting Exec argument #0's type: unsupported type emailqueue.StatusEnum, a string

I was trying to implement some other interfaces I've found with no luck.

I have other custom types implemented with sql.Scanner and sql.driver.Valuer interfaces, but the problem is the same.

I was guessing about struct and type inheritance differentiation, but I couldn't get any hint on that... 在使用`sql.DB.Exec`时,对于自定义类型可能会感到困惑。

Please help to understand what's going on. Thanks!!! 在使用`sql.DB.Exec`时,对于自定义类型可能会感到困惑。

1: https://golang.org/src/database/sql/convert.go#L37 "database/sql/convert.go"

答案1

得分: 5

你为StatusEnum类型实现了Valuer接口,该接口操作的是*StatusEnum。因此,当你将其作为参数传递给Exec时,只有将其作为指针才有意义,因为只有指向StatusEnum的指针实现了Value(),需要在内联中解引用。

你没有EmailList类型的定义,所以我可以建议对你给出的Valuer接口进行修改。

func (s StatusEnum) Value() (driver.Value, error) {
    return []byte(s), nil
}

这是标准库中实现自定义可空类型的方式。

英文:

You implement the Valuer interface for your StatusEnum type as operating on a *StatusEnum. Therefore when you pass one as a parameter to Exec it only makes sense as a pointer as only pointers to StatusEnum implement Value(), requiring a deference inline.

You don't have a definition for the EmailList type so I can suggest an alteration to your given Valuer interface.

func (s StatusEnum) Value() (driver.Value, error) {
    return []byte(s), nil
}

This is the standard library implements custom nullable types.

huangapple
  • 本文由 发表于 2015年5月16日 23:12:43
  • 转载请务必保留本文链接:https://go.coder-hub.com/30277296.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定