Try to use JSON for complex types for storage, facilitating automatic conversion to objects upon scanning, avoiding custom parsing

Here‘s an example. Suppose we need to implement a product sale specification list that includes selectable shard numbers, shard capacity, and replica numbers, as shown in the image below (non-production code, for example learning only):

Our table design is as follows:

CREATE TABLE `sell_spec` (
    `id`            int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
    `product`       varchar(45) NOT NULL COMMENT 'Product Name',
    `resources`     json NOT NULL COMMENT 'Resource specifications (cpu:memory), for example: ["0:0.25", "0:1", "1:2"]',
    `disk_min`      int(10) DEFAULT NULL COMMENT 'Minimum disk capacity',
    `disk_max`      int(10) DEFAULT NULL COMMENT 'Maximum disk capacity',
    `disk_step`     int(10) DEFAULT NULL COMMENT 'Disk increment size',
    `shards`        json NOT NULL COMMENT 'Shard specifications, for example: [1,3,5,8,12,16,24,32,40,48,64,80,96,128]',
    `replicas`      json NOT NULL COMMENT 'Replica specifications, for example: [1,2,3,4,5,6,7,8,9,12]',
    `created_at`    datetime DEFAULT NULL COMMENT 'Creation Time',
    `updated_at`    datetime DEFAULT NULL COMMENT 'Update Time',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Sale Specification Configuration';

We define resources, shards, replicas in json format to store custom lists of specifications for resources, shards, and replicas (non-sequentially). The go struct definition is as follows:

// SellSpec is a data structure automatically generated by the GoFrame tool and maintained by the tool.
type SellSpec struct {
    Id        uint        `description:"Primary Key"`
    Product   string      `description:"Product Name"`
    Resources string      `description:"Resource specifications (cpu:memory), for example: [\"0:0.25\", \"0:1\", \"1:2\"]"`
    DiskMin   int         `description:"Minimum disk capacity"`
    DiskMax   int         `description:"Maximum disk capacity"`
    DiskStep  int         `description:"Disk increment size"`
    Shards    string      `description:"Shard specifications, for example: [1,3,5,8,12,16,24,32,40,48,64,80,96,128]"`
    Replicas  string      `description:"Replica specifications, for example: [1,2,3,4,5,6,7,8,9,12]"`
    CreatedAt *gtime.Time `description:"Creation Time"`
    UpdatedAt *gtime.Time `description:"Update Time"`
}

// SellSpecItem is a custom data structure extending the entity,
// some fields such as Resources/Shards/Replicas are overridden to array types for automatic type conversion when operating with ORM.
type SellSpecItem struct {
    entity.SellSpec
    Resources []string `dc:"Resource specifications"`
    Shards    []int    `dc:"Shard specifications"`
    Replicas  []int    `dc:"Replica specifications"`
}

In the program, we can write and query data records as follows.

Data writing:

_, err = dao.SellSpec.Ctx(ctx).Data(v1.SellSpecItem{
    SellSpec: entity.SellSpec{
        Product:  "redis",
        DiskMin:  50,
        DiskMax:  1000,
        DiskStep: 10,
    },
    Resources: []string{"1:2", "2:4", "4:8"},
    Shards:    []int{1, 3, 5, 8, 12, 16, 24, 32, 40, 48, 64, 80, 96, 128},
    Replicas:  []int{1, 2, 3, 4, 5, 6, 7, 8, 9, 12},
}).Insert()

Data querying, the ORM component will automatically convert records in the data table to array type attributes corresponding to the go struct:

var items []v1.SellSpecItem
err = dao.SellSpec.Ctx(ctx).Scan(&items)