mysql5.7 json 类型字段的聚合统计问题

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 1401 天前的主题，其中的信息可能已经有所发展或是发生改变。

DROP TABLE IF EXISTS `abc`;
CREATE TABLE `abc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `score` json DEFAULT NULL,
  `type` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `abc` (`id`, `score`, `type`) VALUES
(1,	'{\"v\": 100, \"type\": \"number\"}',	'a'),
(2,	'{\"v\": 95, \"type\": \"number\"}',	'a'),
(3,	'{\"v\": 60, \"type\": \"number\"}',	'a'),
(4,	'{\"v\": 100, \"type\": \"number\"}',	'b'),
(5,	'{\"v\": 95, \"type\": \"number\"}',	'b'),
(6,	'{\"v\": 60, \"type\": \"number\"}',	'b');

执行 sql 语句

select type, max(score->'$.v') as v1, min(score->'$.v') as v2 from abc group by type;

得出结果

+------+------+------+
| type | v1   | v2   |
+------+------+------+
| a    | 95   | 100  |
| b    | 95   | 100  |
+------+------+------+

不是预期

type=a, v1=100, v2=60
type=b, v1=100, v2=60

查了下，可能 mysql 的 json 字段索引方式是按照 ascii 码顺序索引的，那么该怎么做才能得到预期结果呢？

5 条回复 • 2021-01-28 14:11:31 +08:00

ily433664

2021-01-27 17:30:37 +08:00

转成数字
select type, max(CONVERT(score->'$.v', DECIMAL)) as v1, min(CONVERT(score->'$.v', DECIMAL)) as v2 from abc group by type;

Rache1

2021-01-27 17:47:05 +08:00

```sql
select type, max(CAST(score->'$.v' AS unsigned)) as v1, min(CAST(score->'$.v' AS unsigned)) as v2 from abc group
by type;
```

xuyl

2021-01-27 17:53:30 +08:00

@ily433664 @faqqcn 测试都可以，多谢两位。

xuyl

2021-01-28 12:06:04 +08:00

@ily433664 现在又遇到新问题，如果数值为浮点数，则会丢失精度

ily433664

2021-01-28 14:11:30 +08:00

@xuyl #4 设置 DECIMAL 的精度，DECIMAL(18, 2)