MySQL8.0 JSON的对比、排序和索引

(目录)

JSON的对比和排序

JSON值可以通过=, <, <=, >, >=, <>, !=, <=>操作符来进行对比 JSON不支持BETWEEN,IN(),GREATEST(),LEAST(),可以通过将JSON转换为其他数据类型来使用这些操作符。

JSON值的对比在两个级别上进行，先进行数据类型的对比，如果类型相同，再进行值的对比。类型可以通过JSON_TYPE()函数来进行获取。

JSON值类型优先级

MySQL不支持在ORDER BY和GROUP BY中对JSON数组和对象进行排序，所以如果在查询语句中执行ORDER BY和GROUP BY，其中数组和对象的顺序可能与下述不同

JSON值的类型优先级：

-- 由上到下由大到小
BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL

同类型的JSON值的顺序

任意JSON值与NULL值对比，结果为UNKNOWN 对于同种类型的JSON值，对比规则如下：

• BLOB,BIT,OPAQUE 对比前N个字符，如果前N个字符相同，长度短的值更小 .N等于较短值的长度。
• ARRAY 如果两个JSON数组具有相同的长度，并且数组中相应位置的值相等，则它们相等。 如果数组不相等，则它们的顺序由第一个位置上存在差异的元素决定。该位置的值较小的数组较大。 如果较短数组的所有值都等于较长数组中的相应值，则优先排序较短数组。

-- 数组对比示例
[] < ["a"] < ["ab"] < ["ab", "cd", "ef"] < ["ab", "ef"]
-- JSON不支持在order by和group by中对数组进行对比和排序，所以如过在查询中使用order by，结果可能与上述结果不同
-- 例如以下语句， ["ab", "cd", "ef"] < ["ab", "ef"]，但是结果中顺序相反
mysql> select value from test_json where json_type(value)="ARRAY" order by value ;
+--------------------+
| value              |
+--------------------+
| []                 |
| ["a"]              |
| ["ab"]             |
| ["ab", "ef"]       | 
| ["ab", "cd", "ef"] |
+--------------------+

• BOOLEAN false < true
• OBJECT 如果两个对象具有相同的键，并且每个键的值相等，那么两个JSON对象相等。不区分键值对在对象中出现的顺序 {"a": 1, "b": 2} = {"b": 2, "a": 1}
• STRING 使用utf8mb4字符集和utf8mb4_bin排序规则，区分大小写 "A" <"a" < "ab" < "b" < "bc"
• INTEGER, DOUBLE JSON值可以包含精确值和近似值。在JSON值中的数值比较与MySQL数值类型不同
  • 在对比MySQL的INT和DOUBLE类型时,将int转换为double来进行对比
  • 在对比JSON的数值时，无法确定数值是整数还是浮点数，MySQL会将浮点数转换为整数。排序结果不会失去精确值的数组精度。 9223372036854775805 < 9223372036854775806 < 9223372036854775807 < 9.223372036854776e18 = 9223372036854776000 < 9223372036854776001

JSON和非JSON的转换

JSON的索引

不能直接在JSON列上创建索引，要优化对JSON列的查询，可以在生成列上创建索引

注意：如果要在字符串值上创建索引，建议在创建生成列时使用以下方法

doc_name TEXT AS (JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name'))) STORED
-- (JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name'))) 等同于 (jdoc->>"$.name")

存疑：在官方文档Optimizer Use of Generated Column Indexes中表示

• 生成列定义时使用c->>"$.name"并在该列上创建索引 可以为c->>"$.name"和c->"$.name"进行优化，
• 生成列定义时使用c->"$.name"并在该列上创建索引 仅能为c->"$.name"进行优化。

但是实际测试中没有使用到c->"$.name"的生成列索引，建议使用c->>"$.name"的形式创建生成列和索引。

示例

-- 建表
mysql> CREATE TABLE jemp (
    ->     id int auto_increment primary key,
    ->     c JSON,
    ->     g INT GENERATED ALWAYS AS (c->"$.id"),
    ->     n varchar(200) GENERATED ALWAYS AS (c->>"$.name"),
    ->     n2 varchar(200) GENERATED ALWAYS AS (c->"$.name"),
    ->     index g(g),
    ->     index n (n),
    ->     index n2(n2)
    -> );

-- 插入数据
mysql> INSERT INTO jemp (c) VALUES ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');

-- 检查数据
mysql> select * from jemp;
+----+-------------------------------+------+--------+----------+
| id | c                             | g    | n      | n2       |
+----+-------------------------------+------+--------+----------+
|  1 | {"id": "1", "name": "Fred"}   |    1 | Fred   | "Fred"   |
|  2 | {"id": "2", "name": "Wilma"}  |    2 | Wilma  | "Wilma"  |
|  3 | {"id": "3", "name": "Barney"} |    3 | Barney | "Barney" |
|  4 | {"id": "4", "name": "Betty"}  |    4 | Betty  | "Betty"  |
+----+-------------------------------+------+--------+----------+

-- 查看执行计划
mysql> explain select * from jemp where c->"$.id" >2;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | jemp  | NULL       | range | g             | g    | 5       | NULL |    2 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- 查询c->"$.name"，只用到了n列作为索引，n列定义：n varchar(200) GENERATED ALWAYS AS (c->>"$.name")
mysql> explain select * from jemp where c->"$.name" ="Barney";
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | jemp  | NULL       | ref  | n             | n    | 803     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

-- 查询c-->"$.name"，也只用到了n列作为索引，n列定义：n varchar(200) GENERATED ALWAYS AS (c->>"$.name")
mysql> explain select * from jemp where c->>"$.name" ="Barney";
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | jemp  | NULL       | ref  | n             | n    | 803     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)