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