測試環境為 CentOS 8 x86_64
使用關聯式資料庫（ RDBMS : Relational Database Management System ）的 MariaDB 也可以儲存與查詢 NoSQL ( Not Only SQL : 使用非關聯式資料庫的資料庫,資料儲存不需要固定的欄位 ) 的 JSON ( JavaScript Object Notation ）資料格式.
要怎麼合併 JSON 資料,可以透過 JSON_MERGE 或是 JSON_MERGE_PRESERVE (新版建議) 函數.
JSON_MERGE(json_doc, json_doc[, json_doc] ...) |
[root@localhost ~]# mysql -u root -pEnter password: Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 10Server version: 10.3.17-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. |
- 合併相同 Key (Duplicate Keys)
MariaDB [testdb]> SELECT JSON_MERGE('{ "Name":"Alan" }','{ "Name":"Cherry"}') Result;+------------------------------+| Result |+------------------------------+| {"Name": ["Alan","Cherry"]} |+------------------------------+1 row in set (0.000 sec) - 陣列 (Arrays)
MariaDB [testdb]> SELECT JSON_MERGE('{ "Name":["Alan","Apple","Cherry"] }','{ "Name":"Cherry"}') Result;+-------------------------------------------------+| Result |+-------------------------------------------------+| {"Name": ["Alan","Apple","Cherry","Cherry"]} |+-------------------------------------------------+1 row in set (0.000 sec)
從 SELECT 出來的 JSON 資料,可以透過 JSON_MERGE 函數來合併嗎? 下面來試試看.
MariaDB [(none)]> CREATE DATABASE testdb;Query OK, 1 row affected (0.001 sec)MariaDB [(none)]> USE testdb;Database changed |
定義包含 json 欄位 (notebook) 的資料表 (json_tb).
SQL 語法:
CREATE TABLE project ( ID int(10) unsigned NOT NULL AUTO_INCREMENT, Name VARCHAR(200),Member JSON, PRIMARY KEY (ID) , CHECK (JSON_VALID(Member))); |
執行結果.
MariaDB [testdb]> CREATE TABLE project ( -> ID int(10) unsigned NOT NULL AUTO_INCREMENT, -> Name VARCHAR(200), -> Member JSON, PRIMARY KEY (ID) , CHECK (JSON_VALID(Member)) -> );Query OK, 0 rows affected (0.008 sec) |
建立兩筆資料 Project A 與 B .
依據 JSON 格式簡單定義了 Project 的成員資料(成員為非固定數量,很適合使用 JSON 格式來儲存).
{ "Name":["Alan","Joy","Art","Jimmy","Orion","Katrina"] }{ "Name":["Cherry","Mark","Jerry","Rick"] } |
MariaDB [testdb]> INSERT INTO project (Name,Member) VALUES ('Project A','{"Name":["Alan","Joy","Art","Jimmy","Orion","Katrina"]}') ;Query OK, 1 row affected (0.002 sec)MariaDB [testdb]> INSERT INTO project (Name,Member) VALUES ('Project B','{"Name":["Cherry","Mark","Jerry","Rick"]}') ;Query OK, 1 row affected (0.001 sec)MariaDB [testdb]> SELECT * FROM project;+----+-----------+---------------------------------------------------------+| ID | Name | Member |+----+-----------+---------------------------------------------------------+| 1 | Project A | {"Name":["Alan","Joy","Art","Jimmy","Orion","Katrina"]} || 2 | Project B | {"Name":["Cherry","Mark","Jerry","Rick"]} |+----+-----------+---------------------------------------------------------+2 rows in set (0.001 sec) |
- JSON_MERGE
來試試看 JSON_MERGE 資料來自 SELECT ,結果居然不行.MariaDB [testdb]> SELECT JSON_MERGE(Member) FROM project;ERROR 1582 (42000): Incorrect parametercountin the call to nativefunction'JSON_MERGE' - CONCAT + GROUP_CONCAT
網路有提供一些其他方式 – https://dba.stackexchange.com/questions/153000/mysql-5-7-json-merge-combine-with-group-concat 但還要依據你的 JSON 格式再去調整 SQL 語法才能達成.SQL 語法
SELECTCONCAT('{',GROUP_CONCAT(SUBSTRING(Member, 2, length(Member) - 2)),'}') Member_NameFROM project WHERE Member != JSON_OBJECT();執行結果
MariaDB [testdb]> SELECT CONCAT('{', GROUP_CONCAT(SUBSTRING(Member, 2, length(Member) - 2)) ,'}') Member_Name FROM project WHERE Member != JSON_OBJECT();+-------------------------------------------------------------------------------------------------+| Member_Name |+-------------------------------------------------------------------------------------------------+| {"Name":["Alan","Joy","Art","Jimmy","Orion","Katrina"],"Name":["Cherry","Mark","Jerry","Rick"]} |+-------------------------------------------------------------------------------------------------+1 row in set (0.001 sec)可以看到就單純把兩個 JSON 資料合在一起而已.
- Prepared Statement
更多關於使用 Prepared Statement 請參考 – https://benjr.tw/102000MariaDB [testdb]> SET @sql = NULL;Query OK, 0 rows affected (0.001 sec)MariaDB [testdb]> SELECT GROUP_CONCAT(CONCAT('\'',Member,'\'')) INTO @sql FROM project;Query OK, 1 row affected (0.002 sec)MariaDB [testdb]> SET @sql = CONCAT('SELECT JSON_MERGE(', @sql,')');Query OK, 0 rows affected (0.000 sec)MariaDB [testdb]> SELECT @sql;+--------------------------------------------------------------------------------------------------------------------------+| @sql |+--------------------------------------------------------------------------------------------------------------------------+| SELECT JSON_MERGE('{"Name":["Alan","Joy","Art","Jimmy","Orion","Katrina"]}','{"Name":["Cherry","Mark","Jerry","Rick"]}') |+--------------------------------------------------------------------------------------------------------------------------+1 row in set (0.000 sec)MariaDB [testdb]>MariaDB [testdb]> PREPARE stmt FROM @sql;Query OK, 0 rows affected (0.000 sec)Statement prepared的確達到所需的效果.
MariaDB [testdb]> EXECUTE stmt;+-------------------------------------------------------------------------------------------------------------------+| JSON_MERGE('{"Name":["Alan","Joy","Art","Jimmy","Orion","Katrina"]}','{"Name":["Cherry","Mark","Jerry","Rick"]}') |+-------------------------------------------------------------------------------------------------------------------+| {"Name": ["Alan","Joy","Art","Jimmy","Orion","Katrina","Cherry","Mark","Jerry","Rick"]} |+-------------------------------------------------------------------------------------------------------------------+1 row in set (0.000 sec)MariaDB [testdb]> DEALLOCATE PREPARE stmt;Query OK, 0 rows affected (0.000 sec)
沒有解決問題,試試搜尋本站其他內容