go-sql映射成非基本类型

sql映射成非基本类型

背景

数据库存储字段为：

foo: '["人工客服","人工","在吗","你好","客服"]'

使用 struct 保存：

type A struct {
	Foo: []string `json:"foo"`
	...
}

会报错：sql: Scan error on column index 1, name \"foo\": unsupported Scan, storing driver.Value type []uint8 into type *[]string

原因

foo 字段非基本映射类型，没有实现Scan 接口，不能把 []uint8（二进制）转 *[]string 类型。

database/sql/sql.go#Scan：

// Scanner is an interface used by Scan.
type Scanner interface {
	// Scan assigns a value from a database driver.
	//
	// The src value will be of one of the following types:
	//
	//    int64
	//    float64
	//    bool
	//    []byte
	//    string
	//    time.Time
	//    nil - for NULL values
	//
	// An error should be returned if the value cannot be stored
	// without loss of information.
	//
	// Reference types such as []byte are only valid until the next call to Scan
	// and should not be retained. Their underlying memory is owned by the driver.
	// If retention is necessary, copy their values before the next call to Scan.
	Scan(src any) error
}

从这个接口可以看出来，sql 映射的基本类型为 int64,float64,bool,[]byte,string,time.Time,nil 其他非基本类型，都要实现 Scan(src any) error 这个接口。

解决

实现 Scan 接口即可。

type A struct {
	Foo: TFoo `json:"foo"`
	...
}

type TFoo []string

func (t *TFoo) Scan(v any) error {
  switch data := v.(type) {
	case []byte:
		return json.Unmarshal(data, &t)
	case string:
		return json.Unmarshal([]byte(data), &t)
	default:
		return fmt.Errorf("invalid phrases field data: %v", v)
	}
}