Shulou(Shulou.com)06/01 Report--

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)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.