SQL 格式化标准 [英] SQL formatting standards

问题描述

在我的上一份工作中，我们开发了一个非常依赖数据库的应用程序，并且我开发了一些格式标准，以便我们都可以使用通用布局编写 SQL.我们还制定了编码标准，但这些标准更特定于平台，因此我不会在此处详细介绍.

In my last job, we worked on a very database-heavy application, and I developed some formatting standards so that we would all write SQL with a common layout. We also developed coding standards, but these are more platform-specific so I'll not go into them here.

我很想知道其他人使用什么来制定 SQL 格式化标准.与大多数其他编码环境不同，我没有在网上找到太多关于它们的共识.

I'm interested to know what other people use for SQL formatting standards. Unlike most other coding environments, I haven't found much of a consensus online for them.

涵盖主要的查询类型:

select
    ST.ColumnName1,
    JT.ColumnName2,
    SJT.ColumnName3
from 
    SourceTable ST
inner join JoinTable JT
    on JT.SourceTableID = ST.SourceTableID
inner join SecondJoinTable SJT
    on ST.SourceTableID = SJT.SourceTableID
    and JT.Column3 = SJT.Column4
where
    ST.SourceTableID = X
    and JT.ColumnName3 = Y

select、from 和where 之后的换行存在一些分歧.选择行的目的是允许其他操作符，例如top X"而不改变布局.在此之后，在关键查询元素之后保持一致的换行似乎会产生良好的可读性.

There was some disagreement about line feeds after select, from and where. The intention on the select line is to allow other operators such as "top X" without altering the layout. Following on from that, simply keeping a consistent line feed after the key query elements seemed to result in a good level of readability.

在 from 和 where 之后删除换行符是可以理解的修订.然而，在像下面的 update 这样的查询中，我们看到 where 之后的换行符为我们提供了良好的列对齐.类似地，group by 或 order by 之后的换行可使我们的列布局清晰易读.

Dropping the linefeed after the from and where would be an understandable revision. However, in queries such as the update below, we see that the line feed after the where gives us good column alignment. Similarly, a linefeed after group by or order by keeps our column layouts clear and easy to read.

update
    TargetTable
set
    ColumnName1 = @value,
    ColumnName2 = @value2
where
    Condition1 = @test

最后，一个插入:

insert into TargetTable (
    ColumnName1,
    ColumnName2,
    ColumnName3
) values (
    @value1,
    @value2,
    @value3
)

在大多数情况下，这些与 MS SQL Server Managements StudioSQL Server Managements Studio/查询分析器写出 SQL，但是它们确实不同.

For the most part, these don't deviate that far from the way MS SQL Server Managements Studio / query analyser write out SQL, however they do differ.

我期待看到 Stack Overflow 社区对此话题是否有任何共识.我一直很惊讶有多少开发人员可以遵循其他语言的标准格式，并且在使用 SQL 时突然变得如此随意.

I look forward to seeing whether there is any consensus in the Stack Overflow community on this topic. I'm constantly amazed how many developers can follow standard formatting for other languages and suddenly go so random when hitting SQL.

推荐答案

我的观点是，只要你能轻松阅读源代码，格式是次要的.只要实现了这个目标，就有很多不错的布局风格可以采用.

I am of the opinion that so long as you can read the source code easily, the formatting is secondary. So long as this objective is achieved, there are a number of good layout styles that can be adopted.

对我来说唯一重要的另一个方面是，无论您选择在商店中采用何种编码布局/样式，都要确保所有编码人员都一致地使用它.

The only other aspect that is important to me is that whatever coding layout/style you choose to adopt in your shop, ensure that it is consistently used by all coders.

仅供参考，以下是我将如何展示您提供的示例，只是我的布局偏好.需要特别注意的是，ON 子句与join 在同一行，join 中只列出了primary join 条件(即key 匹配)，其他条件为移至 where 子句.

Just for your reference, here is how I would present the example you provided, just my layout preference. Of particular note, the ON clause is on the same line as the join, only the primary join condition is listed in the join (i.e. the key match) and other conditions are moved to the where clause.

select
    ST.ColumnName1,
    JT.ColumnName2,
    SJT.ColumnName3
from 
    SourceTable ST
inner join JoinTable JT on 
    JT.SourceTableID = ST.SourceTableID
inner join SecondJoinTable SJT on 
    ST.SourceTableID = SJT.SourceTableID
where
        ST.SourceTableID = X
    and JT.ColumnName3 = Y
    and JT.Column3 = SJT.Column4

一个提示，给自己一份SQL Prompt 来自 红门.您可以自定义该工具以使用您想要的布局偏好，然后您商店的编码员都可以使用它来确保每个人都采用相同的编码标准.

One tip, get yourself a copy of SQL Prompt from Red Gate. You can customise the tool to use your desired layout preferences, and then the coders in your shop can all use it to ensure the same coding standards are being adopted by everyone.

这篇关于SQL 格式化标准的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！