背景

项目常用关系型数据库MYSQL，理论上遵循范式，应该为每个模型设计一个表，减少表之间冗余数据 往往实际业务会有很多冗长/相似/无需（或者极少情况需要）索引的数据, 举个例子：

{
    "id":"-LsaoCreHzrHXQ4sKseq",
    "name":"123",
    "image":{
        "uid":"ba791a9c-56bf-43c9-8317-9b4efe19ef3b",
        "name":"inside_corbit.png",
        "url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/prod/world/-LsanywxSrSpx1T_0AVF/topic/-LsaoCreHzrHXQ4sKseq/inside_corbit.png",
        "size":99802,
        "status":"done"
    },
    "introAudio":{
        "uid":"ab856fb2-076b-46ac-afb7-32b8caf86a00",
        "name":"circuits_video_intro.mp3",
        "url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/prod/world/-LsanywxSrSpx1T_0AVF/topic/-LsaoCreHzrHXQ4sKseq/circuits_video_intro.mp3",
        "size":88868,
        "status":"done"
    },
    "celebrations":[
        {
            "audio":{
                "uid":"c34a588c-e008-4bf8-9054-77fe907122fb",
                "name":"circuits_video_intro.mp3",
                "url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/prod/world/-LsanywxSrSpx1T_0AVF/topic/-LsaoCreHzrHXQ4sKseq/circuits_video_intro.mp3",
                "size":88868,
                "status":"done"
            }
        }
    ]
}

面对这种情况，去设计多个关联表必然是一个复杂的工作。如果项目存在大量这种需求，可能需要审视数据库选型，是否非关系型数据库更为合适。

针对其他原因，使用Mysql的情况下，可以使用mysql json特性

gorm + json

源码

package main

import (
	"database/sql/driver"
	"encoding/json"
	"fmt"
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"
)

// model
type Medium struct {
	URL  string `json:"url"`
	Name string `json:"name"`
}

type Demo struct {
	Id    uint64
	Icon  Medium     `gorm:"TYPE:json"`
}

func (c Medium) Value() (driver.Value, error) {
	b, err := json.Marshal(c)
	return string(b), err
}

func (c *Medium) Scan(input interface{}) error {
	return json.Unmarshal(input.([]byte), c)
}


func main(){
	// connect db
	config := fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=utf8mb4&parseTime=%t&loc=%s",
		"root",
		"123",
		"localhost:3306",
		"go-to-gorm",
		true,
		"Local")

	db, _ := gorm.Open("mysql", config)

	// construct data
	names := []string{
		"aa",
		"bb",
		"cc",
		"dd",
		"ee",
	}
	for i:=0; i< 5; i++ {
		demo := Demo{
			Icon: Medium{
				URL:"http://makeblock.com/" + names[i],
				Name:names[i],
			},
		}
		fmt.Println(demo.Icon)
		db.Table("demo").Create(&demo)
	}

	//search json filed
	var item Demo
	db.Table("demo").Where("icon->'$.name' = (?)", names[4]).First(&item)
	fmt.Println(item)

}

模型函数

为模型实现Value/Scan函数

func (c Medium) Value() (driver.Value, error) {
	b, err := json.Marshal(c)
	return string(b), err
}

func (c *Medium) Scan(input interface{}) error {
	return json.Unmarshal(input.([]byte), c)
}

添加json类型标签

type Demo struct {
	Id    uint64
	Icon  Medium     `gorm:"TYPE:json"`
}

获取json类型属性

db.Table("demo").Where("icon->'$.name' = (?)", names[4]).First(&item)