我有一个availability
结构如下表
| id | weekday | interval |
| 1 | 0 | {"open":"1583055000","close":"1583087400"} |
| 2 | 0 | {"open":"1583069400","close":"1583086200"} |
我如何可以搜索打开和关闭场之间的具体时间?
我试过了:
select * from availabilities where json_extract(`interval`, "$.open") > 1583063400 and json_extract(`interval`, "$.close") < 1583063400;
但返回0结果
使用JSON工作充满了陷阱。
首先,如果你JSON_EXTRACT(),你没有得到一个字符串或数字,你就会得到一个JSON文件,虽然一个是只是一个字符串。
mysql> select json_extract(`interval`, '$.open') from availabilities;
+------------------------------------+
| json_extract(`interval`, '$.open') |
+------------------------------------+
| "1583055000" |
| "1583069400" |
+------------------------------------+
所以,你还需要解除引用值来获得一个普通的标量值。阅读关于JSON_UNQUOTE() 。
mysql> select json_unquote(json_extract(`interval`, '$.open')) from availabilities;
+--------------------------------------------------+
| json_unquote(json_extract(`interval`, '$.open')) |
+--------------------------------------------------+
| 1583055000 |
| 1583069400 |
+--------------------------------------------------+
您也可以使用->>
简写做同样的提取物和所享有:
mysql> select `interval`->>'$.open' from availabilities
+-----------------------+
| `interval`->>'$.open' |
+-----------------------+
| 1583055000 |
| 1583069400 |
+-----------------------+
其他问题与您查询的是,你必须是永真为你的数据值的条件。让我们来告诉你为什么:
select
open, open > 1583063400 as gt1583063400,
close, close < 1583063400 as lt1583063400
from (
select
json_unquote(json_extract(`interval`, '$.open')) as open,
json_unquote(json_extract(`interval`, '$.close')) as close
from availabilities
) as t
+------------+--------------+------------+--------------+
| open | gt1583063400 | close | lt1583063400 |
+------------+--------------+------------+--------------+
| 1583055000 | 0 | 1583087400 | 0 |
| 1583069400 | 1 | 1583086200 | 0 |
+------------+--------------+------------+--------------+
无论你在你的数据有密切的值都小于你比较它们的价值。
在您的查询,你要行,其中无论是开盘价和收盘价通过你给的条件。也许你想用OR?
无论如何,如果你改变你比较值,你可以找到一个行其中两个通。
select
open, open > 1583063400 as gt1583063400,
close, close < 1583087200 as lt1583087200
from (
select
json_unquote(json_extract(`interval`, '$.open')) as open,
json_unquote(json_extract(`interval`, '$.close')) as close
from availabilities
) as t
+------------+--------------+------------+--------------+
| open | gt1583063400 | close | lt1583087200 |
+------------+--------------+------------+--------------+
| 1583055000 | 0 | 1583087400 | 0 |
| 1583069400 | 1 | 1583086200 | 1 |
+------------+--------------+------------+--------------+
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句