这里利用了COLUMN_CREATE()函数来创建动态列。插入的四条数据。针对各个不同的商品定义不同的属性。比如1在各个商品中表示的是颜色;3表示的是操作系统;10表示的是大小。每行数据的属性不完全相同,也不要求所有的属性值都需要包含在动态列中。那么,动态列的有哪些属性要怎么查看列:
select id, name, type, price, length(dynstr) as len, column_list(dynstr) as list from t1;
+----+-------------------------+----------+--------+------+------+
| id | name | type | price | len | list |
+----+-------------------------+----------+--------+------+------+
| 1 | Funny shirt | shirt | 10.00 | 17 | 1,10 |
| 2 | nokia | phone | 649.00 | 27 | 1,2 |
| 3 | htc Desire hd | phone | 579.00 | 23 | 1,3 |
| 4 | BM/Lenovo Thinkpad X60s | computer | 419.00 | 21 | 1,3 |
+----+-------------------------+----------+--------+------+------+
COLUMN_LIST()可以列出动态列到底有哪些属性,这样的话我们就可以根据属性过滤:
SELECT name FROM t1 WHERE COLUMN_GET(dynstr, 1 as char(10)) = "black";
+-------------------------+
| name |
+-------------------------+
| nokia |
| htc Desire hd |
| BM/Lenovo Thinkpad X60s |
+-------------------------+
SELECT name, COLUMN_GET(dynstr, 1 as char(10)) FROM t1 WHERE COLUMN_EXISTS(dynstr, 1);
+-------------------------+--------+
| name | colour |
+-------------------------+--------+
| Funny shirt | blue |
| nokia | black |
| htc Desire hd | black |
| BM/Lenovo Thinkpad X60s | black |
+-------------------------+--------+
前面都是对动态列创建和查询的例子,我们再看看更新的例子。比如我们需要对类型为‘computer’的数据加4G内存:
UPDATE t1 set dynstr=COLUMN_ADD(dynstr, 15, "4G ram") where type="computer";
SELECT name, type, price, length(dynstr) as len, column_list(dynstr) as list from t1 where type="computer";
+-------------------------+----------+--------+------+--------+
| name | type | price | len | list |
+-------------------------+----------+--------+------+--------+
| BM/Lenovo Thinkpad X60s | computer | 419.00 | 29 | 1,3,15 |
+-------------------------+----------+--------+------+--------+
COLUMN_ADD()函数会给每个’computer’的行加上新的属性,属性值为‘4G
ram’。不过,COLUMN_ADD()并不仅仅是添加,它和MySQL的replace一样,如果发现相同类型的属性,对应的属性值将被覆盖。