go - 不支持扫描，将 driver.Value 类型 []uint8 存储为 *[]string 类型

Question

我已经实现了rest api使用 golang , gin和 gorp

Employee structure:

type Employee struct {
  Id            int64  `db:"id" json:"id"`
  Firstname string `db:"firstname" json:"firstname"`
  Lastname  string `db:"lastname" json:"lastname"`
  Dob           time.Time `db:"dob" json:"dob"`
  Skills        []string `db:skills json:"skills"`
}

在 POST发送请求为:

func PostEmployee(c *gin.Context) {
  var emp Employee
  c.Bind(&emp)

  skills, _ := json.Marshal(emp.Skills)

  if emp.Firstname != "" && emp.Lastname != "" {

    if insert, _ := dbmap.Exec(`INSERT INTO employee (firstname, lastname, dob, skills) VALUES (?, ?, ?, ?)`, emp.Firstname, emp.Lastname, emp.Dob, skills); insert != nil {
        emp_id, err := insert.LastInsertId()
    .....
    }
  ......
  }

这会将数据保存到 mysql数据库，完美运行。

用于从数据库中检索数据已实现 GET要求

 func GetEmployees(c *gin.Context) {
   var emps []Employee
   _, err := dbmap.Select(&emps, "SELECT * FROM employee")
   log.Println(err)
   if err == nil {
     c.JSON(200, emps)
 } else {
     c.JSON(404, gin.H{"error": "no employee(s) into the table"})
 }

GET查询不提供来自数据库的任何数据， log.Println(err)日志说:

 Scan error on column index 4: unsupported Scan, storing driver.Value type []uint8 into type *[]string

有任何想法吗？

Answer 1

两种方法:
1.实现自定义类型的sql.Scanner和driver.Valuer接口(interface)
好处:

易于存储和检索

不必从另一个表中查询/加载

注意事项:

字符串大小受 sql 列定义的限制(即在本例中为 255)。
根据您的数据库架构，这将被截断或导致需要处理的错误。

跳过箍以删除数据库级别的某人/每个人的特定技能。

搜索需要通过包含而不是相等运算符

将来修改技能结构会很困难。

package tgorm

import (
    "database/sql/driver"
    "encoding/json"
    "errors"
    "fmt"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
    "github.com/stretchr/testify/assert"
    "strings"
    "testing"
    "time"
)

type Skills []string

func (s Skills) Value() (driver.Value, error) {
    if len(s) == 0 {
        return "[]", nil
    }
    return fmt.Sprintf(`["%s"]`, strings.Join(s, `","`)), nil
}

func (s *Skills) Scan(src interface{}) (err error) {
    var skills []string
    switch src.(type) {
    case string:
        err = json.Unmarshal([]byte(src.(string)), &skills)
    case []byte:
        err = json.Unmarshal(src.([]byte), &skills)
    default:
        return errors.New("Incompatible type for Skills")
    }
    if err != nil {
        return
    }
    *s = skills
    return nil
}

type Employee struct {
    Id        int64     `db:"id" json:"id"`
    Firstname string    `db:"firstname" json:"firstname"`
    Lastname  string    `db:"lastname" json:"lastname"`
    Dob       time.Time `db:"dob" json:"dob"`
    Skills    Skills    `gorm:"type:varchar(255);" db:"skills" json:"skills"`
}

func (e Employee) TableName() string {
    return "employee"
}


func getMemoryDataBase() *gorm.DB {
    db, err := gorm.Open("sqlite3", ":memory:")
    if err != nil {
        panic(err)
    }
    db = db.Debug()
    db.AutoMigrate(Employee{})
    return db
}

func TestSaveEmployee(t *testing.T) {
    db := getMemoryDataBase()
    emp := Employee{
        Id:        1,
        Firstname: "Fake",
        Lastname:  "Emp",
        Dob:       time.Time{},
        Skills:    []string{"C#", "GO", "C++"},
    }
    skills, _ := json.Marshal(emp.Skills)
    err := db.Exec(`INSERT INTO employee (firstname, lastname, dob, skills) VALUES (?, ?, ?, ?)`, emp.Firstname, emp.Lastname, emp.Dob, skills).Error
    assert.Nil(t, err)
    var emps []Employee
    err = db.Raw("SELECT * FROM employee").Scan(&emps).Error
    assert.Nil(t, err)
    assert.Equal(t, []Employee{emp}, emps)
}

2. 将技能移到单独的表中，并引用员工。
好处:

扩展表定义

更好的搜索能力

更容易在数据库级别为某人/每个人放弃技能

注意事项:

需要再次查询/加载技能

增加了数据库模式定义

package subgrom

import (
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
    "github.com/stretchr/testify/assert"
    "testing"
    "time"
)

type Skill struct {
    Id          int64  `db:"id" json:"id"`
    Skill       string `db:"skill" json:"skill"`
    EmployeeRef int64
}

type Employee struct {
    Id        int64     `db:"id" json:"id"`
    Firstname string    `db:"firstname" json:"firstname"`
    Lastname  string    `db:"lastname" json:"lastname"`
    Dob       time.Time `db:"dob" json:"dob"`
    Skills    []Skill   `db:"skills" json:"skills" gorm:"foreignkey:EmployeeRef"`
}

func (e Employee) TableName() string {
    return "employee"
}

func getMemoryDataBase() *gorm.DB {
    db, err := gorm.Open("sqlite3", ":memory:")
    if err != nil {
        panic(err)
    }
    db = db.Debug()
    db.AutoMigrate(Employee{}, Skill{})
    return db
}

func TestSaveEmployee(t *testing.T) {
    db := getMemoryDataBase()
    emp := Employee{
        Id:        1,
        Firstname: "Fake",
        Lastname:  "Emp",
        Dob:       time.Time{},
        Skills:    []Skill{{Skill: "C#"}, {Skill: "GO"}, {Skill: "C++"}},
    }
    err := db.Create(&emp).Error
    assert.Nil(t, err)
    var emps []Employee
    err = db.Preload("Skills").Find(&emps).Error
    assert.Nil(t, err)
    assert.Equal(t, []Employee{emp}, emps)
}