mysql> create table me(id int auto_increment primary key, m json, c json);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into me(m,c)values('{"xm":"ldf","xb":"nan","mz":"han","hf":true,"sg":168}','["xm","mz"]');
Query OK, 1 row affected (0.00 sec)
mysql> select * from me;
+----+----------------------------------------------------------------+--------------+
| id | m | c |
+----+----------------------------------------------------------------+--------------+
| 1 | {"hf": true, "mz": "han", "sg": 168, "xb": "nan", "xm": "ldf"} | ["xm", "mz"] |
+----+----------------------------------------------------------------+--------------+
1 row in set (0.00 sec)
m欄位包含多個鍵值對，回傳c欄位給出的鍵值對。
若希望得到下面的資料，這里應該如何寫查詢陳述句，注意c欄位的值是不確定的
+----+----------------------------+--------------+
| id | mmm | ccc |
+----+----------------------------+--------------+
| 1 | {"mz": "han", "xm": "ldf"} | ["xm", "mz"] |
+----+----------------------------+--------------+
uj5u.com熱心網友回復：
你看下 網上的MySQL 出來jsonuj5u.com熱心網友回復：
沒看粗來啊~~~
uj5u.com熱心網友回復：
自己寫個函式，類似這樣CREATE FUNCTION f(m json, c json)
RETURNS json
BEGIN
SELECT c->'$[0]', JSON_LENGTH(c), 1 INTO @name, @len, @pos;
SET @r:=CONCAT('{', @name, ':', JSON_EXTRACT(m, CONCAT('$.',@name)), '}');
WHILE @pos < @len DO
SELECT CONCAT('$.', JSON_EXTRACT(c, CONCAT('$[',@pos,']'))), @pos+1 INTO @name, @pos;
SELECT JSON_INSERT( @r, @name, JSON_EXTRACT(m, @name) ) INTO @r;
END WHILE;
RETURN @r;
END
uj5u.com熱心網友回復：
然后呼叫函式實作查詢SELECT *, f(m, c) FROM me;
uj5u.com熱心網友回復：
直接查詢出資料應該就可以了吧，為什么要執著的查出一個json，根據條件查詢出資料并不難，使用mysql5.7對json型別的操作函式即可。三樓不愧為資料庫大佬，佩服，佩服，函式寫的很強轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/85493.html
標籤：MySQL