Json column insert into mysql db using GORM - Stack Overflow

admin管理员组

文章数量:1353240

I have two structs in Go that look like the following:

type struct UserInfo{
   Name string `json:"name"`
   Age int `json:"age"`
}

type struct Profile{
   ID string `json:"id"`
   User *UserInfo `json:"user"`
   UserType string `json:"user_type"`
}

The SHOW CREATE TABLE for profile looks like the following:

CREATE TABLE `profile`{
  `id` varchar(24) NOT NULL,
  `user` json DEFAULT NULL,
  `user_type` varchar(24) DEFAULT NULL
}

Here is my code:

var profile Profile
Profile.ID = "Demo~1"
Profile.UserType = "Permanent"
Profile.User = &User {
  Name: "John",
  Age: 24,
}

if err:= db.Create(&profile).Error; err != nil {
  fmt.Errorf("Unsuccessful insert")
}

The insert runs successfully and but when I check the mysql db, I see that all the fields have expected values except the json field user which remains NULL.

Can anyone help?

I have two structs in Go that look like the following:

type struct UserInfo{
   Name string `json:"name"`
   Age int `json:"age"`
}

type struct Profile{
   ID string `json:"id"`
   User *UserInfo `json:"user"`
   UserType string `json:"user_type"`
}

The SHOW CREATE TABLE for profile looks like the following:

CREATE TABLE `profile`{
  `id` varchar(24) NOT NULL,
  `user` json DEFAULT NULL,
  `user_type` varchar(24) DEFAULT NULL
}

Here is my code:

var profile Profile
Profile.ID = "Demo~1"
Profile.UserType = "Permanent"
Profile.User = &User {
  Name: "John",
  Age: 24,
}

if err:= db.Create(&profile).Error; err != nil {
  fmt.Errorf("Unsuccessful insert")
}

The insert runs successfully and but when I check the mysql db, I see that all the fields have expected values except the json field user which remains NULL.

Can anyone help?

• mysql
• json
• go
• go-gorm

Share Improve this question Follow asked Mar 31 at 17:52 pippip 3755 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

I got this code to work, tested with Go 1.24.1 and Gorm 1.25.11 and MySQL 8.4.4 on MacOS.

package main

import (
        "gorm.io/driver/mysql"
        "gorm.io/gorm"
        "fmt"
)

type UserInfo struct {
        Name string
        Age int
}

type Profile struct {
        ID string
        User UserInfo `gorm:"type:bytes;serializer:json"`
        UserType string
}

// necessary because Gorm defaults to plural table names
func (Profile) TableName() string {
        return "profile"
}

func main() {
        // refer https://github/go-sql-driver/mysql#dsn-data-source-name for details
        dsn := "root:@tcp(127.0.0.1:3306)/test?charset=utf8mb4&parseTime=True&loc=Local"
        db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
        if err != nil {
                err.Error()
        }

        var profile Profile = Profile{
                ID: "Demo~1",
                UserType: "Permanent",
                User: UserInfo {
                        Name: "John",
                        Age: 24,
                },
        }

        if err:= db.Create(&profile).Error; err != nil {
                fmt.Errorf("Unsuccessful insert", err)
        } else {
                fmt.Printf("OK")
        }
}

I built and ran this test code, and it inserted the data into a row of the table, successfully formatting the sub-struct as JSON.

mysql> select * from profile;
+--------+-----------------------------+-----------+
| id     | user                        | user_type |
+--------+-----------------------------+-----------+
| Demo~1 | {"Age": 24, "Name": "John"} | Permanent |
+--------+-----------------------------+-----------+

The gorm serializer attribute is needed in the struct definition, to let Gorm know that you want the sub-struct to be marshalled into a string of bytes in JSON format. Otherwise you'd have to do that yourself with json.Marshal.

I didn't specify the JSON names for the struct members. I guess Gorm infers the JSON keys from their Go struct names by default.

Read more about Gorm serializers here: https://gorm.io/docs/serializer.html

You need to add the serializer tag to specify the serializer that handles how data is serialized and deserialized to and from the database, such as: serializer:json/gob/unixtime. Since your field is of type JSON, your User field should be defined as:

User *UserInfo `json:"user" gorm:"serializer:json"`

Otherwise, you need to manually call json.Marshal before inserting it into the database.

var profile Profile
Profile.ID = "Demo~1"
Profile.UserType = "Permanent"

user, _ := json.Marshal(&User {
  Name: "John",
  Age: 24,
})
Profile.User = string(user)

if err:= db.Create(&profile).Error; err != nil {
  fmt.Errorf("Unsuccessful insert")
}

本文标签： Json column insert into mysql db using GORMStack Overflow