MySql中如何提取text类型字段中的数据，json_extract试过了，取出来的都是空值

Question

"{"source":"self","id":"13","name":"01.mp4","length":0}"
"{"source":"self","id":"13","name":"01.mp4","length":0}"
"{"size":3556197,"length":0,"source":"self","status":"none","name":"\u662f\u662f\u662f1.mp4","id":9}"
"{"size":3869713,"length":0,"source":"self","status":"none","name":"\u662f\u662f\u662f2.mp4","id":10}"

字段类型为text，类型也不能改，

JSON_EXTRACT(( cast(字段 as josn),"$.id") 也不行，查询是空值。

如何取出字段中id对应的数字？

Answer 1

你没写正确！！！

mysql> desc m0_61364557;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| m1    | text | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.02 sec)

mysql> select * from m0_61364557;
+---------------------------------------------------------------------------------------------------+
| m1                                                                                                |
+---------------------------------------------------------------------------------------------------+
| {"source":"self","id":"13","name":"01.mp4","length":0}                                            |
| {"source":"self","id":"13","name":"01.mp4","length":0}                                            |
| {"size":3556197,"length":0,"source":"self","status":"none","name":"u662fu662fu662f1.mp4","id":9}  |
| {"size":3869713,"length":0,"source":"self","status":"none","name":"u662fu662fu662f2.mp4","id":10} |
+---------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> select JSON_EXTRACT( m1,"$.id") from m0_61364557;
+--------------------------+
| JSON_EXTRACT( m1,"$.id") |
+--------------------------+
| "13"                     |
| "13"                     |
| 9                        |
| 10                       |
+--------------------------+
4 rows in set (0.00 sec)

mysql>