MySQL的 json 数据类型

MySQL5.7 后的版本，添加了对于 json 类型的支持。此前，json 类型的数据，只能在代码层面做 json.loads() 和 json.dumps() 操作。因此无法直接对 json 内的数据进行查询操作。所有的处理都需要将查询结果转换后再操作，非常的麻烦。

创建表

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student`  (

  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '表的id',

  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '名字',

  `age` int(0) UNSIGNED NOT NULL COMMENT '年龄',

  `info` json NULL COMMENT '其他信息',

  PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

添加数据

添加数据时，首先要理解 mysql 对 json 的本质操作，其本质还是对支付串的操作，只是当字符串被定义为 JSON时，会在内部对数据再进行一些索引的创建，以方便后续的操作而已。，因此， JSON 需要用引号引起来。

INSERT INTO student (name,age,info) VALUES ("张山",28,'{"sex":"man","school":"清华","score":[88,92,100]}');

查询数据

-- 使用 -> 作为 json 的操作方法时，json 的返回结果会包含引号。

select name, info -> "$.sex" from student;

-- 使用 ->>　作为 json 的操作方法时，可以直接把 json 返回结果的引号去掉。

select name, info ->> "$.sex" from student;

select name, info -> "$.score" from student;

select name, info ->> "$.score" as score from student;

select name, info -> "$.name" from student;

条件查询

写查询语句时，可以直接按二级目录查询，如下：

select * from student where info -> "$.sex"="man";

修改数据

修改 json中的某个字段时

update student set info=JSON_SET(info,"$.sex","woman") where id=3

在 json 中插入某个字段

update student set info=JSON_INSERT(info,"$.addr","上海") where id=3;

mysql 中引号的使用

单引号:

只用来限定字符串，数值类型是不需要加引号的。不管使用何种形式的支付串，值都必须包含在引号里（最好是单引号）。
双引号：

标准的 SQL 中是不包含双引号的，往往是数据库本身对于 SQL 的扩展，在mysql中，单引号和双引号的效果是等价的。

go 语言操作 json

type User struct {

	ID      uint

	Name    string

	Age     int

	Profile Profile `gorm:"column:info"`

}

type Profile struct {

	Sex    string

	Addr   string

	Score  []int

	School string

}

func (User) TableName() string {

	return "student"

}

// Scan 实现 sql.Scanner 接口，从数据库中读值

func (p *Profile) Scan(value any) error {

	bytes, ok := value.([]byte)

	if !ok {

		return errors.New(fmt.Sprintf("failed to unmarshal JSONB　value %v", value))

	}

	return json.Unmarshal(bytes, p)

}

// Value 实现 driver.Valuer 接口，Value 返回 json value

func (p Profile) Value() (driver.Value, error) {

	return json.Marshal(p)

}

func main() {

	dsn := "root:123456@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 {

		panic(err)

	}

	var user User

	err = db.First(&user).Error

	if err != nil {

		panic(err)

	}

	fmt.Println(user)

}

参考文献

https://segmentfault.com/a/1190000024445924

巴特西

mysql 的 json 类型