MariaDB扩展特性－－动态列

最新推荐文章于 2021-02-01 10:47:42 发布

weixin_33953249

最新推荐文章于 2021-02-01 10:47:42 发布

阅读量174

点赞数

文章标签： python json

原文链接：https://my.oschina.net/liuliufa/blog/661088

版权

2019独角兽企业重金招聘Python工程师标准>>>

从版本5.3开始，MariaDB开始支持动态列，允许一个表的每一行存储不同列的信息。
使用动态列的前提是表中必须包含blob类型的列。

create table computer(
rid int not null auto_increment primary key,
product_name varchar(50),
product_attr blob
);

MariaDB [test]> insert into computer(product_name,product_attr) values("macbook pro",column_create("color","sliver","size","15.6","price","16000"));
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> insert into computer(product_name,product_attr) values("surfacebook",column_create("color","sliver","keyboard","red","size","13","memory","8GB","price","12000"));
Query OK, 1 row affected (0.01 sec)

// 查询

MariaDB [test]> select product_name, column_get(product_attr, "size" as char) as size from computer;
+--------------+------+
| product_name | size |
+--------------+------+
| macbook pro  | 15.6 |
| surfacebook  | 13   |
+--------------+------+
2 rows in set (0.00 sec)

// 删除

MariaDB [test]> update computer set product_attr=column_delete(product_attr,"color") where column_get(product_attr,"keyboard" as char)="red";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

// 新添

MariaDB [test]> update computer set product_attr=column_add(product_attr, "year","2016") where product_name="macbook pro";
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

// 查看动态列

MariaDB [test]> select product_name, column_list(product_attr) from computer;
+--------------+------------------------------------+
| product_name | column_list(product_attr)          |
+--------------+------------------------------------+
| macbook pro  | `size`,`year`,`color`,`price`      |
| surfacebook  | `size`,`price`,`memory`,`keyboard` |
+--------------+------------------------------------+
2 rows in set (0.00 sec)

// 查看动态列及值(json格式)

MariaDB [test]> select product_name, column_json(product_attr) from computer;
+--------------+----------------------------------------------------------------+
| product_name | column_json(product_attr)                                      |
+--------------+----------------------------------------------------------------+
| macbook pro  | {"size":"15.6","year":"2016","color":"sliver","price":"16000"} |
| surfacebook  | {"size":"13","price":"12000","memory":"8GB","keyboard":"red"}  |
+--------------+----------------------------------------------------------------+
2 rows in set (0.00 sec)

转载于:https://my.oschina.net/liuliufa/blog/661088