MySQL中JSON使用

• 在使用数据库存储数据时,经常会用到json方式;比如产品的各种属性,这时候又不能确定每一样产品有多少属性,因此直接存json可以避免扩展数据库字段,使之更为灵活.
• MySQL自5.7之后开始支持json类型.

1.JSON_EXTRACT提取json中字段
2.JSON_OBJECTAGG键值对方式组装成json返回,列表需要配合GROUP BY使用

一.直接存json,JSON_EXTRACT提取

CREATE TABLE `mo_stat_order`  (
  `stat_id` bigint(20) UNSIGNED NOT NULL COMMENT '统计ID',
  `stat_typ` tinyint(3) UNSIGNED NOT NULL COMMENT '统计类型',
  `stat_date` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '周期开始日',
  `stat_corp` bigint(20) UNSIGNED DEFAULT NULL COMMENT '企业用户ID',
  `stat_user` bigint(20) UNSIGNED DEFAULT NULL COMMENT '用户ID',
  `stat_data` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '统计的数据json格式',
  PRIMARY KEY (`stat_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '订单统计表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of mo_stat_order
-- ----------------------------
INSERT INTO `mo_stat_order` VALUES (21325055773984, 11, '2022', 16168574354689, 0, '{\"crm_cnt\":1,\"crm_amt\":500,\"bank_cnt\":1,\"bank_amt\":5000,\"loan_cnt\":2,\"loan_amt\":56000,\"fee_cnt\":1,\"fee_amt\":50000}');


# 查询:使用JSON_EXTRACT提取
SELECT
stat_id,stat_typ,stat_date,stat_user,stat_data,
JSON_EXTRACT(stat_data,'$.crm_cnt') crm_cnt,
JSON_EXTRACT(stat_data,'$.crm_amt') crm_amt
FROM mo_stat_order

二.JSON_OBJECTAGG使用

• 该函数主要是对以键值对方式存数据,然后以json方式返回数据,只会返回一条数据,因此列表需要使用分组的方式
• 如图:
  

SELECT stat_typ,JSON_OBJECTAGG(stat_key,stat_val) stat_data FROM mo_stat_orders GROUP BY stat_typ