我正在尝试从MySQL中的JSONFIELD“数据”中提取日期时间。
但是，如果我执行简单的JSON_EXTRACT，则返回字段类型为JSON。
mysql> select JSON_EXTRACT(data, "$.new_time") from analytics limit 10;
+----------------------------------+
| JSON_EXTRACT(data, "$.new_time") |
+----------------------------------+
| NULL |
| "2016-09-30T04:00:00+00:00" |
| "2016-09-29T05:30:00+00:00" |
| NULL |
| "2016-10-01T05:30:00+00:00" |
| "2016-09-27T23:00:00+00:00" |
| NULL |
| "2016-09-23T01:30:00+00:00" |
| "2016-09-23T04:00:00+00:00" |
| "2016-09-27T01:30:00+00:00" |
+----------------------------------+
我想将其转换为MySQL DATETIME。但是，如果我链接JSON_EXTRACT和STR_TO_DATETIME，我将获得所有NULL值：
mysql> select STR_TO_DATE(JSON_EXTRACT(data, "$.new_time") ,"%Y-%m-%d") from analytics_calendaranalytics limit 10;
+-----------------------------------------------------------+
| STR_TO_DATE(JSON_EXTRACT(data, "$.new_time") ,"%Y-%m-%d") |
+-----------------------------------------------------------+
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+-----------------------------------------------------------+
同样，CAST作为DATETIME也会失败：
mysql> select CAST(JSON_EXTRACT(data, "$.new_time") as DATETIME) from analytics_calendaranalytics limit 10;
+----------------------------------------------------+
| CAST(JSON_EXTRACT(data, "$.new_time") as DATETIME) |
+----------------------------------------------------+
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+----------------------------------------------------+
当我从字符串值开始时，这两个命令都起作用：
mysql> select CAST("2016-09-30T04:00:00+00:00" as DATETIME);
+-----------------------------------------------+
| CAST("2016-09-30T04:00:00+00:00" as DATETIME) |
+-----------------------------------------------+
| 2016-09-30 04:00:00 |
+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select STR_TO_DATE("2016-09-30T04:00:00+00:00", "%Y-%m-%d");
+------------------------------------------------------+
| STR_TO_DATE("2016-09-30T04:00:00+00:00", "%Y-%m-%d") |
+------------------------------------------------------+
| 2016-09-30 |
+------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
在解决此问题方面将不胜感激！
您必须使用JSON_UNQUOTE
select CAST( JSON_UNQUOTE( JSON_EXTRACT(data, "$.new_time")) as DATETIME) from analytics_calendaranalytics limit 10;
会工作。我要说的是，因为您尚未提供示例数据。我尝试如下：
select @js := JSON_OBJECT('new_time',"2016-09-30T04:00:00+00:00" );
select CAST(JSON_UNQUOTE(JSON_EXTRACT(@js,'$.new_time')) as DATETIME);
以下查询也适用
select STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(@js,'$.new_time')) ,"%Y-%m-%d");
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句