分享 SQL Style Guide

hooopo · 2018年09月09日 · 最后由 hooopo 回复于 2018年09月19日 · 393 次阅读
本帖已被设为精华帖!

SQL在开发界一只被诟病易写难读,但规范良好的SQL语句还是可以给可读性带来显著提升的,至少让你可以一眼看出语句的层次和意图。

Joe Celko的《SQL ProgrammingStyle》总结了一套规则和每一条原则后的理由,包括命名规范、缩进以及设计规范。其实命名的话,按照Rails的约定几乎是没什么问题的,表名用小写、主键用id,外键用xxx_id等等,但对于自己写的SQL,只要稍微逻辑复杂一点就会风格迥异。网上有一份sql style guide,把常见的模式都做了总结,当然这并不是唯一的风格,大家也可以根据个人风格进行定制。

https://www.sqlstyle.guide/zh/

最有意思的是缩进方式,既不是左对齐也不是右对齐,其实中间是有一条河的,SELECT FROM GROUP ORDER WHERE LIMIT在左,JOIN在右,嵌套结构也以此类推:

SELECT r.last_name
  FROM riders AS r
       INNER JOIN bikes AS b
       ON r.bike_vin_num = b.vin_num
          AND b.engine_tally > 2

       INNER JOIN crew AS c
       ON r.crew_chief_last_name = c.last_name
          AND c.chief = 'Y';

共收到 10 条回复

缩进方式很有意思。这个例子还是很考究的,比如 JOIN 其实是属于 FROM clause 的部分,算是 FROM 下一层的表达式,因此缩进在 FROM 的更右边,ON 下面换行的 AND 同理。

这种方式我尝试过一段时间(可能不是严格照着这个 styleguide 来的)。最大的感受是必须有编辑器提供自动格式化才好用,几乎没办法靠人工维护。尤其是写超过 20 行的话自己都不知道缩进对不对,每多加一行都可能导致之前写的部分重新缩进。

PostgreSQL 文档里有些 SQL 例子是按这种方式缩进的,但也有混用的。比如 window function 的一个例子,内层按分界缩进外层就顶头了:

SELECT depname, empno, salary, enroll_date
FROM
  (SELECT depname, empno, salary, enroll_date,
          rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
     FROM empsalary
  ) AS ss
WHERE pos < 3;

我觉得sql锁进并不好懂,我都写左对齐的

darkbaby123 回复

SQL嵌套结构超过三层确实很难读了,缩进也非常痛苦。一般我会用CTE把子查询上移,让层次没那么深。比如:

WITH ss AS (
  SELECT depname, 
         empno, 
         salary, 
         enroll_date,
         rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
     FRO empsalary
)

SELECT depname, 
       empno, 
       salary, 
       enroll_date
  FROM ss
 WHERE pos < 3;

但有时候WITH表达式和子查询其实查询计划并不完全等价的,比如下推不能什么的,就要多写一些重复的WHERE条件。 编辑器好像不容易自动格式化,只能手动对齐....

jasl 将本帖设为了精华贴 09月10日 03:29
6楼 已删除

SQL复杂到一定程度,就可以考虑写存储过程了,如此和其他代码一样,有变量、有循环、有选择,容易缩进、更易读的,而且性能更好。但是,存储过程要保存在数据库中、运维的时候,不提醒新人的话,容易被忽略。

不过,在 ruby on rails 中,好像没有太多场景、一定要写复杂的SQL、或者存储过程的。大多数场景下,是可以通过 ActiveRecord ORM 和 Model 层逻辑解决的。这几年,都不写嵌套的子查询,最复杂的就是 join 几下而已了。

hooopo 回复

确实 query plan 不同,感觉 CTE 更严格的按照每一步去划分优化的边界,而子查询作为一个整体查询有时候会优化得更多一点。所以我一般是测试时写 CTE 然后转到代码里再换成子查询。这样也更容易转换成 app 层的代码。因为 Elixir 的 Ecto 可以写出非常复杂的分析型查询,但目前还不支持 CTE 。

Emacs 有个插件能帮你对齐,也是你说的 river 风格。不过 Emacs 的对齐功能基本都有点自恋 + 死不认错,人工调整后编辑器也会不停的帮你 “纠正” 过来。想调整规则得去研究类似 AST 的数据结构,所以我直接放弃了……

我是用workbench自带的格式化。。懒得折腾

mizuhashi 回复

http://www.dpriver.com/pp/sqlformat.htm 这个不错,很多配置选项,但没API接口

hooopo 回复

哈,才注意到pgsql的cte是被隐式的当成hint了

找到一个14年的链接 https://www.postgresql.org/message-id/CAFwQ8rfPGgcgn5kxpJEwu_mtJh19ehuQcO5CMJp7+mF6PPe+eA@mail.gmail.com 感觉这个优化很好做啊,不明白pgsql这样是设计好的还是懒得inline 那个cte

darkbaby123 回复

其实并不是CTE就一定比子查询慢,要看场景的,CTE会物化结果,对于一些重计算的查询是好事,CTE其实让Postgres有了caching功能...

https://medium.com/@hakibenita/be-careful-with-cte-in-postgresql-fca5e24d2119

需要 登录 后方可回复, 如果你还没有账号请点击这里 注册