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

[xuyl]

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 码顺序索引的，那么该怎么做才能得到预期结果呢？

[ily433664]

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

[Rache1]

```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]

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

[xuyl]

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

[ily433664]

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