Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MariaDB 10.0.X, the dynamic column support JSON format to obtain data     - CentOS 6.7 install Nagios Tutorials (Server)

- Hadoop configuration ssh automation of automation (Server)

- Linux System Getting Started Tutorial: How to automatically set the JAVA_HOME environment variable on Linux (Linux)

- Configuring Android NDK development environment for Mac OS X (Linux)

- How to manage KVM virtual environments with command-line tools in Linux (Server)

- Compile and install Ubuntu Linux 4.0.5 kernel, network and fix vmware kernel module compilation error (Linux)

- VMware difference in three network connection (Linux)

- How to install Nginx on FreeBSD 10.2 as an Apache reverse proxy (Server)

- How to Install Xombrero 1.6.4 (minimalist Web browser) on Ubuntu and Archlinux (Linux)

- C ++ type conversion and RTTI (Programming)

- SendMail version of Java implementation with attachments (Programming)

- Analysis of common mistakes when compiling MySQL installation (Database)

- Installing Linux and Windows 10 dual system (Linux)

- Java Virtual Machine class loading mechanism and bytecode execution engine (Programming)

- How to manage your to-do list with the Go For It on Ubuntu (Linux)

- Stucts2 values on the page and how the attribute values corresponding to the Action (Programming)

- How to view the Linux program or process used in the library (Linux)

- Tsung first test installation (Linux)

- To create a full command line Android Build System (Linux)

- Oracle Database Performance Optimization of memory disk (Database)

 
         
  MariaDB 10.0.X, the dynamic column support JSON format to obtain data
     
  Add Date : 2017-04-13      
         
         
         
  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)
     
         
         
         
  More:      
 
- Windows Desktop use VNC remote connect Linux (Linux)
- Python in os.path Magical (Programming)
- Ubuntu 14.10 / 14.04 / 12.04 How to install Kodi 14.0 RC3 (Linux)
- Spring AOP (Programming)
- Getting Started with Linux system to learn: how to check in a package is installed on Ubuntu (Linux)
- Linux directory structure (Linux)
- Linux basic introductory tutorial ---- simple text processing (Linux)
- How do I upgrade to Ubuntu 15.04 (Beta) (Linux)
- ORA-27054 NFS problem solving (Database)
- CoreCLR compiled in Linux CentOS (Linux)
- MySQL server after an unexpected power outage can not start (Database)
- MySQL Installation Troubleshooting (Database)
- C ++ Supplements --new delete overload (Programming)
- Linux System Getting Started Tutorial: How to change the default Java version in Linux (Linux)
- Linux automatic installation and implementation (Linux)
- I use the desktop environment in GNU / Linux combination tool (Linux)
- Using Oracle for Oracle GoldenGate to achieve a one-way data synchronization (Database)
- Zabbix monitors Nginx status (Server)
- Virtual Judge environment to build and configure under Ubuntu (Server)
- Under CentOS using yum command to install the Task Scheduler crontab (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.