| |
MariaDB 10.0.X, the dynamic column (Dynamic Columns), to support JSON format to obtain the data.
For compatibility with traditional SQL syntax, MariaDB 10 and MySQL5.7 support for native JSON format, relational databases and NoSQL databases rolled into one document type.
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 JSON data format
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)
### Get Key (key) color of Value (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)
Get all ### Key (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)
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: 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)
### 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.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)
### Changing 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: 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) |
|
|
|