mysql 的 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;

插入数据

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

select name, info -> "$.sex" from student;
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";