mysql - 按 JSON 字段值排序

Question

我有一个包含如下 json 值的表:

-表1

id    |   name   |   data
------+----------+---------------
1     | Test     | {"city_id": 3, "email":"test@test.com", "city_name":"something"}
2     | Test 2   | {"city_id": 1, "email":"test2@test2.com", "city_name":"another"}
3     | Test 3   | {"city_id": 6, "email":"test3@test3.com", "city_name":"blahblah"}

现在我想要SELECT记录order bydata.city_name，所以我使用这个代码:

SELECT id, name, JSON_EXTRACT(data, 'city_name') AS cityName
FROM table1
ORDER BY cityName ASC

但是这个查询不能正确排序我的记录!

附注:city_name 有 UTF-8 字符。

Answer 1

您似乎没有使用 JSON_EXTRACT()正确，尝试:

SELECT id, name, JSON_EXTRACT(data, '$.city_name') AS cityName 
FROM demo ORDER BY cityName ASC

Demo Fiddle