In MariaDB 10.0.X, dynamic columns support JSON format to get data.
In MariaDB 10.0.X, the dynamic column (Dynamic Columns) can support JSON format to get data.
To be compatible with traditional SQL syntax, MariaDB 10 and MySQL5.7 support the native JSON format, that is, a combination of relational and document NoSQL databases.
Instructions for use:
# Table structure
Create table assets (item_name varchar (32) primary key,-A common attribute for all items dynamic_cols blob-Dynamic columns will be stored here)
# insert data in JSON format
Mysql > INSERT INTO assets VALUES-> ('MariaDB Tmurshirtships, COLUMN_CREATE (' color', 'blue',' size', 'XL')); Query OK, 1 row affected (0.02sec) mysql > INSERT INTO assets VALUES-> (' Thinkpad Laptop', COLUMN_CREATE ('color',' black', 'price', 500)); Query OK, 1 row affected (0.01sec)
# get Value (value) of Key (key) color:
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)
# get all Key (keys)
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)
# get all 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)
# Delete a 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: 0mysql > 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)
# add a 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.01sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > 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)
# change a 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: 0mysql > 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)
