引子: 今天被人说我MySQL懂得很浅,作为一个老OCP,我竞然无言以对。因为确实我这么
多年一直都用的ORACLE,没玩过啥重量级的MySQL应用. 不过,这个刚好让我想起了前几天,
一个API接口的讨论,可以拿出来聊聊.
当时的需求是如下图这样子在APP端显示一个商家列表(商家来自各行各业.).
ps: 1.每个商家行业可能不同,参数不同.
2.同行业不同商家,所要显示的参数也有可能不同。
我开出的表结构:
商家 | 参数 | 参数值
然后做Web接口的同事,也直接用这样子的方式给做了接口。这下捅娄子了,
引起了APP端的激烈反弹。他们觉得这样很不好处理。KV形式的才是他们的
最爱,然后讨论后认为应当改如下的表结构:
商家 | 参数1 | 参数2 | 参数3| ......
以后每出现一种新的参数,就在表中新加一个字段,以此类推。
这样能解决问题吗?
能解决,只要在表的最大列数范围内,是没问题的。
不过试想一下,以后维护会是什么样子,比如真出现一个新参数之类要怎么处理?
我画了一张图,可供参考:
这个维护量可不小。
其实App端主要的诉求在于,他们想要一个KV的结构。数据库行转列一下,就可以搞定.
我来举个MySQL的例子吧:
CREATE TABLE shop_param(
shopid varchar(10) not null comment '商家',
param_name varchar(20) not null comment '参数',
param_value varchar(30) not null comment '参数值',
primary key (shopid,param_name)
);
INSERT INTO shop_param (shopid,param_name,param_value) VALUES( 'A01','星级','y');
INSERT INTO shop_param (shopid,param_name,param_value) VALUES( 'A01','WIFI','aaaaaaa');
INSERT INTO shop_param (shopid,param_name,param_value) VALUES( 'C01','健身房','abc');
INSERT INTO shop_param (shopid,param_name,param_value) VALUES( 'B01','游泳池','bb111');
INSERT INTO shop_param (shopid,param_name,param_value) VALUES( 'B01','健身房','bbb222');
INSERT INTO shop_param (shopid,param_name,param_value) VALUES( 'A01','健身房','abcddfdsfsdf');
INSERT INTO shop_param (shopid,param_name,param_value) VALUES( 'C01','餐饮','123');
COMMIT;
MariaDB [pushdb]> SELECT * FROM shop_param;
+--------+------------+--------------+
| shopid | param_name | param_value |
+--------+------------+--------------+
| A01 | WIFI | aaaaaaa |
| A01 | 健身房 | abcddfdsfsdf |
| A01 | 星级 | y |
| B01 | 健身房 | bbb222 |
| B01 | 游泳池 | bb111 |
| C01 | 健身房 | abc |
| C01 | 餐饮 | 123 |
+--------+------------+--------------+
7 rows in set (0.00 sec)
MariaDB [pushdb]> SELECT shopid,GROUP_CONCAT(CONCAT_WS(':',param_name,param_value))
-> FROM shop_param
-> GROUP BY shopid;
+--------+-----------------------------------------------------+
| shopid | GROUP_CONCAT(CONCAT_WS(':',param_name,param_value)) |
+--------+-----------------------------------------------------+
| A01 | WIFI:aaaaaaa,健身房:abcddfdsfsdf,星级:y |
| B01 | 健身房:bbb222,游泳池:bb111 |
| C01 | 健身房:abc,餐饮:123 |
+--------+-----------------------------------------------------+
3 rows in set (0.00 sec)
很容易搞定.
我再用茴香豆有几种写法的精神,用Oracle PL/SQL写一下:
SQL> -- 11g及以后常用
SQL> SELECT shopid,listagg(param_name||':'||param_value,',') within GROUP (ORDER BY param_name) as param
2 FROM shop_param
3 GROUP BY shopid ;
SHOPID PARAM
---------- --------------------------------------------------------------------------------
A01 WIFI:aaaaaaa,健身房:abcddfdsfsdf,星级:y
B01 健身房:bbb222,游泳池:bb111
C01 餐饮:123,健身房:abc
SQL> -- 10g及以前常用
SQL> SELECT shopid,wmsys.wm_concat(param_name||':'||param_value) as param
2 FROM shop_param
3 GROUP BY shopid ;
SHOPID PARAM
---------- --------------------------------------------------------------------------------
A01 WIFI:aaaaaaa,星级:y,健身房:abcddfdsfsdf
B01 健身房:bbb222,游泳池:bb111
C01 餐饮:123,健身房:abc
这样就解决问题了,以后维护,只需更新表里面的数据即可.
如下图:这个行转列的技巧一点都不难,对SQL稍有点关注的基本都知道。但Web和APP端竞然都没人知道这个技巧。
我后来想了下原因,可能是因为现在ORM大行其道。可以跨库,不用直接写SQL,用映射好的类和函数即可。
结果,变得少有人去关心它产生的SQL及研究SQL技巧了。 SQL曾经的荣光啊。
其实数据库发展了这么多年,有非常多好的东西。多学学,里面的学问深着呢,指不定哪天就用上了。
BLOG: http://blog.csdn.net/xcl168