Gorm在自定义字符串类型上返回Scanner错误。

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

Gorm returns Scanner error on custom string type

问题

我写了下面的实体：

  1. type DataCategory string
  3. const (
  4. 	DataCategory1 DataCategory = "Category1"
  5. 	DataCategory2 DataCategory = "Category2"
  6. )
  8. type Data struct {
  9. 	Name       string         `json:"name"`
  10. 	Categories []DataCategory `json:"category" gorm:"type:text[]"`
  11. }

我手动在数据库中创建了一行，并将categories数组类型填充为Category1和Category2。

但是在读取数据时出现以下错误：

  1. sql: Scan error on column index 19, name "category": unsupported Scan, storing driver.Value type string into type *[]DataCategory

示例Value方法：

  1. func (s DataCategorySlice) Value() (driver.Value, error) {
  2. 	if s == nil {
  3. 		return nil, nil
  4. 	}
  5. 	if len(s) == 0 {
  6. 		return "{}", nil
  7. 	}
  9. 	v := []string{}
  10. 	for _, dc := range s {
  11. 		v = append(v, string(dc))
  12. 	}
  13. 	result := fmt.Sprintf("{%s}", strings.Join(v, ","))
  14. 	return result, nil
  15. }
英文:

I wrote the below entity:

  1. type DataCategory string
  3. const (
  4. 	DataCategory1 DataCategory = "Category1"
  5. 	DataCategory2 DataCategory = "Category2"
  6. )
  8. type Data struct {
  9. 	Name            string         `json:"name"`
  10. 	Categories      []DataCategory `json:"category" gorm:"type:text[]"`
  11. }

I manually created a row in the database and filled the categories array type with Category1 and Category2.

But this gives the below error when reading the data:

  1. sql: Scan error on column index 19, name "category": unsupported Scan, storing driver.Value type string into type *[]DataCategory

Example Value method:

  1. func (s DataCategorySlice) Value() (driver.Value, error) {
  2. 	if s == nil {
  3. 		return nil, nil
  4. 	}
  5. 	if len(s) == 0 {
  6. 		return "{}", nil
  7. 	}
  9. 	v := []string{}
  10. 	for _, dc := range s {
  11. 		v = append(v, string(dc))
  12. 	}
  13. 	result := fmt.Sprintf("{%s}", strings.Join(v, ","))
  14. 	return result, nil
  15. }

答案1

得分: 1

以下是翻译好的内容：

下面的示例假设您正在使用PostgreSQL作为关系数据库管理系统，并且DataCategory的值不包含逗号或未转义的单引号。

  1. // 声明自定义类型
  2. type DataCategorySlice []DataCategory
  1. // 实现driver.Valuer接口，将值编码为目标RDBMS接受的正确格式
  2. func (s DataCategorySlice) Value() (driver.Value, error) {
  3.     if s == nil {
  4.         return nil, nil
  5.     }
  6.     if len(s) == 0 {
  7.         return []byte(`{}`), nil
  8.     }
  10.     v := []byte(`{`)
  11.     for i := range s {
  12.         v = append(v, s[i]...)
  13.         v = append(v, ',')
  14.     }
  15.     v[len(v)-1] = '}' // 用闭合括号替换最后一个逗号
  16.     return v, nil
  17. }
  1. // 实现scanner接口，将从数据库检索到的原始源值解码
  2. func (s *DataCategorySlice) Scan(src any) error {
  3.     var data []byte
  4.     switch v := src.(type) {
  5.     case []byte:
  6.         data = v
  7.     case string:
  8.         data = []byte(v)
  9.     case nil:
  10.         return nil
  11.     default:
  12.         return fmt.Errorf("不支持的类型：%T", src)
  13.     }
  14.     if len(data) == 0 {
  15.         return nil
  16.     }
  17.     data = data[1:len(data)-1] // 移除周围的大括号
  18.     for _, v := range bytes.Split(data, []byte{','}) {
  19.         *s = append(*s, DataCategory(v))
  20.     }
  21.     return nil
  22. }

或者，如果从Value() (driver.Value, error)返回[]byte不起作用，例如导致"ERROR: malformed array literal: <hex representation of the field's value> (SQLSTATE 22P02)"，那么您可以尝试将返回类型更改为string。

来自@kozhioyrin的示例

  1. // 实现driver.Valuer接口，将值编码为目标RDBMS接受的正确格式
  2. func (s DataCategorySlice) Value() (driver.Value, error) {
  3. 	if s == nil {
  4. 		return nil, nil
  5. 	}
  6. 	if len(s) == 0 {
  7. 		return "{}", nil
  8. 	}
  10. 	v := []string{}
  11. 	for _, dc := range s {
  12. 		v = append(v, string(dc))
  13. 	}
  14. 	result := fmt.Sprintf("{%s}", strings.Join(v, ","))
  15. 	return result, nil
  16. }
英文:

The following example assumes that you're using PostgreSQL as the RDBMS and that the DataCategory values do not contain commas nor unescaped single quotes.

  1. // declare the custom type
  2. type DataCategorySlice []DataCategory
  1. // implement driver.Valuer to encode the value into the
  2. // correct format that is accepted by the target RDBMS
  3. func (s DataCategorySlice) Value() (driver.Value, error) {
  4.     if s == nil {
  5.         return nil, nil
  6.     }
  7.     if len(s) == 0 {
  8.         return []byte(`{}`), nil
  9.     }
  11.     v := []byte(`{`)
  12.     for i := range s {
  13.         v = append(v, s[i]...)
  14.         v = append(v, &#39;,&#39;)
  15.     }
  16.     v[len(v)-1] = &#39;}&#39; // replace last comma with closing brace
  17.     return v, nil
  18. }
  1. // implement scanner to decode the raw source
  2. // value as retrieved from the database
  3. func (s *DataCategorySlice) Scan(src any) error {
  4.     var data []byte
  5.     switch v := src.(type) {
  6.     case []byte:
  7.         data = v
  8.     case string:
  9.         data = []byte(v)
  10.     case nil:
  11.         return nil
  12.     default:
  13.         return fmt.Errorf(&quot;unsupported type: %T&quot;, src)
  14.     }
  15.     if len(data) == 0 {
  16.         return nil
  17.     }
  18.     data = data[1:len(data)-1] // remove surrounding braces
  19.     for _, v := range bytes.Split(data, []byte{&#39;,&#39;}) {
  20.         *s = append(*s, DataCategory(v))
  21.     }
  22.     return nil
  23. }

Alternatively, if returning []byte from Value() (driver.Value, error) doesn't work, e.g. it results in &quot;ERROR: malformed array literal: &lt;hex representation of the field&#39;s value&gt; (SQLSTATE 22P02)&quot;, then you can try using string as the return type instead.

<sub>example from @kozhioyrin</sub>

  1. // implement driver.Valuer to encode the value into the
  2. // correct format that is accepted by the target RDBMS
  3. func (s DataCategorySlice) Value() (driver.Value, error) {
  4. 	if s == nil {
  5. 		return nil, nil
  6. 	}
  7. 	if len(s) == 0 {
  8. 		return &quot;{}&quot;, nil
  9. 	}
  11. 	v := []string{}
  12. 	for _, dc := range s {
  13. 		v = append(v, string(dc))
  14. 	}
  15. 	result := fmt.Sprintf(&quot;{%s}&quot;, strings.Join(v, &quot;,&quot;))
  16. 	return result, nil
  17. }

huangapple
  • 本文由 发表于 2023年4月11日 23:46:22
  • 转载请务必保留本文链接：https://go.coder-hub.com/75987793.html
  • go
  • go-gorm
匿名

发表评论

匿名网友

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

确定