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

資深大佬 : xuyl 4

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)

資深大佬 : ily433664

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

資深大佬 : faqqcn

“`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)