您好,登錄后才能下訂單哦!
MariaDB 10.0.X中,動態(tài)列(Dynamic Columns),可以支持 JSON 格式來獲取數(shù)據(jù)。
為了兼容傳統(tǒng)SQL語法,MariaDB 10和MySQL5.7支持原生JSON格式,即關(guān)系型數(shù)據(jù)庫和文檔型NoSQL數(shù)據(jù)庫集于一身。
使用說明:
###表結(jié)構(gòu)
create table assets ( item_name varchar(32) primary key, -- A common attribute for all items dynamic_cols blob -- Dynamic columns will be stored here );
###插入JSON格式數(shù)據(jù)
mysql> INSERT INTO assets VALUES -> ('MariaDB T-shirt', COLUMN_CREATE('color', 'blue', 'size', 'XL')); Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO assets VALUES -> ('Thinkpad Laptop', COLUMN_CREATE('color', 'black', 'price', 500)); Query OK, 1 row affected (0.01 sec)
###獲取Key(鍵)color的Value(值):
mysql> SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color FROM assets; +-----------------+-------+ | item_name | color | +-----------------+-------+ | MariaDB T-shirt | blue | | Thinkpad Laptop | black | +-----------------+-------+ 2 rows in set (0.00 sec)
###獲取全部Key(鍵)
mysql> SELECT item_name, column_list(dynamic_cols) FROM assets; +-----------------+---------------------------+ | item_name | column_list(dynamic_cols) | +-----------------+---------------------------+ | MariaDB T-shirt | `size`,`color` | | Thinkpad Laptop | `color`,`price` | +-----------------+---------------------------+ 2 rows in set (0.00 sec)
###獲取全部Key-Value
mysql> SELECT item_name, COLUMN_JSON(dynamic_cols) FROM assets; +-----------------+-------------------------------+ | item_name | COLUMN_JSON(dynamic_cols) | +-----------------+-------------------------------+ | MariaDB T-shirt | {"size":"XL","color":"blue"} | | Thinkpad Laptop | {"color":"black","price":500} | +-----------------+-------------------------------+ 2 rows in set (0.01 sec)
###刪除一個Key-Value:
mysql> UPDATE assets SET dynamic_cols=COLUMN_DELETE(dynamic_cols, "price") -> WHERE COLUMN_GET(dynamic_cols, 'color' as char)='black'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT item_name, COLUMN_JSON(dynamic_cols) FROM assets; +-----------------+------------------------------+ | item_name | COLUMN_JSON(dynamic_cols) | +-----------------+------------------------------+ | MariaDB T-shirt | {"size":"XL","color":"blue"} | | Thinkpad Laptop | {"color":"black"} | +-----------------+------------------------------+ 2 rows in set (0.00 sec)
###增加一個Key-Value:
mysql> UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '3 years') -> WHERE item_name='Thinkpad Laptop'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT item_name, COLUMN_JSON(dynamic_cols) FROM assets; +-----------------+----------------------------------------+ | item_name | COLUMN_JSON(dynamic_cols) | +-----------------+----------------------------------------+ | MariaDB T-shirt | {"size":"XL","color":"blue"} | | Thinkpad Laptop | {"color":"black","warranty":"3 years"} | +-----------------+----------------------------------------+ 2 rows in set (0.00 sec)
###更改一個Key-Value:
mysql> UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols,'color', 'white') WHERE COLUMN_GET(dynamic_cols, 'color' as char)='black'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT item_name, COLUMN_JSON(dynamic_cols) FROM assets; +-----------------+----------------------------------------+ | item_name | COLUMN_JSON(dynamic_cols) | +-----------------+----------------------------------------+ | MariaDB T-shirt | {"size":"XL","color":"blue"} | | Thinkpad Laptop | {"color":"white","warranty":"3 years"} | +-----------------+----------------------------------------+ 2 rows in set (0.00 sec)
免責聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。