请教大家一个 SQL 里使用 JSON_EXTRACT 的问题：为啥这条 sql 能选到第二条数据呢？
你的前面有个.啊。
不好意思，请问 . 具体指什么？是 $.name 么？
隐式转换的问题，你不加coalesce比默认是吧后边字符串转为json类型比的，在你的SQL里是转为varchar比的，但是两边还有引号，就比不上，把引号去掉就比上了
MySQL [test]> explain analyze select json_extract(info,'$.name'),coalesce(json_extract(info,'$.name'),''),info from t5 where json_extract(info,'$.name')='Barney';
+---------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+---------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| Projection_4 | 0.80 | 1 | root | | time:688µs, loops:2, RU:0.475726, Concurrency:OFF | json_extract(test.t5.info, $.name)->Column#4, coalesce(cast(json_extract(test.t5.info, $.name), var_string(16777216)), )->Column#5, test.t5.info | 636 Bytes | N/A |
| └─TableReader_7 | 0.80 | 1 | root | | time:649.9µs, loops:2, cop_task: {num: 1, max: 572.1µs, proc_keys: 0, tot_proc: 2.18µs, tot_wait: 56.6µs, rpc_num: 1, rpc_time: 542.8µs, copr_cache_hit_ratio: 1.00, build_task_duration: 8.57µs, max_distsql_concurrency: 1} | data:Selection_6 | 297 Bytes | N/A |
| └─Selection_6 | 0.80 | 1 | cop[tikv] | | tikv_task:{time:12ms, loops:1}, scan_detail: {get_snapshot_time: 21.3µs, rocksdb: {block: {}}} | eq(json_extract(test.t5.info, "$.name"), cast("Barney", json BINARY)) | N/A | N/A |
| └─TableFullScan_5 | 1.00 | 1 | cop[tikv] | table:t5 | tikv_task:{time:0s, loops:1} | keep order:false, stats:pseudo | N/A | N/A |
+---------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
4 rows in set (0.01 sec)
MySQL [test]> select json_extract(info,'$.name'),coalesce(json_extract(info,'$.name'),''),info from t5 where coalesce(json_extract(info,'$.name'),'') not in ('Barney');
+-----------------------------+------------------------------------------+-------------------------------+
| json_extract(info,'$.name') | coalesce(json_extract(info,'$.name'),'') | info |
+-----------------------------+------------------------------------------+-------------------------------+
| "Barney" | "Barney" | {"id": "3", "name": "Barney"} |
+-----------------------------+------------------------------------------+-------------------------------+
1 row in set (0.01 sec)
MySQL [test]> explain select json_extract(info,'$.name'),coalesce(json_extract(info,'$.name'),''),info from t5 where coalesce(json_extract(info,'$.name'),'') not in ('Barney');
+---------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_4 | 0.80 | root | | json_extract(test.t5.info, $.name)->Column#4, coalesce(cast(json_extract(test.t5.info, $.name), var_string(16777216)), )->Column#5, test.t5.info |
| └─TableReader_7 | 0.80 | root | | data:Selection_6 |
| └─Selection_6 | 0.80 | cop[tikv] | | ne(coalesce(cast(json_extract(test.t5.info, "$.name"), var_string(16777216)), ""), "Barney") |
| └─TableFullScan_5 | 1.00 | cop[tikv] | table:t5 | keep order:false, stats:pseudo |
+---------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
MySQL [test]> select json_extract(info,'$.name'),coalesce(json_extract(info,'$.name'),''),info from t5 where coalesce(JSON_UNQUOTE(json_extract(info,'$.name')),'') not in ('Barney');
Empty set (0.00 sec)
MySQL [test]> explain select json_extract(info,'$.name'),coalesce(json_extract(info,'$.name'),''),info from t5 where coalesce(JSON_UNQUOTE(json_extract(info,'$.name')),'') not in ('Barney');
+---------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_4 | 0.80 | root | | json_extract(test.t5.info, $.name)->Column#4, coalesce(cast(json_extract(test.t5.info, $.name), var_string(16777216)), )->Column#5, test.t5.info |
| └─TableReader_7 | 0.80 | root | | data:Selection_6 |
| └─Selection_6 | 0.80 | cop[tikv] | | ne(coalesce(json_unquote(cast(json_extract(test.t5.info, "$.name"), var_string(16777216))), ""), "Barney") |
| └─TableFullScan_5 | 1.00 | cop[tikv] | table:t5 | keep order:false, stats:pseudo |
+---------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.01 sec)
3 个赞
牛，这个没想到
学到了
此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。