多懂点SQL可以写出更好的接口

原创 2015年11月17日 21:59:09

         引子: 今天被人说我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



版权声明:本文为博主原创文章,未经博主允许不得转载。

SQL Server开发接口生成方法

为提高开发效率,生成固定格式的接口是必须的,以下以提供新增/修改/删除/读取接口为例: 以常见的表结构为例,特殊表结构可自己尝试去调整方法...
  • roy_88
  • roy_88
  • 2016年05月06日 14:13
  • 3086

SQL Server开发接口生成方法

为提高开发效率,生成固定格式的接口是必须的,以下以提供新增/修改/删除/读取接口为例: 以常见的表结构为例,特殊表结构可自己尝试去调整方法...
  • roy_88
  • roy_88
  • 2016年05月06日 14:13
  • 3086

Java中使用Statement接口SQL语句

Statement执行SQL语句: 1. 对数据库的曾删改操作时,使用stmt.executeUpdate(sql  执行给定 SQL 语句,分别为 insert 、update、delete....
  • u010986080
  • u010986080
  • 2016年07月02日 22:14
  • 3494

强大的SQLHelper类

在做机房收费系统的时候,曾经使用过Sqlhelper,当时对此内容理解不是很好,参照的是下篇的博客:   vb.net—SQLHelper类的使用   而做过之后,当再次回首往事的时候,发现这个S...
  • ZHOUCHAOQIANG
  • ZHOUCHAOQIANG
  • 2014年09月20日 13:20
  • 2470

SQL创建链接服务器

创建MySQL的链接服务器: use master go -- 删除链接服务器,如果存在此链接服务器 if exists(select * from sysservers where ...
  • yanzhibo
  • yanzhibo
  • 2014年01月20日 22:42
  • 2268

如何写出高性能SQL语句

优化SQL查询:如何写出高性能SQL语句 1、首先要搞明白什么叫执行计划?   执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生欀如一条SQL语...
  • sxdtzhaoxinguo
  • sxdtzhaoxinguo
  • 2013年11月15日 11:19
  • 4251

JDBC中Statement 接口提供了三种执行 SQL 语句的方法: executeQuery executeUpdate execute 使用哪一个方法由 SQL 语句所产生的内容决定。

JDBC中Statement 接口提供了三种执行 SQL 语句的方法: executeQuery executeUpdate  execute 使用哪一个方法由 SQL 语句所产生的内容决...
  • qq_20302155
  • qq_20302155
  • 2017年06月24日 23:50
  • 1759

SQL查询优化:如何写出高性能SQL语句

1.查询的模糊匹配 尽量避免在一个复杂查询里面使用 LIKE '%parm1%'—— 红色标识位置的百分号会导致相关列的索引无法使用,最好不要用. 解决办法: 其实只需要对该脚本略做...
  • hj7jay
  • hj7jay
  • 2016年04月25日 09:42
  • 1589

接口测试注意的几点

接口测试作为集成测试的一部分,通过直接调用被测试的接口来确定系统在功能性、可靠性、安全性和性能方面是否能达到预期,有些情况是功能测试无法覆盖的,所以接口测试是非常必要的。   接口测试分为两种,...
  • u012062310
  • u012062310
  • 2017年05月18日 16:59
  • 3418

成为编程大牛很简单,把这些书看个八成就OK

程序员必读书单 1.0 原文链接:http://lucida.me/blog/developer-reading-list/ 本文把程序员所需掌握的关键知识总结...
  • u012845099
  • u012845099
  • 2017年10月24日 14:56
  • 824
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:多懂点SQL可以写出更好的接口
举报原因:
原因补充:

(最多只允许输入30个字)